Get Primary Key Columns (GPKC)
A stored procedure that builds a formatted list of a table's primary key columns with configurable format tokens and delimiters.
GPKC (Get Primary Key Columns) builds a formatted list of the primary key columns on a table - handy for generating INSERT/UPDATE statements, column lists for replication, or just inspecting a schema. The @Format parameter supports the tokens Example: EXEC dbo.GPKC @Table = 'Customers' -- prints [CustomerID] and
CREATE PROCEDURE [dbo].[GPKC] --Get Primary Key Columns
(
@Table nVarChar(4000),
@Schema nVarChar(4000) = 'dbo',
@Format nVarChar(1000) = '<column>',
@Delimiter nVarChar(1000) = ',<crlf>',
@PrintOutput Bit = 1,
@ExcludeIdentity Bit = 0,
@Exclude nVarChar(1000) = NULL, --Like "%[_]%"
@Output nVarchar(4000) = NULL OUTPUT
) AS
BEGIN
SET NOCOUNT ON;
SET @Delimiter = Replace(@Delimiter, '<crlf>', + Char(13) + Char(10))
SET @Delimiter = Replace(@Delimiter, '<cr>', + Char(13))
SET @Delimiter = Replace(@Delimiter, '<lf>', + Char(10))
SET @Delimiter = Replace(@Delimiter, '<tab>', + Char(9))
DECLARE @ColumnList nVarChar(4000)
DECLARE @ColumnName nVarChar(1024)
DECLARE tableColumns CURSOR FAST_FORWARD FOR
SELECT
Replace(Replace(Replace(@Format, '<column>', C.name), '<table>', O.name), '<database>', db_name()) AS Name
FROM
sys.columns AS C
INNER JOIN sys.objects AS O
ON O.object_id = C.object_id
INNER JOIN sys.schemas AS S
ON S.schema_id = O.schema_id
INNER JOIN sys.indexes AS I
on I.object_id = O.object_id
INNER JOIN sys.index_columns as IC
ON IC.object_id = O.object_id
AND IC.index_id = I.index_id
AND IC.column_id = C.column_id
WHERE
I.is_primary_key = 1
AND O.name = @Table
AND S.name = @Schema
AND (C.is_identity = 0 OR @ExcludeIdentity = 0)
AND (C.name NOT LIKE @Exclude OR @Exclude IS NULL)
ORDER BY
C.column_id
OPEN tableColumns
FETCH FROM tableColumns INTO @ColumnName
WHILE(@@FETCH_STATUS = 0)
BEGIN--BEGIN
----------------------------------------------------------------------------------
SET @ColumnName = Replace(@ColumnName, '<crlf>', + Char(13) + Char(10))
SET @ColumnName = Replace(@ColumnName, '<cr>', + Char(13))
SET @ColumnName = Replace(@ColumnName, '<lf>', + Char(10))
SET @ColumnName = Replace(@ColumnName, '<tab>', + Char(9))
SET @ColumnList = IsNull(@ColumnList, '') + @ColumnName
----------------------------------------------------------------------------------
FETCH FROM tableColumns INTO @ColumnName
IF(@@FETCH_STATUS = 0)
BEGIN--IF
SET @ColumnList = IsNull(@ColumnList, '') + @Delimiter
END--IF
END--WHILE
CLOSE tableColumns DEALLOCATE tableColumns
IF @PrintOutput = 1
BEGIN--IF
PRINT @ColumnList
END--IF
SET @Output = @ColumnList --Push the output back to the caller.
END