We can write a query like below to get all the Tables with no Primary key constraint:
SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
AND type = 'U'
We can write a query like below to get all the Tables with Primary key constraint:
SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
AND type = 'U'
Let us understand this with example:
CREATE DATABASE SqlHintsDemoDB
GO
USE SqlHintsDemoDB
GO
/*Let us create Customers table with Primary Key.*/
CREATE TABLE dbo.Customers (
CustomerId int IDENTITY (1, 1)
PRIMARY KEY CLUSTERED NOT NULL ,
FirstName Varchar(50),
LastName Varchar(50))
GO
/*Let us create Orders Table without any primary key.*/
CREATE TABLE dbo.Orders (
OrderId int IDENTITY (1, 1) NOT NULL ,
CustomerId int NOT NULL ,
CreationDT DATETIME NOT NULL)
GO
Now let us run the queries to get the list of all Tables with or without Primary Keys and verify the result:
you can use this query to know which have Primary Key
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
hello.
thanks for sharing the script ..
got the list of tables .. and then what is the next step? as I got 3295 rows!
Found some mistreated tables in my database (>100 tables) in a snap. Great stuff, thanks & namaste! 🙂
Thank you
Awesome stuff, now I have a quick question. I list the tables next to the fields that conform the primary key and its data type, is this possible?
If you want to create Primary Keys on all tables in SQL Server that don’t have PKs, this SQL will generate all the PK create code.
I use this on databases i inherit that are missing lots of keys…
Hope this is of use!
@nickhac
select * ,
‘ALTER TABLE ‘ + SchemaName + ‘.’ + Tablename + ‘ ADD CONSTRAINT PK_’ + Tablename + ‘ PRIMARY KEY CLUSTERED (‘ +Table_Column_FirstWithIDInName + ‘)’ ScriptToCreatePrimaryKey
from (
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) SchemaName, OBJECT_NAME(OBJECT_ID) AS Tablename,
(SELECT top 1 Column_Name FROM INFORMATION_SCHEMA.COLUMNS where table_name = T.name and column_name like ‘%ID%’ order by Ordinal_Position) Table_Column_FirstWithIDInName
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0 AND type = ‘U’
) TblsWithoutPK