SQL Server stores information about data in system tables. It also provides system stored procedures to access this information or Information Schema Views to access this information. Using the system tables needs to have in depth knowledge on columns in these tables.
System Tables: Most common or used system tables are –
SysObjects: This table keeps information on all objects in the database. The objects are identified by the xtype column. Some of xtypes are U – User Table, P – Stored Procedures, C – Check Constraint, F – Foreign Key Constraint etc… So, for example to get all tables in the database you can write select statement as –
SELECT * FROM sysObjects where xtype = ‘U’
SysColumns: This system table stores information on all columns for tables. The column identifying the relationship between columns and tables is the id column in sysColumns table. Also, the data type for columns is stored in xtype column. It relates to the sysTypes table. For example, assume you have a table called tblUsers. You would like to list all columns, their data types, data size from this table. Your SQL would look like
SELECT syscolumns.name columnname, systypes.name datatype, syscolumns.length, syscolumns.prec
from SysObjects JOIN syscolumns ON SysObjects.id = SysColumns.id
JOIN systypes ON syscolumns.xtype = sysTypes.xtype
Where SysObjects.name = 'Users'
SysTypes: This can be considered as lookup table to get all the Data types in the database. To get all the data type names in the database your SQL will look like –
SELECT name from Systypes
SysForeignKeys: This keeps information regarding foreign key relationships in the database. The way it is stored needs some explaination. SysForeignkeys table has 4 important columns. The columns are –
Fkeyid: This stores the ID related to SysObjects table. The ID stored is for the table which contains the foreign key.
rkeyID: The ID stored is for the referenced table that has the primary key.
Fkey: This actually references to the column that is the foreign key in the table. It is related to the colid column in the sysColumns table.
Rkey: References the primary key in the primary table. It is related to the colid column in the sysColumns table.
To select all tables that depend on Users table your select statement will look like
SELECT ChildTable.tablename, ChildTable.columnname
FROM sysforeignkeys JOIN (Select SysObjects.Name tablename, sysObjects.id, sysColumns.name columnname, sysColumns.colid FROM SysObjects JOIN sysColumns ON SysObjects.id = sysColumns.id Where sysobjects.xtype = 'U') PrimaryTable ON
sysforeignkeys.rkeyID = PrimaryTable.ID AND sysforeignkeys.rkey = PrimaryTable.colid
JOIN (Select SysObjects.Name tablename, sysObjects.id, sysColumns.name columnname, sysColumns.colid FROM SysObjects JOIN sysColumns ON SysObjects.id = sysColumns.id Where sysobjects.xtype = 'U') ChildTable ON
sysforeignkeys.fkeyID = ChildTable.ID AND sysforeignkeys.fkey = ChildTable.colid
WHERE PrimaryTable.tablename = 'Users'
