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.