System Tables and Catalog Views

March 1, 2006

When a new version is released or when existing software is upgraded there is always a learning curve and getting used to new ways of doing things. This article demonstrates how to use SQL Server 2005's catalog views in comparison with using system tables in SQL Server 2000.

Catalog views are a storehouse for static metadata. They contain data about the server, database, objects, logins, permissions, etc.

Catalog views are the general interface to the catalog metadata, and we should start using them rather than accessing the system tables directly.

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

Conclusion

All of the SQL Statements that are used in SQL Server 2000 can still be used in SQL Server 2005. It is advisable to start using the catalog views since the underlying system tables might change in future releases and catalog views are the general interface to the catalog metadata.

» See All Articles by Columnist MAK