Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding Primary Key Columns to tables for Transactional Replication Expand / Collapse
Author
Message
Posted Wednesday, October 3, 2012 6:07 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:04 AM
Points: 28, Visits: 176
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
);

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?
Post #1367596
Posted Tuesday, October 9, 2012 1:02 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:31 PM
Points: 466, Visits: 1,914
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
Post #1370183
Posted Tuesday, October 9, 2012 1:18 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 18, 2014 5:04 AM
Points: 28, Visits: 176
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.


Post #1370188
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse