Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : How to Determine if a Table has a Primary Key
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

To determine if a table has a primary key, run the following statement:

SELECT OBJECTPROPERTY(OBJECT_ID(N'[dbo].[User]'), 
'TableHasPrimaryKey')

A value of 1 means that the specified table has a Primary Key constraint while a value of 0 means the table doesn’t have a Primary Key constraint.  A value of NULL means either the table doesn’t exist or the object name passed is not a table.

To get a list of tables that doesn’t have a Primary Key constraint, run the following statement:

SELECT [Name]
FROM [sys].[objects]
WHERE [Type_Desc] = 'USER_TABLE' AND
      OBJECTPROPERTY([Object_ID], 'TableHasPrimaryKey') = 0
ORDER BY [Name]

Back to Tip of the Day List Next Tip