Dear All,
Information Schema Views are defined in SQL-92 standard as a way to view metadata in a database. These views are there in sql server since version 7.0, at that time they were the original system tables. Now, to comply with the SQL-92 standard, all these views are there in a schema called INFORMATION_SCHEMA. We can use these views to see objects that are compatible with SQL-92 standard. One such query could be to see the Foreign Key relationships for all the tables in a database. We can use the following query:
SELECT RC.CONSTRAINT_NAME as ConstraintName, TC.TABLE_CATALOG as DatabaseName, TC.TABLE_SCHEMA as SchemaName, TC.TABLE_NAME as TableName, KCU.COLUMN_NAME as ColumnName, RTC.TABLE_CATALOG as ReferenceedDatabaseName, RTC.TABLE_SCHEMA as ReferencedSchemaName, RTC.TABLE_NAME as ReferencedTableName, RTC_COLS.COLUMN_NAME as ReferencedColumnName FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON RC.CONSTRAINT_CATALOG = TC.CONSTRAINT_CATALOG AND RC.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS RTC ON RC.UNIQUE_CONSTRAINT_CATALOG = RTC.CONSTRAINT_CATALOG AND RC.UNIQUE_CONSTRAINT_SCHEMA = RTC.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = RTC.CONSTRAINT_NAME AND RTC.CONSTRAINT_TYPE = 'PRIMARY KEY' INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE RTC_COLS ON RTC.CONSTRAINT_NAME = RTC_COLS.CONSTRAINT_NAME
There are many ways of getting this result set including the system catalogs. This is using Information Schemas.
Regards
Rahul Sharma
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
