There are two different methods to retrieve the list of Primary Keys and Foreign Keys from the 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 a list of Primary Keys and Foreign Keys from the database. I have a standard answer. I prefer method 2, 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 hands. 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 (https://blog.sqlauthority.com)
24 Comments. Leave new
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
very cool, I am totally new and this was kind of fun for seeing the exact code in my book!!
Great. Thanks for letting me know Jeremy.
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
A few years later…. Script as good as it was. Wish I could write that in an hour..
I am curious a couple of things I’ve seen.. Why is it you have selected only PK with index_id = 1? I have a batch of Pks for which the index_id is >1 .
Another issue with this is that the joins with I & IC in the PK inner join is creating duplicates. as it seems to be linking to Indexes. sysindexkeys and Key_constraints need to be used
I also noticed an issue with schema for the PK. where the incorrect schema is being selected resulting in lots of null creation scripts.
All that being said working through the code has certainly help so thank you very much!
This is where i am at so far.. As said this is just the subquery for the PK side.
/****** Object: View [dbo].[View_999] Script Date: 09/11/2013 18:07:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_999]
AS
SELECT ic.object_id, t.name AS TableName, sys.schemas.name AS schema_name, ic.is_descending_key AS is_desc, sys.key_constraints.type AS ConstraintType, i.type,
i.type_desc, i.is_primary_key, c.name AS PKColumnName, sys.key_constraints.name AS PKName, sys.key_constraints.unique_index_id, sys.sysindexkeys.indid,
sys.sysindexkeys.keyno, sys.sysindexkeys.colid
FROM sys.columns AS c INNER JOIN
sys.tables AS t ON c.object_id = t.object_id INNER JOIN
sys.index_columns AS ic INNER JOIN
sys.indexes AS i ON i.object_id = ic.object_id AND ic.index_id = i.index_id ON c.column_id = ic.column_id AND c.object_id = ic.object_id INNER JOIN
sys.key_constraints ON sys.key_constraints.parent_object_id = t.object_id INNER JOIN
sys.sysindexkeys ON sys.key_constraints.unique_index_id = sys.sysindexkeys.indid AND sys.sysindexkeys.colid = ic.column_id AND
sys.sysindexkeys.indid = ic.index_id AND sys.sysindexkeys.id = ic.object_id INNER JOIN
sys.schemas ON t.schema_id = sys.schemas.schema_id
WHERE (i.is_primary_key = 1)
GO
EXEC sys.sp_addextendedproperty @name=N’MS_DiagramPane1′, @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = “(H (1[40] 4[21] 2[20] 3) )”
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = “(H (1 [50] 4 [25] 3))”
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = “(H (1 [50] 2 [25] 3))”
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = “(H (4 [30] 2 [40] 3))”
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = “(H (1 [56] 3))”
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = “(H (2 [66] 3))”
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = “(H (4 [50] 3))”
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = “(V (3))”
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = “(H (1[56] 4[18] 2) )”
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = “(H (1 [75] 4))”
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = “(H (1[66] 2) )”
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = “(H (4 [60] 2))”
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = “(H (1) )”
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = “(V (4))”
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = “(V (2))”
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = “t”
Begin Extent =
Top = 44
Left = 1035
Bottom = 388
Right = 1287
End
DisplayFlags = 280
TopColumn = 2
End
Begin Table = “key_constraints (sys)”
Begin Extent =
Top = 0
Left = 766
Bottom = 200
Right = 966
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = “i”
Begin Extent =
Top = 0
Left = 0
Bottom = 285
Right = 197
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = “sysindexkeys (sys)”
Begin Extent =
Top = 0
Left = 535
Bottom = 129
Right = 705
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = “ic”
Begin Extent =
Top = 2
Left = 271
Bottom = 293
Right = 465
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = “c”
Begin Extent =
Top = 225
Left = 630
Bottom = 354
Right = 835
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = “schemas (sys)”
Begin Extent =
Top = 38
Left = 1339
Bottom = 150
Right = 1509
End
DisplayFlags’ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’VIEW’,@level1name=N’View_9′
GO
EXEC sys.sp_addextendedproperty @name=N’MS_DiagramPane2′, @value=N’ = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = “”
End
Begin ColumnWidths = 20
Width = 284
Width = 6540
Width = 2685
Width = 3045
Width = 1500
Width = 2160
Width = 2475
Width = 1500
Width = 3015
Width = 1500
Width = 1500
Width = 1500
Width = 555
Width = 1125
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 3165
Alias = 1920
Table = 1605
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
‘ , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’VIEW’,@level1name=N’View_9′
GO
EXEC sys.sp_addextendedproperty @name=N’MS_DiagramPaneCount’, @value=2 , @level0type=N’SCHEMA’,@level0name=N’dbo’, @level1type=N’VIEW’,@level1name=N’View_9′
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…
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.
Hopefully this is without errors… all based on John Vanda’s work
SELECT TOP ( 100 ) PERCENT
o.type ,
o.name AS NameofConstraint ,
s.name AS SchemaName ,
tbl.name AS TableName ,
o.parent_object_id AS TableID ,
parent_column_id,
referenced_column_id,
isSelfReferal = CASE WHEN o.parent_object_id = referenced_object_id THEN 1 END,
o.parent_object_id,
referenced_object_id,
DropScript = ‘ALTER TABLE [‘ + s.name + ‘].[‘ + tbl.name
+ ‘] DROP CONSTRAINT [‘
+ CASE WHEN fk.FK_Name IS NOT NULL
THEN +[FK_Name] + ‘] ‘
ELSE ‘1’
END ,
create_statement = CASE WHEN fk.FK_Name IS NOT NULL
THEN ‘ALTER TABLE [‘ + s.name + ‘].[‘
+ tbl.name
+ ‘] WITH NOCHECK ADD CONSTRAINT [‘
+ [FK_Name] + ‘] FOREIGN KEY([‘
+ fk.name + ‘]) REFERENCES [‘
+ fk.[PTable_Schema] + ‘].[‘
+ fk.referenced_table + ‘]([‘
+ fk.referencedcolumn + ‘])’
WHEN fk.FK_Name IS NULL
THEN N’ALTER TABLE [‘ + pk.pkschema_name
+ ‘].[‘ + pk.pktablename
+ ‘] ADD CONSTRAINT [‘ + pk.PKName
+ ‘] 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
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
INNER JOIN sys.tables AS tbl ON o.parent_object_id = tbl.object_id
LEFT OUTER JOIN ( SELECT ps.name AS PTable_Schema ,
rs.name AS RTable_Schema ,
fk.object_id ,
fkc.constraint_column_id AS ID ,
fkc.parent_object_id ,
fkc.parent_column_id ,
fkc.referenced_object_id ,
fkc.referenced_column_id ,
rt.name AS referenced_table ,
pt.name AS PTable_Name ,
fk.name AS FK_Name ,
pc.name AS Name ,
rc.name AS ReferencedColumn
FROM sys.tables AS rt
INNER JOIN sys.schemas AS rs ON rt.schema_id = rs.schema_id
INNER JOIN sys.tables AS pt
INNER JOIN sys.foreign_keys AS fk ON fk.parent_object_id = pt.object_id
INNER JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN sys.columns AS pc ON fkc.parent_column_id = pc.column_id
AND fkc.parent_object_id = pc.object_id
INNER JOIN sys.columns AS rc ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.object_id
INNER JOIN sys.schemas AS ps ON pt.schema_id = ps.schema_id ON rt.object_id = rc.object_id
) AS FK ON FK.object_id = o.object_id
LEFT OUTER JOIN ( SELECT c.name AS PKColumnName ,
sys.sysindexkeys.id AS PKID ,
t.object_id AS PKTableID ,
t.name AS PKTableName ,
sys.schemas.name AS PKschema_name ,
sys.key_constraints.type AS ConstraintType ,
i.type ,
i.type_desc ,
i.is_primary_key ,
sys.key_constraints.name AS PKName ,
sys.key_constraints.unique_index_id ,
sys.sysindexkeys.indid ,
sys.sysindexkeys.keyno ,
sys.sysindexkeys.colid ,
i.is_primary_key AS Expr1 ,
CASE WHEN dsi.type = ‘FG’ THEN dsi.name
ELSE N”
END AS FileGroup ,
ic.is_descending_key AS is_desc ,
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.name AS [column]
FROM sys.columns AS c
INNER JOIN sys.tables AS t ON c.object_id = t.object_id
INNER JOIN sys.index_columns AS ic
INNER JOIN sys.indexes AS i ON i.object_id = ic.object_id
AND ic.index_id = i.index_id ON c.column_id = ic.column_id
AND c.object_id = ic.object_id
INNER JOIN sys.key_constraints ON sys.key_constraints.parent_object_id = t.object_id
INNER JOIN sys.sysindexkeys ON sys.key_constraints.unique_index_id = sys.sysindexkeys.indid
AND sys.sysindexkeys.colid = ic.column_id
AND sys.sysindexkeys.indid = ic.index_id
AND sys.sysindexkeys.id = ic.object_id
INNER JOIN sys.schemas ON t.schema_id = sys.schemas.schema_id
LEFT OUTER JOIN sys.stats AS s ON sys.key_constraints.object_id = s.object_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON i.data_space_id = dsi.data_space_id
WHERE ( i.is_primary_key = 1 )
) AS pk ON o.parent_object_id = pk.PKTableID
WHERE ( o.type_desc IN ( ‘FOREIGN_KEY_CONSTRAINT’, ‘PRIMARY_KEY_CONSTRAINT’ ) )
ORDER BY TableName
I finally got around to doing this:
thanks for this post I needed these tips
I prefer method 1.
Because you can call it while being connected to another database.
Example:
If you execute
SELECT * FROM .INFORMATION_SCHEMA.KEY_COLUMN_USAGE
or better yet:
Primary Keys:
SELECT * FROM .INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
Foreign Keys:
SELECT * FROM .INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’
with the connection opened to or any other database than , you will be able to have the results, which is not the case for other methods.
Thanks for your comment Sébastien
Note that part of the content has been filtered in my previous post because of some special characters.
Of course I meant:
DatabaseName.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
instead of:
.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
and the last sentence is:
with the connection opened to or any other database than DatabaseName, you will be able to have the results, which is not the case for other methods.
Too bad I can’t edit it.
Pinal what is the 3rd method?
I meant method 2.
Hi Pinal,
I sent a modified version of the script back, they came back notifying that trying to make primary keys with a fillfactor of 0 (unset) was throwing errors.
So I changed Line 28 to read:
+ ‘SORT_IN_TEMPDB = OFF’+CASE WHEN Ix.FIll_factor>0 THEN ‘, FILLFACTOR =’ + CAST(ix.fill_factor AS VARCHAR(3)) ELSE ” END AS IndexOptions
This let things come through successfully for them.
Thought I’d throw the note up here in case it solves this for anyone else.
Thanks for the tip.