Thursday, July 31, 2008

Display schema and constraints

SELECT C.CONSTRAINT_NAME ,PK.TABLE_NAME as PK_TABLE_NAME , PK.COLUMN_NAME as PK_COLUMN_NAME
, FK.TABLE_NAME as FK_TABLE_NAME, FK.COLUMN_NAME as FK_COLUMN_NAME
FROM Information_Schema.REFERENTIAL_CONSTRAINTS C
JOIN Information_Schema.KEY_COLUMN_USAGE PK
ON PK.CONSTRAINT_NAME = C.UNIQUE_CONSTRAINT_NAME
JOIN Information_Schema.KEY_COLUMN_USAGE FK
ON FK.CONSTRAINT_NAME = C.CONSTRAINT_NAME
--WHERE (((PK.TABLE_NAME ='Customers') and (FK.TABLE_NAME ='Orders'))
--OR ((PK.TABLE_NAME ='Orders') and (FK.TABLE_NAME ='Customers')))
Select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME from Information_Schema.KEY_COLUMN_USAGE where
TABLE_NAME = 'Orders'
Select CONSTRAINT_NAME, UNIQUE_CONSTRAINT_NAME from Information_Schema.REFERENTIAL_CONSTRAINTS
select * from INFORMATION_SCHEMA.Columns where Table_Name = 'Orders'

No comments:

Post a Comment