SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database

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

SQL SERVER - Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database keycertification 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)

Quest

SQL Constraint and Keys, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Four Different Ways to Find Recovery Model for Database
Next Post
SQLAuthority News – Whitepaper – Using the Resource Governor

Related Posts

24 Comments. Leave new

  • ashishgilhotra
    July 17, 2009 2:44 pm

    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

    Reply
  • 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

    Reply
    • champagne_charly
      November 9, 2013 11:40 pm

      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

      Reply
  • 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.

    Reply
  • I prefer method 3 ! what is the method 3 ??

    Reply
  • Behtash Moradi
    July 18, 2009 2:38 pm

    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

    Reply
  • 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…

    Reply
  • 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.

    Reply
  • scottie2hottie
    July 29, 2011 6:42 pm

    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

    Reply
  • 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 + “‘”;

    Reply
  • Hi My prior Post above is to be used to create insert , updates dynamically oops

    Reply
  • Andrew Whettam
    March 6, 2013 6:37 pm

    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.

    Reply
  • champagne_charly
    November 10, 2013 7:49 am

    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

    Reply
  • nakulvachhrajani
    March 1, 2014 8:00 pm

    I finally got around to doing this:

    Reply
  • thanks for this post I needed these tips

    Reply
  • Sébastien Sevrin
    June 25, 2015 3:31 pm

    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.

    Reply
    • Thanks for your comment Sébastien

      Reply
      • Sébastien Sevrin
        July 2, 2015 5:38 pm

        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.

  • Mridula Pandit
    June 10, 2016 7:21 pm

    Pinal what is the 3rd method?

    Reply
  • 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.

    Reply

Leave a Reply