WITH

column_names -- listing of all the columns used in the primary key

(

SELECT

kc.object_id

, kc.parent_object_id

, PrimaryKeyName = QUOTENAME(OBJECT_NAME(kc.object_id))

, SchemaName = QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))

, TableName = QUOTENAME(OBJECT_NAME(kc.parent_object_id))

, ColumnName = COL_NAME(ob.object_id, ic.column_id)

, ic.key_ordinal

, key_number = CAST(kc.parent_object_id AS VARCHAR) + CAST(ic.key_ordinal AS VARCHAR)

, parent_number = CAST(kc.parent_object_id AS VARCHAR) + CAST(ic.key_ordinal - 1 AS VARCHAR)

, descending_key = CASE ic.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END

, clustered_desc = CASE INDEXPROPERTY(kc.parent_object_id, OBJECT_NAME(kc.object_id),'IsClustered') WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END

FROM

sys.key_constraints kc

INNER JOIN sys.objects ob on kc.parent_object_id = ob.object_id

INNER JOIN sys.indexes AS I ON KC.unique_index_id = I.index_id AND KC.parent_object_id = I.object_id

INNER JOIN sys.index_columns ic ON I.object_id = IC.object_id AND I.index_id = IC.index_id

WHERE

kc.type = 'PK'

AND ob.type = 'U'

AND ob.name not in ('dtproperties','sysdiagrams') -- not true user tables

--AND COL_NAME(ob.object_id, ic.column_id) = N'$(targetColumn)'

)

,

max_key_ordinal --get the max ordinal number for complete list of columns

(

SELECT

object_id

, parent_object_id

, max_ordinal_value = MAX(key_ordinal)

FROM column_names

GROUP BY

object_id

, parent_object_id

)

,

primary_key_columns(object_id, parent_object_id, PrimaryKeyName, ColumnNames, SchemaName, TableName, key_ordinal, key_number, clustered_desc) -- recursive query to get all the columns in a comma separated list

(

SELECT pt.object_id, pt.parent_object_id, pt.PrimaryKeyName, ColumnNames = CAST(pt.ColumnName + ' ' + pt.descending_key AS VARCHAR(MAX)), pt.SchemaName, pt.TableName, pt.key_ordinal, pt.key_number, pt.clustered_desc

FROM column_names pt

INNER JOIN column_names ch ON pt.key_number = ch.key_number

WHERE pt.key_ordinal = 1 -- parent record

UNION ALL

SELECT pt.object_id, pt.parent_object_id, pt.PrimaryKeyName, CAST(ch.ColumnNames + ', ' + pt.ColumnName + ' ' + pt.descending_key AS VARCHAR(MAX)), pt.SchemaName, pt.TableName, pt.key_ordinal, pt.key_number, pt.clustered_desc

FROM column_names pt

INNER JOIN primary_key_columns ch ON pt.parent_number = ch.key_number --recursive part

WHERE pt.key_ordinal != 1 -- child record

)

SELECT

pk.PrimaryKeyName

, pk.SchemaName

, pk.TableName

, 'create_primary_key' = 'ALTER TABLE ' + pk.SchemaName + '.' + pk.TableName + ' ADD CONSTRAINT ' + pk.PrimaryKeyName + ' PRIMARY KEY ' + clustered_desc + ' (' + pk.ColumnNames + ') '

, 'drop_primary_key' = 'ALTER TABLE ' + pk.SchemaName + '.' + pk.TableName + ' DROP CONSTRAINT ' + pk.PrimaryKeyName

FROM

primary_key_columns pk

INNER JOIN max_key_ordinal mv ON mv.object_id = pk.object_id AND mv.parent_object_id = pk.parent_object_id AND mv.max_ordinal_value = pk.key_ordinal