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)
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 [...]
This retrieves the key names, but does not retrieve the field names of the keys. We need this for ASP.NET to dynamically generate the INSERT, UPDATE, and DELETE commands for a web interface that can edit data of any server database.
SELECT OBJECT_NAME(parent_object_id) Parent,
COL_NAME(parent_object_id, parent_column_id) ParentCol,
OBJECT_NAME(referenced_object_id) Child,
COL_NAME(referenced_object_id, referenced_column_id) ChildCol
FROM sys.foreign_key_columns
i have used this for years…works great for dynamic sql in .c#
cmd.CommandText = “select upper(c.Name) as Name,” +
“t.Name as Type,” +
“c.max_length as [Len],” +
“c.Scale as Scale,”+
“c.Precision as Precision,” +
“c.is_nullable,” +
“c.is_ansi_padded,” +
“c.is_rowguidcol,” +
“c.is_identity ,” +
“case when c.name =cu.Column_Name and tc.constraint_type=’primary key’ then 1 else 0 end as PrimaryKey ” +
“from sys.tables s with (nolock) ” +
“INNER JOIN sys.columns c with (nolock) ON s.object_id = c.object_id ” +
“inner join sys.types t with (nolock) on t.user_type_id=c.user_type_id ” +
“left join INformation_schema.KEY_COLUMN_USAGE cu with (nolock) on cu.column_name=c.name and cu.table_name=’” + TableName + “‘ “+
“left join Information_schema.Table_Constraints tc with (nolock) on tc.table_name=cu.Table_Name “+
” and tc.constraint_name=cu.constraint_name and Constraint_type=’primary key’ “+
“where s.name = ‘” + TableName + “‘”;
Hi My prior Post above is to be used to create insert , updates dynamically oops
Here are a couple of statements to generate rename statements for primary and foreign keys, assuming the following naming rules:
Primary Key: PK_ + table_name + _ + column_name_1 + _ + column_name_2 …
Foreign Key: FK_ + referencing_table + _ + column_name_1 + _ + column_name_2 … _ + referenced table
– GENERATE RENAME STATEMENTS – PRIMARY KEYS
SELECT ‘EXEC sp_rename ”’ + CC.TABLE_SCHEMA + ‘.’ + CC.CONSTRAINT_NAME + ”’, ”PK_’ + CC.TABLE_NAME + ‘_’ + REPLACE(CC.ColumnConcatenation, ‘,’, ‘_’) + ”’;’
FROM (
SELECT CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, STUFF(
(SELECT ‘,’ + COLUMN_NAME AS [text()]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
FOR XML PATH(”)), 1, 1, ” ) AS ColumnConcatenation
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
WHERE B.CONSTRAINT_NAME LIKE ‘%_PK’
GROUP BY CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME) AS CC
ORDER BY CC.TABLE_SCHEMA, CC.TABLE_NAME;
– GENERATE RENAME STATEMENTS – FOREIGN KEYS
– Only works for FKs that reference a PK, not a Unique Constraint
SELECT ‘EXEC sp_rename ”’ + FK_TableSchema + ‘.’ + FK_Constraint_Name + ”’, ”FK_’ + FK_TableName + ‘_’ + REPLACE(FkColumnConcatenation, ‘,’, ‘_’) + ‘_’ + PK_TableName + ”’;’
– select *
FROM (
SELECT
B.TABLE_SCHEMA AS FK_TableSchema,
B.TABLE_NAME AS FK_TableName,
B.CONSTRAINT_NAME AS FK_Constraint_Name,
STUFF(
(SELECT ‘,’ + COLUMN_NAME AS [text()]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
FOR XML PATH(”)), 1, 1, ” ) AS FkColumnConcatenation,
PkRef.TABLE_SCHEMA AS PK_TableSchema,
PkRef.TABLE_NAME AS PK_TableName,
PkRef.CONSTRAINT_NAME AS PK_ConstraintName,
STUFF(
(SELECT ‘,’ + COLUMN_NAME AS [text()]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
WHERE C.TABLE_SCHEMA = PkRef.TABLE_SCHEMA
AND C.TABLE_NAME = PkRef.TABLE_NAME
AND C.CONSTRAINT_NAME = PkRef.CONSTRAINT_NAME
FOR XML PATH(”)), 1, 1, ” ) AS PkColumnConcatenation
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
ON B.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
AND B.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PkRef
ON RC.UNIQUE_CONSTRAINT_NAME = PkRef.CONSTRAINT_NAME
AND RC.CONSTRAINT_SCHEMA = PkRef.CONSTRAINT_SCHEMA
WHERE B.CONSTRAINT_NAME LIKE ‘%_FK%’
GROUP BY
B.CONSTRAINT_NAME, B.TABLE_SCHEMA, B.TABLE_NAME,
PkRef.CONSTRAINT_NAME, PkRef.TABLE_NAME, PkRef.TABLE_SCHEMA
) Sub
ORDER BY Sub.FK_TableSchema, Sub.FK_TableName
I used INFORMATION_SCHEMA rather than system views because it is simpler for the column name concatenation.