Hi

One major aspect of database design that has been debated a lot is having primary keys. Normally a primary key would be the entry point to the data to perform DML action. Not having primary keys are not just bad for design purpose, but that can also cause major problems in replication. The problem can be even severe when we are importing when we are trying to import data from other data source and there might be some duplicate data there.

But Lets say you have a database already designed and you feel there are some tables that do not have any primary key. You have over 200 tables in the database so to find out manually which table has primary key, which do not can be more than tedious. But there is an easy way out. You can query the system tables to find out these results. Below are two queries to provide the list of tables with and without primary key.

--Query to find all the tables with primary key

SELECT u.name, o.name, i.name
FROM sysobjects o
INNER JOIN sysindexes i ON o.id = i.id
INNER JOIN sysusers u ON o.uid = u.uid
WHERE (i.status & 2048)<>0

--Query to find tables without any primary key

SELECT u.name, o.name
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
WHERE xtype = 'U'
AND NOT EXISTS
(SELECT i.name
FROM sysindexes i
WHERE o.id = i.id
AND (i.status & 2048)<>0)

Thanks
Vikram