Systems Engineering and RDBMS

Some useful SQL Server Scripts

Posted by decipherinfosys on January 6, 2008

Here are some pretty simple T-SQL scripts to query the meta-data in SQL Server. These will work on both SQL Server 2000 and SQL Server 2005.

/*************************************************************************************
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_MASTER’
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
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_NAME = B.TABLE_NAME
ORDER BY A.TABLE_NAME, B.COLUMN_NAME
GO

Sorry, the comment form is closed at this time.

 
%d bloggers like this: