SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

My article SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key has received following question many times. I have deleted similar questions and kept only latest comment there.

In SQL Server 2005 How to Find Tables With Primary Key Constraint in Database?

Script to find all the primary key constraint in database:
USE AdventureWorks;
GO
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

In SQL Server 2005 How to Find Tables With Foreign Key Constraint in Database?
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database
Next Post
SQLAuthority News – Interesting Read – Using A SQL JOIN In A SQL UPDATE/Delete Statement – Ben Nadel

Related Posts

38 Comments. Leave new

  • For sec I thought I should call you god. But I think praying god to give you long life and everything you want in life is more appropriate :-) you are simply awesome buddy!!

    Reply
  • Hi,

    I was working on a script that will let me know the order in which I can insert data in to the tables , so that they do not encounter the referential integrity problem. But I have a table that has got both the primary key and foreign key on the same column and as a result my script show the order of insertion for this table to be 3 , where as when I check it with the help of view dependencies in the management studio its order is 4. any suggestion are appreciated.\
    — Script———-
    DECLARE @Iteration int
    SET @Iteration = 1

    Select Table_Name,NULL AS InsertOrderRank into #Table
    from Information_Schema.Tables Where Table_Type = ‘BASE TABLE’

    Select FK.Constraint_Name as FKConstraintName,
    FK.Table_Name as TableName,
    CUF.Column_Name as ColumnName,
    PK.Table_Name as ReferringTableName,
    CUP.Column_Name as ReferringTableColumnName
    into #FKColumns
    from Information_Schema.REFERENTIAL_CONSTRAINTS RC– Foreignkeys
    JOIN Information_Schema.TABLE_CONSTRAINTS FK
    ON RC.Constraint_Name = FK.Constraint_Name
    JOIN Information_Schema.TABLE_CONSTRAINTS PK
    ON RC.Unique_Constraint_Name = PK.Constraint_Name
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CUF
    ON FK.Constraint_Name = CUF.Constraint_Name
    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CUP
    ON PK.Constraint_Name = CUP.Constraint_Name

    update T
    Set InsertOrderRank = 1
    From #Table T
    LEFT JOIN #FKColumns F
    on T.Table_Name = F.TableName
    WHERE F.TableName IS NULL

    WHILE ((Select Count(1) from #Table where InsertOrderRank IS NULL) > 0)
    BEGIN

    UPDATE T1
    Set InsertOrderRank = @Iteration + 1
    FROM #Table T1
    JOIN
    (SELECT Distinct T.Table_Name
    From #Table T
    INNER JOIN #FKColumns F
    on T.Table_Name = F.TableName
    INNER JOIN #Table T2
    on T2.Table_Name = F.ReferringTableName
    Group By T.Table_Name
    Having Count(Distinct ISNULL(T2.InsertOrderRank,0)) = @Iteration) as T2
    on T1.Table_Name = T2.Table_Name

    SET @ITeration = @Iteration + 1

    END

    Select * from #Table
    Order By InsertOrderRank

    Thanks

    Reply
  • hi,
    i want to write a function in SQL. i will pass table name and value of the pk. on basis of these two parameter i would like to get the name of the table(s) that contains the specified PK as FK.

    thanks.

    Reply
  • hello,

    i am trying to write a function in MS SQL which i will pass table_name and PK value and the function will return me the table(s) where that specified PK of the table is used.

    like we have two tables;
    item purchase
    itemID (PK) itemID (FK)
    100 100

    the function will return table name purchase if the (itemID=100) is used in it

    thanks,

    Reply
  • Pinal,

    You are absolutely the best SQL server authority.

    Reply
  • Vijay Anand Madhuranaygam
    January 12, 2012 6:01 pm

    Shall we use this Query?

    select [name] from SYS.TABLES where [name] not in (
    select distinct(TABLE_NAME) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS)

    The above query will return all the heap tables.

    Thanks & regards,
    Vijay

    Reply
  • rakesh7044@gmail.com
    July 17, 2012 4:52 pm

    SELECT IT.TABLE_NAME ,ITC.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLES it join INFORMATION_SCHEMA.TABLE_CONSTRAINTS itc ON IT.TABLE_NAME=ITC.TABLE_NAME
    WHERE OBJECTPROPERTY(OBJECT_ID(IT.TABLE_NAME),’TableHasPrimaryKey’)=1 AND TABLE_TYPE=’BASE TABLE’ and itc.CONSTRAINT_TYPE=’primary key’

    Reply
  • Thank you all for the useful info, but I have a concern:

    can we get the list of primary key columns in 1 row. for example if we have a composite primary key consists of 4 columns, the above query will retrive all 4 of them in 4 rows,

    question: could it be in 1 row.

    thanx.

    Reply
  • Thanks for the post.

    It is easy to find the primary keys columns in all tables in a database.

    Reply
  • Manish Solanki
    August 9, 2013 5:49 pm

    Dear Sir,
    I have Primary key Name and suppose i don’t know table name.
    How can i Get Table Name or Table Information from Unique or Primary Key ?

    Reply
  • Dear Sir ,

    I got the result of Tables With Primary Key Constraint in Database as below

    select OBJECT_NAME(OBJECT_ID) from sys.indexes where is_primary_key = 1
    Please correct me if I’m wrong.

    Reply
  • Hi Sir,

    I know one table name in that I have one field, This same field exists in several tables. So now I want to get the table name where this field name is declared as PRIMARY KEY ????

    Thanks,
    Suresh V

    Reply

Leave a Reply

Menu