0

I'm trying to construct the ALTER TABLE statements for multiple tables for adding PRIMARY KEY constraints. For example:

ALTER TABLE dbo.Employee_Info ADD PRIMARY KEY (EmployeeID, Employee_Name);

I'm able to get the list of PK columns for a given table using INFORMATION_SCHEMA objects:

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = 'Employee_Info'

Output:

Column_Name
EmployeeID
Employee_Name

But I need to concatenate the resultant output column lists into a single line in order for me to use it in my ALTER statement's ADD PRIMARY KEY clause as shown at the top.

I tried to use COALESCE as shown below:

DECLARE @pkcols varchar(100)
BEGIN
SET @pkcols = (SELECT @pkcols = COALESCE(@pkcols + ', ', '') + Column_Name 
FROM (
SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = 'Employee_Info' ))
END

but it keeps throwing an error: Incorrect syntax near '@pkcols'.

I'm not sure how to solve this issue and get the list of columns in a single line so that I can use it in my automation.

Can someone please help?

LearneR
  • 2,351
  • 3
  • 26
  • 50
  • 3
    `STRING_AGG` is your friend. – Jeroen Mostert Jun 29 '22 at 10:54
  • 1
    FYI, the syntax you were attempting, with a self referencing recursive variable to aggregate, is a [documented antipattern](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/select-local-variable-transact-sql?view=sql-server-ver15#c-antipattern-use-of-recursive-variable-assignment) and should be avoided. Use string aggregation to achieve the results you need, as shown in the linked duplicate. – Thom A Jun 29 '22 at 10:59
  • Wow! `STRNIG_AGG` freaking rocks! Just had to change my `SELECT` clause to `SELECT STRING_AGG(Col.Column_Name, ', ')` and it works like magic. Thank you guys! – LearneR Jun 29 '22 at 11:07

0 Answers0