Presents your 
SQL SERVER E-NEWSLETTER for March 13, 2003

<------------------------------------------->

DETERMINE IF A TABLE HAS A PRIMARY KEY WITH SP_KEYS

When you need to find out if a table has a primary key, you can use the
system stored procedure sp_pkeys. This system procedure has three input
parameters:


* @TABLE_NAME: This is the name of the table in question, and is a
required parameter where wildcards cannot be used.

* @TABLE_OWNER: This is the table's owner (optional).

* @TABLE_QUALIFIER: This is the database name where the table resides.
(This is misleading, because you must run this procedure in the 
database where the table resides.)


The sp_keys procedure will return this information:

* TABLE_QUALIFIER
* TABLE_OWNER
* TABLE_NAME
* COLUMN_NAME
* KEY_SEQ
* PK_NAME

You can also use the sp_keys system stored procedure to find tables
without primary keys.

The following code will retrieve the primary key information from
NorthWind's Orders table:

USE NORTHWIND
GO
EXEC sp_pkeys @table_name = N'Orders', @table_owner = N'dbo',
@table_qualifier = N'Northwind'
GO
SELECT TC.CONSTRAINT_CATALOG, TC.CONSTRAINT_SCHEMA, TC.TABLE_NAME,
KCU.COLUMN_NAME, KCU.ORDINAL_POSITION, KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
   ON TC.TABLE_NAME = KCU.TABLE_NAME
   AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.TABLE_NAME = N'Orders' AND TC.CONSTRAINT_TYPE = N'PRIMARY KEY'
GO

----------------------------------------