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 (http://blog.SQLAuthority.com)

About these ads

37 thoughts on “SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

  1. hi
    grt query !
    but i have a different question
    i want to know whether i can delete a specfic record in sql server 2005 prior to execute the delete query.
    mean if the record will have foreign key constraint and with the records child records will exists then db will not allow me to delete that record.
    i want to know whether master table have child recrods or not
    is there any query to do so ?
    thanks in advance.
    tgc
    faheem

  2. Whilst the above may/does work for SQL2k5 ,
    The following also works for SQL2k DB.

    I had to crib this together because some of our processes are ‘so generic’ they don;t know what table they’ll be passed let alone what the primary key will be (for use in DML)

    SELECT SC.Name, SC.COLID
    FROM SYSOBJECTS SO
    INNER JOIN SYSINDEXKEYS SI ON SO.ID = SI.ID AND SI.INDID = 1
    INNER JOIN SYSCOLUMNS SC ON SO.ID = SC.ID AND SC.COLID=SI.COLID
    WHERE SO.NAME= @MyTableName
    AND SO.XTYPE=’U’

    Corrections are welcome.

  3. hi
    i read about ur article do you know difference between
    rowlevel primary key and column level if yes send me an example of this also
    thanks
    lalit bohra

  4. Pingback: SQL SERVER - 2005 Find Table without Clustered Index - Find Table with no Primary Key Journey to SQL Authority with Pinal Dave

    • HI PINAL ,

      WE CAN ALSO WRITE IN OTHER WAY FOR FINDING THE PRIMARY CONSTRAINT NAMES …

      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’

  5. How do u find out the which column is the primary key column in a table if the we are not able to view the design or the script the tabel?

    Thanks,
    Bharath

  6. Why we see all related table data on a primary key. for example a employe table e1 has a primary key id and e2 has f. k id and e3 has also id as a f.k. we search all data on 3 tables e1 e2 e3 on primary key without gaves table e2 and e3 names only e1 and primary key

  7. I want to know how to write a constraint i.e a primary key in already defined table.Alter syntax with constraints

    • ALTER TABLE
      ADD CONSTRAINT PRIMARY KEY (COLUMN_NAME_1, [COLUMN_NAME_2], [COLUMN_NAME_n])

      where n is the nth number, as a Primary Key can have more than one Column

    • ALTER TABLE TABLE_NAME
      ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN_NAME_1, [COLUMN_NAME_2], [COLUMN_NAME_n])

      where n is the nth number, as a Primary Key can have more than one Column

  8. I’m havin two database, could i map a foeign key constraint in the other database
    e.g
    database A->table xyz.id set primary key
    I’wanna make foreign key to
    database B-> table xyz.pid

    is it possible

  9. Hello Shwetang,

    What I read on internet, is it is not possible to create a foriegn key constraint referencing table outside database.

    I tried myself creating this but had no luck…

    The only solution to your problem is triggers.

    or create child table in the same database as parent table.

    Thanks,
    Imran

  10. I have one parent table and has 10 child tables.

    I would like to delete the child table records first then perent
    database level, through constraints and triggers.

    All these tables has forign key constrains.

    Please advise the better way. I am using sql server 2005.

    • Hi Kash,

      you can delete child table records first then parent table through constraint on delete cascade constraints.

      You can set this constraints at the same time while creating relationship between two tables.

      Thanks
      KENSAi

  11. Hi Prasad,

    To get the list of all indexes of a table use the below query:

    SELECT ind.*
    FROM sys.indexes ind INNER JOIN sys.tables tbl
    ON ind.object_id = tbl.object_id
    WHERE tbl.name = ‘yourTableName’

    To get the list of all index in a database use the sys.indexes table.

    Regards,
    Pinal Dave

  12. I have two tables one of them(table A) has 2 Primary keys (Composite keys) and another one (Table B) has own primary key and a Foreign key which is one of the existing primary key of the Table A.

    I am going to make a relation between table A and Table B in this relation Table A is my Primary key Table and Table B is my Foreign key table,and in Foreign key table i select one primary key of Table A which should be set as a primary key field in Table A as Primary key table. when i want to make this relation i get this Error : ‘ The column in the table don’t match an Existing primary key or UNIQUE constraint.

    Help me please

  13. Hemadri

    Right click on the specified table choose design option. Right click on the column to which you need to create primary key. You will find set primary key option. Just select that.

  14. 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!!

  15. 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

  16. 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.

  17. 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,

  18. 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’

  19. 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.

  20. 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 ?

  21. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s