PK Columns for [dbo].[Order Details] are "OrderID", "ProductID"
DECLARE @current_object_schema NVARCHAR(128),
@current_object_name NVARCHAR(128),
@generated_key_column_argument NVARCHAR(MAX)
USE Northwind
SET @current_object_schema = 'dbo'
SET @current_object_name = 'Order Details'
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DECLARE @fn_keylist_ordinal INT,
@fn_keylist_key_name NVARCHAR(128),
@fn_keylist_next_ordinal INT,
@fn_keylist_input_schema NVARCHAR(128),
@fn_keylist_input_name NVARCHAR(128),
@fn_keylist_output NVARCHAR(MAX),
@fn_keylist_pk_name NVARCHAR(128),
@fn_keylist_pk_schema NVARCHAR(128)
-- --> Function KeyList- set input
SET @fn_keylist_input_schema = @current_object_schema
SET @fn_keylist_input_name = @current_object_name
-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
BEGIN --> Function KeyList common code
SET @fn_keylist_output = ''
SET @fn_keylist_ordinal =0
SET @fn_keylist_next_ordinal =0
SELECT @fn_keylist_pk_schema = CONSTRAINT_SCHEMA,
@fn_keylist_pk_name = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE ( TABLE_SCHEMA = @fn_keylist_input_schema )
AND ( TABLE_NAME = @fn_keylist_input_name )
AND ( CONSTRAINT_TYPE = 'Primary Key' )
IF ISNULL(@fn_keylist_pk_name, '') != ''
BEGIN
-- build an argument for the TableAttribute
SET @fn_keylist_output = ''
SELECT TOP 1
@fn_keylist_ordinal = [ORDINAL_POSITION]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE CONSTRAINT_SCHEMA = @fn_keylist_pk_schema
AND CONSTRAINT_NAME = @fn_keylist_pk_name
ORDER BY [ORDINAL_POSITION]
WHILE 1 = 1
BEGIN
-- add the column to the key array
SELECT @fn_keylist_key_name = [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE CONSTRAINT_SCHEMA = @fn_keylist_pk_schema
AND CONSTRAINT_NAME = @fn_keylist_pk_name
AND [ORDINAL_POSITION] = @fn_keylist_ordinal
SET @fn_keylist_output = @fn_keylist_output + '"' + @fn_keylist_key_name + '"'
-- get the next part
SELECT TOP 1
@fn_keylist_next_ordinal = [ORDINAL_POSITION]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE CONSTRAINT_SCHEMA = @fn_keylist_pk_schema
AND CONSTRAINT_NAME = @fn_keylist_pk_name
AND [ORDINAL_POSITION] > @fn_keylist_ordinal
ORDER BY [ORDINAL_POSITION]
IF ISNULL(@fn_keylist_next_ordinal, 0) = 0
OR @fn_keylist_ordinal = @fn_keylist_next_ordinal
BREAK
-- add seperator
SET @fn_keylist_output = @fn_keylist_output
+ ', '
SET @fn_keylist_ordinal = @fn_keylist_next_ordinal
END
-- close argument array
END
END --> Function KeyList - copy/paste this block as-is and just set/get input/output
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
-- <-- Function KeyList - get output
SET @generated_key_column_argument = @fn_keylist_output
-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
PRINT 'PK Columns for [' + @current_object_schema + '].[' + @current_object_name + '] are ' + @generated_key_column_argument
Technorati tags: SQL