SQLServerCentral Article

Querying System Tables



You might have come across situations where it was necessary to copy all the

details in your database into another. To make the situation more complex,

consider it between different servers.

Think, think and think very hard. Ultimately you end-up with the solution which

is inefficient and time-consuming. Passing the complete stored procedure text as

a command string or executing the script file associated with the stored


What else can you do? Is there any other option or method?

Yes. You can do it querying the system tables and getting the details. But which

system tables will give you these details? In which column you can find the

required data? This article will give you information about how the details are maintained by

SQL Server about the tables, its constraints and stored procedures.

First of all, system tables are never changed by the user explicitly. Only the

database engine makes modification to these tables depending upon the various

DDL and DML executed on user tables.

About Various System Tables

sysobjects contains the list of various objects like tables, views, functions,

stored procedures. Each object is identified by an ID which is updated whenever

any DML is executed on a particular table. It contains a field “xtype”, which

can be used to identify the various object types as user table, system table, a

view, a stored procedure or a primary key.

Some examples:


To retrieve all the user created tables:


SELECT name FROM sysobjects WHERE xtype = ‘U’;


To get list of all the stored procedures:


SELECT name FROM sysobjects WHERE xtype=’P’;

syscolumns table contains the details about all the columns which are defined in

the database. All the properties of the column, i.e., the data type, length,

null allowed or not, etc. can be obtained through this table.


Some examples:


To retrieve all the fields of table ‘XYZ’:


SELECT name FROM syscolumns WHERE ID = (SELECT id FROM sysobjects WHERE



The syscolumns stores column definition along with their table id. Table id is a

unique identifier which is used to identify tables among different system

objects. As discussed earlier, sysobjects contains the all details about the

database objects, which include the tables also. We provide the name of the

table, and get the id from sysobjects. This id is used to retrieve the columns.

systypes table contains details about the various data types supported by SQL

Server. So, to find out about the data type of all the columns of a particular

table (say ‘XYZ’):


SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype


FROM sysobjects, syscolumns, systypes


WHERE sysobjects.id = syscolumns.id AND


syscolumns.xtype = systypes.xtype AND


sysobjects.name = 'table1'

syscomments table can be used to retrieve the content of the stored procedure

i.e., it can be used to get the full script of a stored procedure. The query

below gives the code of the procedure named ‘Proc1’:


SELECT text FROM syscomments


WHERE id = (SELECT id FROM sysobjects WHERE name = ‘Proc1’ AND xtype=’P’);


The ‘text’ column in syscomments stores the definition of the stored procedure

and other database objects also. So, we select the ‘text’ column whose ‘id’ is

same as that of the procedure we want to find.


Identity column in a table is used if you want to make sure the uniqueness of

the data is maintained.

The content of the identity column can be obtained by using:




SELECT emp_id FROM employee; (emp_id is identity column.)

What if you want to find the whether a column is identity or not?

The system tables do not store directly that a column is IDENTITY column or not.

The query to find out the identity column in table XYZ is:


SELECT name FROM syscolumns


WHERE status = 128 AND


id = (SELECT id FROM sysobjects WHERE name = ‘XYZ’)


‘status’ column in syscolumns tables is set to 128 if the column is of identity


Computed Columns

The columns which store the data through a combination of two or more columns

based on a formula are called computed columns. It is possible to find out

whether a table contains any computed columns or not.

Consider the following query:






aa int,


bb int,


cc as [aa] + [bb]



A table will be created with computed column “cc”. No direct entry is allowed in

the computed column. The database engine will automatically enter the value

depending upon the formula.

Now, to find the computed column and its formula from our table through system



SELECT    syscolumns.name, syscomments.text


FROM    sysobjects, syscomments, syscolumns


WHERE  sysobjects.id = syscolumns.id AND


syscolumns.id = syscomments.id AND


syscolumns.colid = syscomments.colid AND


sysobjects.name = 'XYZ' AND


syscolumns.iscomputed = 1


Output will be:


name       text


zz             ([xx] + [yy])


The ‘iscomputed’ column is set to 1 if the column is computed and the ‘text’

field is the same, which is used for procedures in earlier example.

This should be enough to make you feel comfortable with the system tables. I

prefer using this methodology for creating the resources (database, tables,

procedures, etc.) on different servers, as it will be easy to replicate and

maintain relations.


SQL Server also provides information about tables, constraints, columns,

procedure text, etc. through system stored procedures. For example, the system

procedure sp_help can take table name, function name or

procedure name as arguments and provide the details about the structure,

its identity field, data type of the columns, length, parameters used in

functions or procedures, return values etc. Also, sp_helptext takes

procedure name or function name as its argument and will give the detail code-listing.

The best part of using system tables is that it is not necessary to save the

scripts. You can replicate tables and various other database objects among

different databases on different servers based on the actual table definitions.

Application based on this concept ensures that the changes made to the source

tables are exactly replicated to the target tables.



3.6 (5)

You rated this post out of 5. Change rating




3.6 (5)

You rated this post out of 5. Change rating