Adding Primary Keys to Tables for Replication

Please note that the following queries should only be used in a Production Environment once the applications that connect to the Database you wish to change have been fully tested in a test environment.

These Scripts change the table structure of any tables within the Database that do not have a Primary Key Column, and do not have a Unique column that can become a Primary Key.

Script 1 - Adds a Unique Column called RowID to all Tables in a Database without a Primary Key.

USE [Database Name]

GO

DECLARE @TableName  VARCHAR(128)

DECLARE @SchemaName VARCHAR(128)

DECLARE @SQL          VARCHAR(1000)

CREATE TABLE #tableList

      (

       SchemaName VARCHAR(128)

      ,TableName  VARCHAR(128)

      ,PrimaryKey BIT

      );

INSERT INTO #tableList

      SELECT SCHEMA_NAME(SCHEMA_ID) AS SchemaName

            ,name AS TableName

            ,0 -- No Primary Key

      FROM sys.tables

      WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0

      ORDER BY SchemaName, TableName

WHILE (SELECT COUNT(*) FROM #tableList WHERE PrimaryKey = 0) > 0

      BEGIN

            SELECT TOP 1 @TableName = TableName

                  , @SchemaName = SchemaName

            FROM #tableList

            WHERE PrimaryKey = 0;

                  SET @SQL = 'ALTER TABLE [' +@SchemaName + '].[' +@TableName + '] ADD [RowID] INT IDENTITY(1,1)'

                  EXEC (@SQL)

                  IF @@ERROR<>0 PRINT @SQL

            UPDATE #tableList

            SET PrimaryKey = 1

            WHERE TableName = @TableName

            AND SchemaName = @SchemaName

      END

DROP TABLE #tableList

Script 2 - Makes the RowID Column a Primary Key Column on all tables in the Database with the RowID Column.

USE [Database Name]

GO

DECLARE @TableName  VARCHAR(128)

DECLARE @SchemaName VARCHAR(128)

DECLARE @SQL          VARCHAR(1000)

CREATE TABLE #tableList

      (

       SchemaName VARCHAR(128)

      ,TableName  VARCHAR(128)

      ,RowID      BIT

      );

INSERT INTO #tableList

      SELECT TABLE_SCHEMA AS SchemaName

      , TABLE_NAME AS TableName

      , 0

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME = 'RowID'

ORDER BY SchemaName, TableName

WHILE (SELECT COUNT(*) FROM #tableList WHERE RowID = 0) > 0

      BEGIN

            SELECT TOP 1 @TableName = TableName

                  , @SchemaName = SchemaName

            FROM #tableList

            WHERE RowID = 0;

                  SET @SQL = 'ALTER TABLE [' +@SchemaName + '].[' +@TableName + '] ADD CONSTRAINT PK_' +@SchemaName + '_' +@TableName + ' PRIMARY KEY NONCLUSTERED (RowID) ON [PRIMARY]'

                  EXEC (@SQL)

                  IF @@ERROR<>0 PRINT @SQL

            UPDATE #tableList

            SET RowID = 1

            WHERE TableName = @TableName

            AND SchemaName = @SchemaName

      END

DROP TABLE #tableList

NB: AGAIN PLEASE USE THESE SCRIPTS WITH EXTREME CAUTION IN A PRODUCTION ENVIRONMENT!!