How to list tables with Primary keys

[Danny]

Hello,

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.

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

Thanks,
Peps

[Erland Sommarskog]

Danny (dl******@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, 'IsAutoStatisti cs') = 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[ctotos]

On Jun 8, 7:59 pm, Danny <dlapi...@gmail .comwrote:

Hello,

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.

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

Thanks,
Peps

What are all the keys used in a database

http://www.sqlhacks.com/faqs/list_all_keys

USE AdventureWorksL T;
go

SELECT schm.name AS 'Schema', tbl.name AS 'Table'
, KEYS.name AS 'Constraint', KEYS.type_desc AS 'Type'
, cols.name AS 'Column'
FROM sys.key_constra ints AS KEYS
JOIN sys.TABLES AS tbl
ON tbl.object_id = KEYS.parent_obj ect_id
JOIN sys.schemas AS schm
ON schm.schema_id = tbl.schema_id
JOIN sys.index_colum ns AS idxcols
ON idxcols.object_ id = tbl.object_id
AND idxcols.index_i d = KEYS.unique_ind ex_id
JOIN sys.COLUMNS AS cols
ON cols.object_id = tbl.object_id
AND cols.column_id = idxcols.column_ id
ORDER BY 1,2,3,4;
go
AND

What are all the tables without a primary key?

http://www.sqlhacks.com/faqs/no_primary_key

USE sql911;
go

SELECT SCHEMA_NAME(sch ema_id) AS "Schema", name AS "Table"
FROM sys.TABLES
WHERE OBJECTPROPERTY( object_id,'Tabl eHasPrimaryKey' ) = 0
ORDER BY 1,2;
go

This includes samples and explanations on how to do it.

Also new this week:

SQL Server index performance
SQL Server - optimization:in dex performance
How to group items into a fixed number of bucket with MS SQL Server
How to have a simple server monitoring in MS SQL Server
What's the current version of MS SQL Server used?
What are all the triggers used in a database
What are all the views in a database in MS SQL Server?
What are all the stored procedures in a database in MS SQL Server?
What's the structure of a table with MS SQL Server?