sysdatabases

Found this column very interesting.

select * from sysdatabases ususually the category column shows as 0 but understood now it is used for replication.

interesting notes from MSDN:

For transactional and snapshot publishing databases, the category bit is set to 1. For merge publishing databases, the category bit is set to 4.

Its mentioned that we need to be careful while restoring databases in Replication, the db to be restored has this category column set correctly based on the replication settings.

Refer MSDN article here:

http://64.4.11.252/en-us/library/aa236981%28SQL.80%29.aspx

also just found out that sysdatabases is a view which refers sys.databases system table.
All the information, various columns etc. are in Sys.Databases


Even Sys.Databases is a view, When I see the helptext for this, its joining the following tables and pulling the results:
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id) p 
 LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB 
 LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state  


But you can't query these tables with just a simple select query, 


select * from master.sys.syspalvalues won't work;