There are two different methods of retrieving the list of Primary Keys and Foreign Keys from database.
Method 1: INFORMATION_SCHEMA
SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO
Method 2: sys.objects
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN ('FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT')
GO
I am often asked about my preferred method of retrieving list of Primary Keys and Foreign Keys from database. I have a standard answer. I prefer method 3, which is querying sys database. The reason is very simple. sys. schema always provides more information and all the data can be retrieved in our preferred fashion with the preferred filter.
Let us look at the example we have on our hand. When Information Schema is used, we will not be able to discern between primary key and foreign key; we will have both the keys together. In the case of sys schema, we can query the data in our preferred way and can join this table to another table, which can retrieve additional data from the same.
Let us play a small puzzle here. Try to modify both the scripts in such a way that we are able to see the original definition of the key, that is, create a statement for this primary key and foreign key.
If I get an appropriate answer from my readers, I will publish the solution on this blog with due credit.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
For Foreign keys which is something difficult …But i take refrence from your blog so may be its like this….
select ‘ALTER TABLE ‘+OBJECT_NAME(f.parent_object_id)+ ‘ ADD CONSTRAINT’ + f.name + ‘ FOREIGN KEY’+'(‘+COL_NAME(fc.parent_object_id,fc.parent_column_id)+’)'+’REFRENCES ‘+OBJECT_NAME (f.referenced_object_id)+’(‘+COL_NAME(fc.referenced_object_id,fc.referenced_column_id)+’)’ as Scripts
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
This is a bit long winded, but it was a fun hour making it.
SELECT OBJECT_NAME(o.[object_id]) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType,
CASE
WHEN fk.ForeignKey_Name IS NOT NULL THEN
‘ALTER TABLE [' + SCHEMA_NAME([schema_id]) + ‘].['
+ OBJECT_NAME([parent_object_id])+ ‘] WITH NOCHECK ADD CONSTRAINT ['
+ [ForeignKey_Name] + ‘] FOREIGN KEY([' + fk.name + ']) REFERENCES ['
+ fk.[Table_Schema] + ‘].[' + fk.referenced_table + '](['
+ fk.referencedcolumn + '])’
WHEN fk.ForeignKey_Name IS NULL THEN
N’ALTER TABLE [' + pk.table_schema + '].[' + pk.table_name + '] ADD CONSTRAINT ['
+ pk.NAME + '] PRIMARY KEY CLUSTERED ([' + pk.[column] + ‘] ‘ +
+ CASE
WHEN pk.is_desc = 0 THEN ‘ASC’
ELSE ‘DESC’
END
+’)WITH
( PAD_INDEX = ‘ + CASE
WHEN pk.PadIndex = 0 THEN ‘OFF’
ELSE ‘ON’
END
+ ‘, STATISTICS_NORECOMPUTE = ‘ + CASE
WHEN pk.NoAutomaticRecomputation = 0 THEN ‘OFF’
ELSE ‘ON’
END
+ ‘, SORT_IN_TEMPDB = OFF’ +
+ ‘, IGNORE_DUP_KEY = ‘ + CASE
WHEN pk.IgnoreDuplicateKeys = 0 THEN ‘OFF’
ELSE ‘ON’
END
+’, ONLINE = OFF’ +
+’, ALLOW_ROW_LOCKS = ‘ + CASE
WHEN pk.DisallowRowLocks = 0 THEN ‘OFF’
ELSE ‘ON’
END
+’, ALLOW_PAGE_LOCKS = ‘ + CASE
WHEN pk.DisallowPageLocks = 0 THEN ‘OFF’
ELSE ‘ON’
END
+’, FILLFACTOR = ‘ + CASE
WHEN pk.[FillFactor] = 0 THEN ‘100′
ELSE CONVERT(NVARCHAR, pk.[FillFactor])
END
+’) ON [' + pk.[filegroup]
+’]’
END [create_statement]
FROM sys.objects o
LEFT JOIN ( SELECT
cstr.[object_id],
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
cstr.name AS [ForeignKey_Name],
fk.constraint_column_id AS [ID],
cfk.name AS [Name],
crk.name AS [ReferencedColumn],
OBJECT_NAME(fk.referenced_object_id) [referenced_table]
FROM sys.tables AS tbl
INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.[object_id]
INNER JOIN sys.foreign_key_columns AS fk ON fk.constraint_object_id=cstr.[object_id]
INNER JOIN sys.columns AS cfk ON fk.parent_column_id = cfk.column_id and fk.parent_object_id = cfk.[object_id]
INNER JOIN sys.columns AS crk ON fk.referenced_column_id = crk.column_id and fk.referenced_object_id = crk.[object_id]) fk ON fk.[object_id] = o.[object_id]
LEFT JOIN ( SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
OBJECT_ID(tbl.[name]) [table_id],
i.name AS [Name],
CASE WHEN ‘FG’=dsi.type THEN dsi.name ELSE N” END AS [FileGroup],
i.ignore_dup_key AS [IgnoreDuplicateKeys],
i.fill_factor AS [FillFactor],
CAST(INDEXPROPERTY(i.[object_id], i.name, N’IsPadIndex’) AS bit) AS [PadIndex],
~i.allow_row_locks AS [DisallowRowLocks],
~i.allow_page_locks AS [DisallowPageLocks],
s.no_recompute AS [NoAutomaticRecomputation],
c.[column],
c.[is_desc]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.[object_id]=tbl.[object_id])
INNER JOIN (
SELECT ic.[object_id], c.[name] [column], ic.is_descending_key [is_desc]
FROM sys.index_columns ic
INNER JOIN sys.indexes i ON i.[object_id] = ic.[object_id] AND i.index_id = 1 AND ic.index_id = 1
INNER JOIN sys.tables t ON t.[object_id] = ic.[object_id]
INNER JOIN sys.columns c ON c.[object_id] = t.[object_id] AND c.column_id = ic.column_id
) AS c ON c.[object_id] = i.[object_id]
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.[object_id] AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.[object_id] = i.[object_id] AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.[object_id] = i.[object_id]
WHERE k.TYPE = ‘PK’
) pk ON o.[parent_object_id] = pk.table_id
WHERE type_desc IN (‘FOREIGN_KEY_CONSTRAINT’,'PRIMARY_KEY_CONSTRAINT’)
ORDER BY [TableName] asc
GO
When you copy/paste the code above, it’s changing some of the sql ticks and double-ticks to apostrophe’s, backwards apostrophes, and quotations. You’ll have to replace those out to run the query.
I prefer method 3 ! what is the method 3 ??
I guess something is wrong with one of suggested methods?
I ran both methods on same database and found different result.First one returned 267 record and second ones returned only 254?
Is it possible?
Thanks
BEHTASH
My response could be excluding a few; Not sure where though. My response boils down to:
SELECT …
FROM sys.objects
LEFT JOIN … {FK subquery}
LEFT JOIN … {PK subquery}
WHERE type_desc IN (’FOREIGN_KEY_CONSTRAINT’,’PRIMARY_KEY_CONSTRAINT’)
ORDER BY [TableName] ASC
Each of the left join sets should contain less records than the sys.objects set, but, since it’s a left join, the set from sys.objects shouldn’t be excluding anything. I could be missing something…
[...] of Primary Key or Foreign Key of database. I have already written an article for the same here : SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database . I am looking for T-SQL script that generates Primary Key from the existing table for all tables [...]