Find out tables with Primary key

One of the questions asked in the forum is “How do I find out the tables that have primary key defined?”

Well. There can be several methods. These are two among them

Let us create the following tables in tempdb database

use tempdb

create table test1
(
id int primary key,
name varchar(100)
)
GO
create table test2
(
id int ,
name varchar(100)
)

Method 1 : Query on INFORMATION_SCHEMA.TABLE_CONSTRAINTS system catalog view

SELECT
	table_name
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
	CONSTRAINT_TYPE='PRIMARY KEY'

Method 2 : Query on SYS.TABLES system table

SELECT
	name AS table_name
FROM
	SYS.TABLES
WHERE
	OBJECTPROPERTY(OBJECT_ID,'TABLEHASPRIMARYKEY') = 1

Both the queries return the following result

table_name
--------------
test1
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s