Technical Article

Report Primary Keys

,

Quick and hassle free method of viewing the existing or non-existant primary keys in your database.

/*
Does: List all tables with and without primary keys (pk).
By: Maxwell van Zuylen
Done: 20051028
Note: All table names will be printed. Each table will be 
called using sp_pkeys. If the table has a pk the 
data for that pk will be shown, if not, no data 
will be displayed.
View results in text in Query Analyzer.
*/
DECLARE @tblName VARCHAR(255), @proc VARCHAR(265)
DECLARE curTbl CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'u'

OPEN curTbl

SET NOCOUNT ON
SELECT @proc = 'sp_pkeys '

FETCH NEXT FROM curTbl INTO @tblName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @tblName = @tblName
PRINT @tblName
EXEC @proc @tblName
FETCH NEXT FROM curTbl INTO @tblName
END
CLOSE curTbl
DEALLOCATE curTbl

SET NOCOUNT OFF

Rate

4 (1)

Share

Share

Rate

4 (1)