sys.objects | SQL Server Portal

Posts Tagged ‘sys.objects’

SQL SERVER – Multiple ways to find all the tables that do not have a primary key?

Posted in Solutions, SQL SERVER, tagged OBJECTPROPERTY, raresql, SQL, SQL Server, SQL Server 2012, sys.key_constraints, sys.objects, sys.tables, TableHasPrimaryKey on February 16, 2014| Leave a Comment »

Today, I was searching for some script from my library and I came across this script (How do I find all the tables that do not have a primary key?) . I usually use this script, once I complete my deployment on any SQL Server to check if I missed to create primary key on any table. There are multiple ways to achieve it. However, I will discuss three of them in this article.

METHOD 1 :
This is the shortest method to find all the tables that do not have a primary key. In this method, we need to use OBJECTPROPERTY to check each table property and filter it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE
OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
GO
--OUPUT

METHOD 2 :
This is one of my favorite methods because I do not need to remember any other view name, I can browse all the data from sys.objects.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM
sys.objects
WHERE [type]='U' AND object_id
NOT IN (
SELECT parent_object_id FROM sys.objects
WHERE [type]='PK'
)
GO
--OUPUT

METHOD 3 :
In this method, we need to use two system views (sys.tables & sys.key_constraints) to achieve it.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
SCHEMA_NAME(schema_id) AS [Schema name]
, name AS [Table name]
FROM sys.tables
WHERE object_id NOT IN
(
SELECT parent_object_id
FROM sys.key_constraints
WHERE type = 'PK'
);
GO
--OUPUT

Read Full Post »