Posted by Erland Sommarskog on 06/09/07 08:13

Danny (dlapitan@gmail.com) writes:
> We imported a bunch of tables from a database and realized that the
> primary keys weren't copied to the destination db. In order to re-
> create the keys, we need to know which tables have them. Is there a
> command that I can use (on the source db) to find out which tables
> contain primary keys? The db has hundreds of tables and I'd rather not
> go through each one to see which has a primary key.

Hopefully all tables have primary keys!

It would have helped if you had said which version of SQL Server you are
using. The query below will run on both SQL 2000 and SQL 2005, but the
old system tables are deprecated on SQL 2005, so had I known you were
using SQL 2005, I would have used the new catalog views instead.

If you did not bring over the primary keys, I suspect that no indexes at
all were copied. This query lists all indexes in a database, and the
column ispk indicates that the index is a primary key. The column
isuniqueconst indicates whether the index is a UNIQUE constraint.

Note that the query as I've written it, will only include the first
five columns in the index. Neither does include information about
ascending/descening, and other less commonly used index properties.

SELECT o.name, i.name,
isclustered = Indexproperty(o.id, i.name, 'IsClustered'),
isunique = Indexproperty(o.id, i.name, 'IsUnique'),
ispk = CASE WHEN o2.xtype = 'PK' THEN 1 ELSE 0 END,
isuniqueconst = CASE WHEN o2.xtype = 'UQ' THEN 1 ELSE 0 END,
cols = ik.col1 + coalesce(', ' + ik.col2, '') +
coalesce(', ' + ik.col3, '') + coalesce(', ' + ik.col4, '') +
coalesce(', ' + ik.col5, '')
FROM sysobjects o
JOIN sysindexes i ON o.id = i.id
LEFT JOIN sysobjects o2 ON o2.name = i.name
AND o2.parent_obj = o.id
JOIN (SELECT ik.id, ik.indid,
col1 = MIN(CASE ik.keyno WHEN 1 THEN c.name END),
col2 = MIN(CASE ik.keyno WHEN 2 THEN c.name END),
col3 = MIN(CASE ik.keyno WHEN 3 THEN c.name END),
col4 = MIN(CASE ik.keyno WHEN 4 THEN c.name END),
col5 = MIN(CASE ik.keyno WHEN 5 THEN c.name END)
FROM sysindexkeys ik
JOIN syscolumns c ON ik.id = c.id
AND ik.colid = c.colid
GROUP BY ik.id, ik.indid) AS ik ON i.id = ik.id
AND i.indid = ik.indid
WHERE Indexproperty(i.id, i.name, 'IsHypothetical') = 0
AND Indexproperty(i.id, i.name, 'IsStatistics') = 0
AND Indexproperty(i.id, i.name, 'IsAutoStatistics') = 0
ORDER BY o.name, i.indid

> Also, for future reference, is there a way to include the primary key
> on an import?

There is. But I don't know which tool you used, which version of SQL Server
you have etc. Personally, I prefer to build databases from scripts. When
I need to copy a database, I prefer to use BACKUP/RESTORE.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]