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