Speed up Development on Backend and Middle Tier with SQL Scripts
page 2 of 6
by Ameet Phadnis
Feedback
Average Rating: 
Views (Total / Last 10 Days): 28520/ 90

Accessing Meta Data - System Tables

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'

 

 


View Entire Article

User Comments

Title: MetaData   
Name: Sanjay Modi
Date: 2007-04-10 2:25:25 PM
Comment:
its realy helpful my best wishes to user

Sanjay Modi
modi_sanjay@yahoo.com
Title: ing   
Name: Jorgen Pedersen
Date: 2005-09-05 12:10:14 PM
Comment:
Hi. I like your article but I need to find the name of the columns constraint name for the DEFAULT value of the column. Can you help?
Yjanks in advance
Title: Thank you   
Name: Mab
Date: 2005-06-21 8:37:32 AM
Comment:
HI
Its really a very gud articles for the developers. Thanks for sharing such a nice article.

Cheers






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 8:04:56 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search