Systems Engineering and RDBMS

Simple meta data queries for SQL Server

Posted by decipherinfosys on August 5, 2008

A reader had sent an e-mail asking about meta-data queries to help them get the foreign key information, the constraints information and just a general information for all the columns, their data types, nullability criteria etc..  So, here are those SQLs (for SQL Server – will post the Oracle and DB2 LUW ones soon as well):

/*Get FK information */

SELECT  (CASE
WHEN OBJECTPROPERTY(CONSTID, ‘CNSTISDISABLED’) = 0 THEN ‘ENABLED’
ELSE ‘DISABLED’
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY  TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME,  KEYNO
GO

/*Get constraint information – replace the table_name below and the type for the key to look at different type of constraints*/

SELECT COLS.CONSTRAINT_NAME,COLS.COLUMN_NAME,COLS.ORDINAL_POSITION, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS ON
COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
WHERE COLS.CONSTRAINT_CATALOG = DB_NAME()
AND COLS.TABLE_NAME = ‘ACCOUNT’
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION
GO

/*List of tables, their columns, data-type for the columns, NULL/Not NULL criteria:*/

SELECT A.TABLE_NAME, B.COLUMN_NAME, B.DATA_TYPE, B.IS_NULLABLE, B.COLUMN_DEFAULT, B.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME
GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: