Technical Article

Stored Proc to List Columns for a Primary Key

,

Stored procedure to return the list of columns of the primary key for a table. The name of the column for the result set is the name of the primary key for the table.

IF  EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[SP__PRIMARYKEY$GETCOLUMNS]') AND OBJECTPROPERTY(ID,N'ISPROCEDURE') = 1)
DROP PROCEDURE [DBO].[SP__PRIMARYKEY$GETCOLUMNS]
GO

CREATE PROCEDURE SP__PrimaryKeys$GetColumns
(@TABLENAME SYSNAME)
-- -------------------------------------------------------------
-- DESCRIPTION: RETURNS A RESULT SET OF COLUMN NAMES WITH 
--SYSNAME DATATYPE; TESTED ON 2000 AND 2005;
--THE RESULT NAME IS THE NAME OF THE PRIMARY KEY
-- 
-- INPUT: @TABLENAME: A VALID TABLE IN THE DATABASE
--
-- CHANGE LOG
-- AUTHORVERSIONDATEDESCRIPTION
-- CFHAWKINS1.09/7/6ORIGINAL
--
-- -------------------------------------------------------------
AS
SET NOCOUNT ON

IF   NOT EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(@TABLENAME) 
AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
BEGIN
SELECT 'NOT A TABLE IN THIS DATABASE'
RETURN -1
END

DECLARE @CMD VARCHAR(8000);

CREATE TABLE #KEYCOLUMNS (COLUMN_NAME SYSNAME);
CREATE TABLE #TEMPTABLEKEYS
(CTYPE TINYINT NOT NULL, CNAME NVARCHAR(258) COLLATE DATABASE_DEFAULT NOT NULL,
CFLAGS  INT  NULL, CCOLCOUNT INT  NULL,  CFILLFACTOR TINYINT NULL,CREFTABLE NVARCHAR(520) COLLATE DATABASE_DEFAULT NULL,
CREFKEY NVARCHAR(260)  COLLATE DATABASE_DEFAULT NULL,
CKEYCOL1 VARCHAR(132),CKEYCOL2 VARCHAR(132),CKEYCOL3 VARCHAR(132), CKEYCOL4 VARCHAR(132), 
CKEYCOL5 VARCHAR(132), CKEYCOL6 VARCHAR(132), CKEYCOL7 VARCHAR(132), CKEYCOL8 VARCHAR(132), 
CKEYCOL9 VARCHAR(132), CKEYCOL10 VARCHAR(132), CKEYCOL11 VARCHAR(132), CKEYCOL12 VARCHAR(132), 
CKEYCOL13 VARCHAR(132), CKEYCOL14 VARCHAR(132), CKEYCOL15 VARCHAR(132), CKEYCOL16 VARCHAR(132), 
CREFCOL1 VARCHAR(132), CREFCOL2 VARCHAR(132), CREFCOL3 VARCHAR(132), CREFCOL4 VARCHAR(132), 
CREFCOL5 VARCHAR(132), CREFCOL6 VARCHAR(132), CREFCOL7 VARCHAR(132), CREFCOL8 VARCHAR(132), 
CREFCOL9 VARCHAR(132), CREFCOL10 VARCHAR(132), CREFCOL11 VARCHAR(132), CREFCOL12 VARCHAR(132), 
CREFCOL13 VARCHAR(132), CREFCOL14 VARCHAR(132), CREFCOL15 VARCHAR(132), CREFCOL16 VARCHAR(132), 
CINDEXID INT  NULL, CGROUPNAME SYSNAME COLLATE DATABASE_DEFAULT  NULL, CDISABLED INT  NULL,  
CPRIMARYFG INT  NULL,  CDELETECASCADE INT  NULL, CUPDATECASCADE INT NULL, DESCENDING INT);

INSERT #TEMPTABLEKEYS
EXEC SP_MSTABLEKEYS @TABLENAME, NULL, 2

INSERT #KEYCOLUMNS
SELECT CKEYCOL1 FROM #TEMPTABLEKEYS WHERE  CKEYCOL1 IS NOT NULL
UNION
SELECT CKEYCOL2 FROM #TEMPTABLEKEYS WHERE  CKEYCOL2 IS NOT NULL
UNION
SELECT CKEYCOL3 FROM #TEMPTABLEKEYS WHERE  CKEYCOL3 IS NOT NULL
UNION
SELECT CKEYCOL4 FROM #TEMPTABLEKEYS WHERE  CKEYCOL4 IS NOT NULL
UNION
SELECT CKEYCOL5 FROM #TEMPTABLEKEYS WHERE  CKEYCOL5 IS NOT NULL
UNION
SELECT CKEYCOL6 FROM #TEMPTABLEKEYS WHERE  CKEYCOL6 IS NOT NULL
UNION
SELECT CKEYCOL7 FROM #TEMPTABLEKEYS WHERE  CKEYCOL7 IS NOT NULL
UNION
SELECT CKEYCOL8 FROM #TEMPTABLEKEYS WHERE  CKEYCOL8 IS NOT NULL
UNION
SELECT CKEYCOL9 FROM #TEMPTABLEKEYS WHERE  CKEYCOL9 IS NOT NULL
UNION
SELECT CKEYCOL10 FROM #TEMPTABLEKEYS WHERE  CKEYCOL10 IS NOT NULL
UNION
SELECT CKEYCOL11 FROM #TEMPTABLEKEYS WHERE  CKEYCOL11 IS NOT NULL
UNION
SELECT CKEYCOL12 FROM #TEMPTABLEKEYS WHERE  CKEYCOL12 IS NOT NULL
UNION
SELECT CKEYCOL13 FROM #TEMPTABLEKEYS WHERE  CKEYCOL13 IS NOT NULL
UNION
SELECT CKEYCOL14 FROM #TEMPTABLEKEYS WHERE  CKEYCOL14 IS NOT NULL
UNION
SELECT CKEYCOL15 FROM #TEMPTABLEKEYS WHERE  CKEYCOL15 IS NOT NULL
UNION
SELECT CKEYCOL16 FROM #TEMPTABLEKEYS WHERE  CKEYCOL16 IS NOT NULL
;

SELECT @CMD = 'SELECT COLUMN_NAME AS ['+CNAME+'] FROM #KEYCOLUMNS'
FROM #TEMPTABLEKEYS;

EXEC (@CMD);

DROP TABLE #KEYCOLUMNS;
DROP TABLE #TEMPTABLEKEYS;
GO

Rate

3 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (1)

You rated this post out of 5. Change rating