SQL Server : List Tables in a Database with / Without Primary Key


It is a good practice to have one column that should have a primary key. A primary key also help to prevent table from duplication. You can also add Auto Increment to the primary key column. Most primary keys will be foreign keys in another table as well as used in multiple indexes. You also need to make sure that the smaller your key, the smaller the index, the less pages in the cache. So how can you determine what tables have primary keys and what tables do not have primary keys?

You can execute below script to identify the tables with or without primary key and add the primary key into tables it does not have primary key.

1. Tables with PRIMARY KEY

SELECT T.name ‘Table witH Primary Key’
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 1 AND type = ‘U’

2. Tables without PRIMARY KEY

SELECT T.name ‘Table without Primary Key’
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,’TableHasPrimaryKey’) = 0 AND type = ‘U’


<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.