Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding Primary Key Columns to tables for Transactional Replication


Adding Primary Key Columns to tables for Transactional Replication

Author
Message
Paul Clark-418949
Paul Clark-418949
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 228
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
   (
    SchemaName   VARCHAR(128)
   ,TableName   VARCHAR(128)
   ,PrimaryKey   BIT
   Wink;

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?
chandan_jha18
chandan_jha18
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 2132
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
Paul Clark-418949
Paul Clark-418949
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 228
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. ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search