0

Based on the examples here, here and here, I tried to build dynamic SQL so that I don't have to manually do this at some point. However, I am facing a challenge when the PK is a combination of more than one column. This code is creating scripts separately for each additional column that is part of PK. I would like to have it something with number of columns in the suffix if it has more than one column that should be part of PK - _12, _123, etc.

ALTER TABLE <Schema_Name>.<Table_Name>
DROP CONSTRAINT <constraint_name>

ALTER TABLE <Schema_Name>.<Table_Name>
ADD CONSTRAINT PK_<Schema_Name>_<Table_Name>_12 PRIMARY KEY (<Column1>,<Column2>)

Code built so far:

SELECT 
    STUFF((
select ';' + 'ALTER TABLE ' + 
    s.name + '.' + 
    t.name + ' DROP CONSTRAINT ' + 
    i.name + ';' +   
    'ALTER TABLE ' + s.name + '.' + t.name + 
    ' ADD CONSTRAINT ' + 'PK__' + s.name + '__' + t.name + '__' + 
    --tc.name,
    string_agg(ic.key_ordinal, ',') + 
    ' PRIMARY KEY (' + tc.name + ')'
from 
    sys.schemas s 
    inner join sys.tables t   on s.schema_id=t.schema_id
    inner join sys.indexes i  on t.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
    inner join sys.columns tc on ic.object_id=tc.object_id 
                             and ic.column_id=tc.column_id
where i.is_primary_key=1 
GROUP BY s.name, t.name, i.name, tc.name, ic.key_ordinal
order by t.name, ic.key_ordinal
 FOR XML PATH('')),1,1,'') + ';'
;
Julaayi
  • 403
  • 2
  • 8
  • 23
  • Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Jun 15 '20 at 20:01
  • 1
    [Dos and Don'ts of Dynamic SQL](https://www.sqlservercentral.com/articles/dos-and-donts-of-dynamic-sql) – Eric Brandt Jun 15 '20 at 20:28

0 Answers0