Add Primary Key constraint automatically

I have the following table InfoSchema which contains the SchemaName and the TableName of each table in my test database :

SchemaName TableName
dbo        Employee
dbo        Department
Function   Company
Finance    Payslips
Sub        ProjectSub

I want to add for each table the constraint PrimaryKey to the column ending with ID or Id : In dbo.Employee there is one column EmployeeId so the query will be like below :

ALTER TABLE dbo.Employee
ADD CONSTRAINT Employee_pk PRIMARY KEY (EmployeeId);

For Sub.ProjectSub there are 3 columns ending with Id :

• ProjectId
• CompanyId
• SubId
  

The constraint will be added at the first column appearing in the structure of the table.

Answer

As I mention in my comment, you can use a dynamic statement to create the statements. I very strongly suggest looking over the SQL generated, however, so I do not include an EXEC sp_executesql statement here. PRINT or SELECT the value of @SQL and check it over first, then run the statements as you need:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

SET @SQL = STUFF((SELECT @CRLF + @CRLF +
                         N'ALTER TABLE ' + QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name]) + @CRLF +
                         N'ADD CONSTRAINT ' + QUOTENAME(CONCAT(t.[name],N'_PK')) + N' PRIMARY KEY (' + QUOTENAME(c.[name]) + N');'
                  FROM sys.schemas s
                       JOIN sys.tables t ON s.schema_id = t.schema_id
                       CROSS APPLY (SELECT TOP 1 *
                                    FROM sys.columns c
                                    WHERE c.object_id = t.object_id
                                      AND c.name LIKE '%id'
                                    ORDER BY c.column_id ASC) c
                 WHERE NOT EXISTS (SELECT 1
                                   FROM sys.key_constraints k
                                   WHERE k.[type] = 'PK'
                                     AND k.parent_object_id = t.object_id)
                 FOR XML PATH(N''),TYPE).value('.','nvarchar(MAX)'),1,4,N'');

PRINT @SQL;

This assumes that the first column, ordinally, needs to be the PK, and it will not attempt to create a PK on a table that already has one.