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.
2007-04-16 (first published: 2007-03-20)
14,066 reads
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