Find all tables in database.
SQL SERVER 2000: select name from sysobjects where type='U'
SQL SERVER 2005: select name from sys.tables
In SQL Server 2005, you can find whether a table is replicated. Execute the following query.
select * from sys.tables where is_replicated =1

Find all views in database
SQL SERVER 2000: select name from sysobjects where type='V'
SQL SERVER 2005: select name from sys.views
In SQL Server 2005, you can find whether a view is replicated. Execute the following query.
select * from sys.views where is_replicated =1

Find all triggers in database
SQL SERVER 2000: select name from sysobjects where type='TR'
SQL SERVER 2005: select name from sys.triggers where parent_class=1
In SQL Server 2005, the triggers can be Assembly trigger (CLR) or a SQL trigger. In addition, we can find whether the trigger is an AFTER trigger or INSTEAD of trigger.
Select name from sys.triggers where type='TA'
Select name from sys.triggers where type='TR'
Select name from sys.triggers where is_instead_of_trigger=1

Find all Sql logins in a server
SQL SERVER 2000: select * from master.syslogins where isntgroup=0 and isntname=0
SQL SERVER 2005: select * from sys.sql_logins

Find all dependencies of the SQL Object in a database
SQL SERVER 2000: Select * from sysdepends
SQL SERVER 2005: Select * from sys.sql_dependencies

Find all data types in SQL Server
SQL SERVER 2000: Select * from systypes
SQL SERVER 2005: Select * from sys.systypes

Find all error messages in SQL Server
SQL SERVER 2000: select * from master.sysmessages
SQL SERVER 2005: select * from sys.messages

Find all the database files of the current database.
SQL SERVER 2000: Select name,filename from sysfiles
SQL SERVER 2005: Select name, physical_name from sys.database_files

Find the type of index.
SQL SERVER 2000: We have to use id column to determine the type of index from 0,1 or 255.
SQL SERVER 2005: select object_name(object_id),name, type_desc from sys.indexes where type_desc ='CLUSTERED'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='HEAP'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='NONCLUSTERED'
Select object_name(object_id),name, type_desc from sys.indexes where type_desc ='XML'