Retrieve List of Primary Keys and Foreign Keys of Database

SELECT f.name AS ForeignKey,

   OBJECT_NAME(f.parent_object_id) AS TableName,

   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,

   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName,

   'ALTER TABLE '+OBJECT_NAME(f.parent_object_id)+' ADD CONSTRAINT '+ f.name

        + ' FOREIGN KEY ('+COL_NAME(fc.parent_object_id, fc.parent_column_id)

        +') REFERENCES '+ OBJECT_NAME(f.referenced_object_id) +'('

        + COL_NAME(fc.referenced_object_id, fc.referenced_column_id)+')'  AS Syntax

FROM sys.foreign_keys f

INNER JOIN sys.foreign_key_columns fc ON f.OBJECT_ID = fc.constraint_object_id

SELECT RC.CONSTRAINT_NAME FK_Name

, KF.TABLE_SCHEMA FK_Schema

, KF.TABLE_NAME FK_Table

, KF.COLUMN_NAME FK_Column

, RC.UNIQUE_CONSTRAINT_NAME PK_Name

, KP.TABLE_SCHEMA PK_Schema

, KP.TABLE_NAME PK_Table

, KP.COLUMN_NAME PK_Column

, RC.MATCH_OPTION MatchOption

, RC.UPDATE_RULE UpdateRule

, RC.DELETE_RULE DeleteRule

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME

JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME

SELECT

CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,

TABLE_CATALOG = FK.TABLE_CATALOG,

TABLE_SCHEMA = FK.TABLE_SCHEMA,

TABLE_NAME = FK.TABLE_NAME,

COLUMN_NAME = FK_COLS.COLUMN_NAME,

REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,

REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,

REFERENCED_TABLE_NAME = PK.TABLE_NAME,

REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG

AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA

AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG

AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA

AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),

PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),

PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),

PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),

FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),

FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),

FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),

FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),

-- Force the column to be non-nullable (see SQL BU 325751)

--KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),

UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')

WHEN 1 THEN 0

ELSE 1

END),

DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')

WHEN 1 THEN 0

ELSE 1

END),

FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),

PK_NAME = CONVERT(SYSNAME,I.NAME),

DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE

FROM SYS.ALL_OBJECTS O1,

SYS.ALL_OBJECTS O2,

SYS.ALL_COLUMNS C1,

SYS.ALL_COLUMNS C2,

SYS.FOREIGN_KEYS F

INNER JOIN SYS.FOREIGN_KEY_COLUMNS K

ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)

INNER JOIN SYS.INDEXES I

ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID

AND F.KEY_INDEX_ID = I.INDEX_ID)

WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID

AND O2.OBJECT_ID = F.PARENT_OBJECT_ID

AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID

AND C2.OBJECT_ID = F.PARENT_OBJECT_ID

AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID

AND C2.COLUMN_ID = K.PARENT_COLUMN_ID

http://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server

http://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server

http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/ 

http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/