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. The question is how to find tables with primary key constraint in a database.
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
Tips for Listing Tables With Primary Key Constraint
The script returns one row per key column, so a table with a composite primary key shows up more than once. Do not be surprised by that. If you want the key columns in the right order, add ic.key_ordinal to the SELECT and sort by the table name and that column.
A few more tips I use when I run this kind of check:
- Add
OBJECT_SCHEMA_NAME(ic.object_id)so you can tell apart two tables with the same name in different schemas. - The same list is available from
sys.key_constraintswheretype = 'PK', or fromINFORMATION_SCHEMA.TABLE_CONSTRAINTSwhereCONSTRAINT_TYPE = 'PRIMARY KEY'. Use whichever reads easier for you. - The reverse question is often more useful.
OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0againstsys.tablesgives you every table that has no primary key at all.
Why do I care about this list? A table without a primary key can collect duplicate rows without anyone noticing, and transactional replication and many tools expect a key to find a single row. When I review a new database, this is one of the first checks I run. I look at the tables without a key, ask the developer why, and usually a key gets added before the data grows.
Also remember that a primary key creates a clustered index by default, unless the table already has one or you ask for a nonclustered key. That is why the script reads from sys.indexes: the key and its index go together.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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!!
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