System Tables and Catalog Views

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=1SQL

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.databasesNote: 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=0Find 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'

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.