Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Querying System Tables

By Vasant Raj, (first published: 2005/06/13)

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.
 

Total article views: 39202 | Views in the last 30 days: 52
 
Related Articles
FORUM

System Stored Procedures referring to which tables.??

System Stored Procedures Tables?

ARTICLE

Creating a System Stored Procedure

Creating a system stored procedure isn't hard. You're always cautioned from making changes to the sy...

FORUM

Script system stored procedures in master?

Script my own sp's that are running as system stored procedures in master?

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

FORUM

stored procedure---finding all tables that contain a specific column

stored procedure---finding all tables that contain a specific column

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones