Working with System Tables in SQL 2005

Find ALL tables that have columns with an identity property

SQL SERVER 2000:

select object_name(id),name from syscolumns where columnproperty(id,name,'IsIdentity')=1 

SQLSERVER 2005:

select object_name(object_id),name from sys.identity_columns 

Note: SQL Server 2005 stores the last value of the identity property that was generated. To query the last value execute the following query.

select name,last_value from sys.identity_columns

Find ALL columns in a table that are computed columns

SQL SERVER 2000:

select name from syscolumns where id =object_id('TableName') and iscomputed=1

SQL SERVER 2005:

select name from sys.computed_columns where object_id =object_id('TableName')

Note: The computed column in SQL Server 2005 may be persisted. To narrow down the result set, you could execute the following query:

select * from sys.computed_columns where is_persisted=0

Find ALL database names in a SQL Server instance

SQL SERVER 2000:

select name from master..sysdatabases

SQL SERVER 2005:

select name from sys.databases

Note: Many enhancements were made to the database. Query all of the columns in sys.databases to understand the new enhancements like snapshot, etc.

Find ALL Procedures in a Database

SQL SERVER 2000:

select name from sysobjects where type='P'

SQL SERVER 2005:

select name from sys.procedures

Note: You can find whether the stored procedure execution is used in replication or if the stored procedure is a startup procedure. Execute the following queries:

select name from sys.procedures where is_execution_replicated=1
select name from sys.procedures where is_auto_executed=0

Find ALL tables in a Database

SQL SERVER 2000:

select name from sysobjects where type='U'

SQL SERVER 2005:

select name from sys.tables

Note: 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 a Database

SQL SERVER 2000:

select name from sysobjects where type='V'

SQL SERVER 2005:

select name from sys.views

Note: 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 a Database

SQL SERVER 2000:

select name from sysobjects where type='TR'

SQL SERVER 2005:

select name from sys.triggers where parent_class=1

Note: 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. Execute the following query:

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 indid 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'