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:

[SOURCECODE LANGUAGE=”SQL”]SELECT * FROM SYS.SQL_LOGINS [/SOURCECODE]

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'