Adding Primary Key Columns to tables for Transactional Replication

  • I have written the following 2 scripts to add Primary Key Columns to all tables in a Database that don't have them. I am aware that these would need to be used with extreme caution, and should not be used in a Production environment without fully testing the Applications that connect to the Database you wish to change in a Test Environment first.

    I wrote these so that I can offload Reporting Services to a Replica Copy of our Coda Database.

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

    USE [Database Name];

    DECLARE @TableName VARCHAR(128)

    DECLARE @SchemaName VARCHAR(128)

    DECLARE @SQL VARCHAR(1000)

    CREATE TABLE #tableList

    (

    SchemaNameVARCHAR(128)

    ,TableNameVARCHAR(128)

    ,PrimaryKeyBIT

    );

    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];

    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;

    Does anyone have any thoughts on the above scripts? Or maybe a better way of doing this?

  • I have not gone through the script entirely but the part where you are generating an SQL command and then executing it.

    If I were you, I would have simply generated the T-SQL command and then execute that in small batches rather than as a whole.

    Also, creating the new column is not that difficult but finding what it can break is. Do a search twice or even thrice to make sure it does not break anything.

    You don't have to search only for the objects inside that server but also for some adhoc processes

    outside that server because there wont be any DDL for those processes and they may break unless you know about it. Spread out the word to all the people directly and indirectly responsible for the application, collect suggests and feedbacks, go for the execution carefully and be ready with a rollback script(just in case).

    Thanks

    Chandan

  • Hi Chandon,

    Thanks for the Feedback.

    chandan_jha18 (10/9/2012)


    I have not gone through the script entirely but the part where you are generating an SQL command and then executing it.

    If I were you, I would have simply generated the T-SQL command and then execute that in small batches rather than as a whole.

    I was sort of hoping not to have to do too much typing, there are over 300 tables to add Primary Key Columns too for the Transactional Replication.....

    I could break this down into batches of tables however maybe by adding a where clause to the select statement that inserts into the temp table....

    Also, creating the new column is not that difficult but finding what it can break is. Do a search twice or even thrice to make sure it does not break anything.

    You don't have to search only for the objects inside that server but also for some adhoc processes

    outside that server because there wont be any DDL for those processes and they may break unless you know about it. Spread out the word to all the people directly and indirectly responsible for the application, collect suggests and feedbacks, go for the execution carefully and be ready with a rollback script(just in case).

    Thanks

    Chandan

    I totally agree with what you're saying about breaking any apps that attach to the DB, which is why I'll be testing this extensively on a Test System first, hence why I said that it should be used with extreme caution in a Production Environment.

    Great minds think alike I have also prepared a Roll-back script (one of the first things I did) just in case, all the testing in the world can't check for every eventuality.

    Thanks again for your feedback. 😉

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply