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)
38 Comments. Leave new
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
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.
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,
Pinal,
You are absolutely the best SQL server authority.
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
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’
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.
Thanks for the post.
It is easy to find the primary keys columns in all tables in a database.
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 ?
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.
Query looks correct. Are you not getting expected results?
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