Querying System Tables

,

Introduction

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

procedure.

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

name=’XYZ’);

           

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 Columns

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 IDENTITYCOL FROM XYZ;

       

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

type.

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:

       

CREATE TABLE XYZ

       

(

           

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

tables:

       

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.

Conclusion

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.

 

Rate

3.6 (5)

Share

Share

Rate

3.6 (5)