Create Primary Key on TableNameId Columns

CreatePrimaryKeyOnTableNameIdColumns.sql

SELECT T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.IS_NULLABLE
INTO #Tables
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN (
    SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = TABLE_NAME + 'Id'
) C ON C.TABLE_NAME = T.TABLE_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
    AND T.TABLE_NAME NOT IN (
        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
    )

DECLARE @SqlScripts NVARCHAR(MAX)
SET @SqlScripts = ''

IF EXISTS ( SELECT * FROM #Tables WHERE IS_NULLABLE = 'YES' )
BEGIN
    SELECT @SqlScripts = @SqlScripts + 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE + ' NOT NULL

'
    FROM #Tables
    WHERE IS_NULLABLE = 'YES'
END
ELSE
BEGIN
    SELECT @SqlScripts = @SqlScripts + 'ALTER TABLE ' + TABLE_NAME + ' ADD CONSTRAINT PK_' + TABLE_NAME 
        + ' PRIMARY KEY CLUSTERED ( ' + COLUMN_NAME + ' )

'
    FROM #Tables
END

-- SELECT * FROM #Tables
SELECT @SqlScripts

DROP TABLE #Tables