datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to find all the foreign key definitions?

Execute the following Microsoft SQL Server T-SQL example script in SSMS Query Editor to list all the foreign key relationships (constraints). Display Foreign Key to Primary Key mappings.

-- SQL Foreign Key - list all Foreign Keys - SQL Server find Foreign Keys - INNER JOIN

-- SQL Server list constraints - SQL information_schema views - Disabled Foreign Key info

USE AdventureWorks2008;

 

SELECT   fkSchemaName = FK.CONSTRAINT_SCHEMA,

         fkTableName = FK.TABLE_NAME,

         fkColumnName = FK.COLUMN_NAME,

         pkSchemaName = PK.TABLE_SCHEMA,

         pkTableName = PK.TABLE_NAME,

         pkColumnName = PK.COLUMN_NAME,

         isDisabled = CASE

                        WHEN is_disabled = 1 THEN 'DISABLED'

                        ELSE ''

                      END,

         fkName = RC.CONSTRAINT_NAME

FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

         INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK

           ON FK.CONSTRAINT_NAME = RC.CONSTRAINT_NAME

         INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK

           ON PK.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME

         INNER JOIN sys.foreign_keys sfk

           ON sfk.name = RC.CONSTRAINT_NAME

WHERE    FK.ORDINAL_POSITION = PK.ORDINAL_POSITION

ORDER BY fkSchemaName,

         fkTableName,

         fkColumnName

 

-- Partial results

fkSchemaName fkTableName fkColumnName pkSchemaName pkTableName pkColumnName isDisabled fkName
Sales ShoppingCartItem ProductID Production Product ProductID FK_Shop…
Sales SpecialOfferProduct ProductID Production Product ProductID FK_Spec…
Sales SpecialOfferProduct SpecialOfferID Sales SpecialOffer SpecialOfferID FK_Spec…
Sales Store BusinessEntityID Person BusinessEntity BusinessEntityID FK_Stor…
Sales Store SalesPersonID Sales SalesPerson BusinessEntityID FK_Stor…

 

 

Exam Prep 70-461
Exam 70-461