Caveman's Blog

My commitment to learning.

SQL Server: List of tables without a primary key

leave a comment »


Following is a simple query that can used to list all the tables that do not have a primary key.


select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where objectproperty(object_id(table_name),'TableHasPrimaryKey')=0
ORDER BY TABLE_NAME

This is another query that I found on the web [1] that can list if the table has an identity column, primary key and a clustered index.


select tab.TABLE_SCHEMA, tab.table_name,
IDNTY = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasIdentity'),
CLSTRD = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasClustIndex'),
PK = objectproperty(object_id(tab.TABLE_SCHEMA + '.' + tab.table_name), 'TableHasPrimaryKey')
from information_schema.tables tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col ON tab.TABLE_NAME = col.TABLE_NAME AND tab.TABLE_SCHEMA = col.TABLE_SCHEMA AND
COLUMNPROPERTY ( object_id(tab.TABLE_SCHEMA + '.' + tab.table_name) , col.COLUMN_NAME , 'IsIdentity' ) = 1
where table_type = 'base table'

References:
1. SQL Server Forums

Advertisements

Written by cavemansblog

June 23, 2010 at 10:27 am

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

%d bloggers like this: