Replication PreScreen for proper Primary Keys

,

THIS TABLE WILL HODL YOUR TABLENAME AND UP TO THREE KEY COLUMNS FOR CREATING YOUR PRIMARY KEYS
THE OTHER COLUMNS ARE USED IN PROCESSING THE KEY'S AND GENERATING THE ALTER TABLE ASTATEMENTS TO
CREATE THE PRIMARY KEYS FOR YOUR DB. THIS TOOL IS HANDY FOR PREPPING FOR A REPLICATION STRATEGY
TO HELP YOU QUICKLY INDENTIFY IF YOU HAVE ANY STRAGGLING TABLES OUT THERE AND TO QUICKLY GENERATE
THE ALTER STATEMENTS TO BRING THEM IN LINE WITH THE REST OF YOUR MODEL. ALL THAT IS NEEDED IS TO
INSERT THE TABLENAMES AND YOUR CHOICES FOR KEY COLUMNS INTO THE TABLE PRIMARYKEYFIX.
THE CODE BELOW INCLUDES THE FOLLOWING:

1. TABLE CREATION SCRIPT
2. SCRIPT TO FIND ALL TABLE OBJECTS IN YOUR DB THAT ARE MISSING PRIMARY KEYS
3. AN EXECUTE STATEMENT (FROM ANOTHER AUTHOR'S PROCEDURE THAT I WAS ABLE TO USE) hIS WORK IS POSTED ON SQLSERVERCENTRAL.COM
AND THERE IS A CITATION AND LINK IN MY CODE THANKING HIM FOR HIS WORK
4. THE SCRIPT TO GENERATE ALL YOUR ALTER STATEMENTS. YOU MAY RECIEVE SOME WARNINGS BASED ON THE SIZE OF YOUR
DATA AND SOME KEY SIZES. wE ARE WORKING THOSE OUT OF OUR MODEL. i LEFT THEM ALONE BECAUES I WANTED YOU TO BE ABLE TO SEE IF YOU RAN INTO ANY PROBLEMS.

REPLICATION CAN BE TOUGH ENOUGH WITHOUGH DEALING WITH PK AND MODEL ISSUES. I HOPE THIS SCRIPT CAN HELP EAS YOUR MIGRATION.

THIS SCRIPT WAS DEVELOPED ENTIRELY IN SQL SERVER 2005, BUT HAS BEEN TESTED AND PROVEN TO WORK ON BOTH PLATFORMS. ENJOY !!!

Adam Jorgensen
adamjorgensen@hotmail.com
8/1/2005

--=================================================================================
--//CREATE TABLE OF TABLE OBJECTS THAT ARE MISSING PRIMARY KEYS
/*
THIS TABLE WILL HODL YOUR TABLENAME AND UP TO THREE KEY COLUMNS FOR CREATING YOUR PRIMARY KEYS
THE OTHER COLUMNS ARE USED IN PROCESSING THE KEY'S AND GENERATING THE ALTER TABLE ASTATEMENTS TO
CREATE THE PRIMARY KEYS FOR YOUR DB. THIS TOOL IS HANDY FOR PREPPING FOR A REPLICATION STRATEGY 
TO HELP YOU QUICKLY INDENTIFY IF YOU HAVE ANY STRAGGLING TABLES OUT THERE AND TO QUICKLY GENERATE 
THE ALTER STATEMENTS TO BRING THEM IN LINE WITH THE REST OF YOUR MODEL.  THE CODE BELOW INCLUDES
THE FOLLOWING:

1. TABLE CREATION SCRIPT
2. SCRIPT TO FIND ALL TABLE OBJECTS IN YOUR DB THAT ARE MISSING PRIMARY KEYS
3. AN EXECUTE STATEMENT (FROM ANOTHER AUTHOR'S PROCEDURE THAT I WAS ABLE TO USE) hIS WORK IS POSTED ON SQLSERVERCENTRAL.COM 
	AND THERE IS A CITATION AND LINK IN MY CODE THANKING HIM FOR HIS WORK
4. THE SCRIPT TO GENERATE ALL YOUR ALTER STATEMENTS. YOU MAY RECIEVE SOME WARNINGS BASED ON THE SIZE OF YOUR 
	DATA AND SOME KEY SIZES. wE ARE WORKING THOSE OUT OF OUR MODEL. i LEFT THEM ALONE BECAUES I WANTED YOU TO BE ABLE TO SEE IF YOU RAN INTO ANY PROBLEMS.

REPLICATION CAN BE TOUGH ENOUGH WITHOUGH DEALING WITH PK AND MODEL ISSUES. I HOPE THIS SCRIPT CAN HELP EAS YOUR MIGRATION. 

THIS SCRIPT WAS DEVELOPED ENTIRELY IN SQL SERVER 2005, BUT HAS BEEN TESTED AND PROVEN TO WORK ON BOTH PLATFORMS. ENJOY !!!

Adam Jorgensen
adamjorgensen@hotmail.com	
8/1/2005

*/
--USE [dbname]
GO
/****** Object:  Table [dbo].[PRIMARYKEYFIX]    Script Date: 07/29/2005 10:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PRIMARYKEYFIX](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[TableName] [sysname] NOT NULL,
	[Key1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Count] [int] NULL,
	[Key2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Key3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Processed] [int] NOT NULL CONSTRAINT [DF_PRIMARYKEYFIX_Processed]  DEFAULT ((0)),
	[BADDATA] [int] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
-- =============================================
--CODE TO LOCATE TABLES WITHOUGH PRIMARY KEYS
--==================================================

SELECT name, *  From dbo.sysobjects (NOLOCK) Where xtype = 'u' and OBJECTPROPERTY(id, 'TableHasPrimaryKey') = 0

--=================================================================================
--//EXECUTE STOREDPROCEDURE TO FIND DUPLICATE ROWS IN YOUR POTENTIAL KEY COLUMN
EXEC sp_Find_Duplication 'TABLENAME','COLUMNNAME' -- THIS STORED PROCEDURE WAS ANOTHER SUBMISSION OF Francisco Macedo - http://www.sqlservercentral.com/scripts/contributions/403.asp
--=================================================================================
--//UPDATE TO ENTER KEY INFORMATION IN THE TABLE TO BUILD THE SCRIPT
Update PrimaryKEyFix
Set key1 = 'COLUMN1'
 key2 = 'COLUMN2',
 Key3 = 'COLUMN3'
where tablename = 'TABLENAME'
--=================================================================================

-- =============================================
--SCRIPT GENERATOR FOR THE ALTER TABLE SCRIPTS
--==============================================
Begin Transaction  /*Create temporary table using SELECT INTO statement*/

DECLARE @I as int,
	@Row_Count int,
	@TableNAme Varchar(50),
	@Key1 varchar(50),
	@Key2 varchar(50),
	@key3 varchar(15),
	@pkname Varchar(50),
	@Counter INT,
	@SQL VARCHAR(8000),
	@SQL2 VARCHAR(8000),
	@Type varchar (20),
	@length varchar (7),
	@type21 varchar (20),
	@type22 varchar (20),
	@type31 varchar (20),
	@type32 varchar (20),
	@type33 varchar (20)


SET NOCOUNT ON

SELECT 	
	[ID],tableName, Key1, Key2, Key3, 0 as Processed
	INTO tmp 
		 FROM PrimaryKeyFix
			Where TableNAme IS NOT NULL 
				AND Key1 is not Null

Begin
SELECT @I = MIN(ID) From tmp Where Processed = 0

	SET @Row_Count = (SELECT COUNT(*) FROM tmp Where Processed = 0)
	SET @counter = 0
		WHILE @Counter <= @Row_Count 
				BEGIN
				SELECT @TableName = tableName, @key1 = key1, @key2 = key2, @key3= key3 FROM tmp WHERE [ID] = @I
					
--CHECK FOR SINGLE KEY VALUE
								IF (@Tablename IS NOT NULL AND @KEY1 IS NOT NULL AND @KEY2 IS NULL and @KEY3 IS NULL)
									BEGIN
									
											select @Type = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY1 
														and Object_id(@tableName) = col.[id]
											
														SELECT @PkName = @tableNAme+'_'+@Key1+'_U_C_IDX'
													
													SELECT @SQL2 =  'IF EXISTS (SELECT * FROM sysobjects WHERE [name] = '''+@PKNAME+''')'+CHAR(13)+CHAR(10)
													+'BEGIN ALTER TABLE '+@tableName+' DROP CONSTRAINT '+@PKNAME+' END '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
													
													PRINT @SQL2

													SELECT @SQL = 
														CASE 
															WHEN (@type = 'int' OR @type = 'smallint' OR @type = 'sysname')  THEN  'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type+' NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															ELSE 'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type+'('+@length+') NOT NULL '+CHAR(13)+CHAR(10)+'GO'
														END
													PRINT @SQL

											
				

											/*Print Statements*/
											SELECT @SQL ='BEGIN ALTER TABLE '+@tableName+' ADD CONSTRAINT '+@PKNAME+' PRIMARY KEY  ('+@Key1+') END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
											PRint @SQL

											UPDATE tmp SET PROCESSED = 1 WHERE [ID] = @I

											SELECT @I = MIN(ID) From tmp Where Processed = 0
											SET @COUNTER = @Counter + 1	
							
									END
--================================================================================================================================================================
								IF (@Tablename IS NOT NULL AND @KEY1 IS NOT NULL AND @KEY2 IS NOT NULL and @KEY3 IS NULL)
									BEGIN
											

											select @Type21 = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY1 
														and Object_id(@tableName) = col.[id]

											select @Type22 = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY2 
														and Object_id(@tableName) = col.[id]

											SELECT @PkName = @tableNAme+'_'+@Key1+'_'+@KEY2+'_U_C_IDX'

											SELECT @SQL = 'IF EXISTS (SELECT * FROM sysobjects WHERE [name] = '''+@PKNAME+''')'+CHAR(13)+CHAR(10)
													+'BEGIN ALTER TABLE '+@tableName+' DROP CONSTRAINT '+@PKNAME+' END '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

											Print @SQL

											SELECT @SQL = 
														CASE 
															WHEN ((@type21 = 'int' OR @type21 = 'sysname' OR @type21 = 'smallint')  AND (@type22 = 'int' OR @type22 = 'sysname' OR @type22 = 'smallint')) THEN  'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type21+' NOT NULL '+ 
																											  +'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type22+'  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN ((@type21 = 'int' OR @type21 = 'sysname' OR @type21 = 'smallint') AND @type22 <> 'int') THEN  'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type21+' NOT NULL '
																											   +'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type22+'('+@length+')  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type21 <> 'int' AND (@type22 = 'int' OR @type22 = 'sysname' OR @type22 = 'smallint')) THEN  'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type21+'('+@length+') NOT NULL '
																											   +'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type22+'  NOT NULL '	+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type21 <> 'int' AND @type22 <> 'int') THEN  'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type21+'('+@length+') NOT NULL '
																												+'ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type22+'('+@length+') NOT NULL '	+CHAR(13)+CHAR(10)+'GO'
														END
													PRINT @SQL

											
				

											/*Print Statements*/
											SELECT @SQL =  'BEGIN ALTER TABLE '+@tableName+' ADD CONSTRAINT '+@PKNAME+' PRIMARY KEY  ('+@Key1+','+@key2+') END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)

											PRint @SQL

											UPDATE tmp SET PROCESSED = 1 WHERE [ID] = @I

											SELECT @I = MIN(ID) From tmp Where Processed = 0
											SET @COUNTER = @Counter + 1	
									END
--================================================================================================================================================================

								IF (@Tablename IS NOT NULL AND @KEY1 IS NOT NULL AND @KEY2 IS NOT NULL and @KEY3 IS NOT NULL)
									BEGIN
											
				


											select @Type31 = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY1 
														and Object_id(@tableName) = col.[id]

											select @Type32 = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY2 
														and Object_id(@tableName) = col.[id]

											select @Type33 = typ.name, @length = col.length 
												From syscolumns col 
													INNER JOIN SYSTYPES typ ON col.xtype = typ.xtype
														WHERE col.NAme = @KEY3
														and Object_id(@tableName) = col.[id]

											SELECT @PkName = @tableNAme+'_'+@Key1+'_'+@KEY2+'_'+@KEY3+'_U_C_IDX'

											SELECT @SQL = 'IF EXISTS (SELECT * FROM sysobjects WHERE [name] = '''+@PKNAME+''')'+CHAR(13)+CHAR(10)
													+'BEGIN ALTER TABLE '+@tableName+' DROP CONSTRAINT '+@PKNAME+' END '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)
											Print @SQL


											SELECT @SQL = 
														CASE 

															WHEN ((@type31 = 'int' OR @type31 = 'sysname' OR @type31 = 'smallint') AND (@type32 = 'int' OR @type32 = 'sysname' OR @type32 = 'smallint') AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint')) 
																															THEN  'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY1+' '+@type31+' NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY2+' '+@type32+' NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY3+' '+@type33+' NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type31 <> 'int' AND @type32 <> 'int' AND @TYPE33 <> 'int') 
																															THEN  ' ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type31+'('+@length+')  NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type32+'('+@length+')  NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY3+' '+@type33+'('+@length+')  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN ((@type31 = 'int' OR @type31 = 'sysname' OR @type31 = 'smallint') AND @type32 <> 'int' AND @TYPE33 <> 'int') 
																															THEN    'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY1+' '+@type31+' NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type32+'('+@length+')  NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY3+' '+@type33+'('+@length+')  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type31 <> 'int' AND (@type32 = 'int' OR @type32 = 'sysname' OR @type32 = 'smallint') AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint') AND @TYPE33 <> 'int') 
																															THEN  ' ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type31+'('+@length+')  NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY2+' '+@type32+' NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY3+' '+@type33+'('+@length+')  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type31 <> 'int' AND @type32 <> 'int' AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint')) 
																															THEN  ' ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type31+'('+@length+')  NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type32+'('+@length+')  NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY3+' '+@type33+' NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN (@type31 <> 'int' AND(@type32 = 'int' OR @type32 = 'sysname' OR @type32 = 'smallint') AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint') AND @TYPE33 = 'int') 
																															THEN  ' ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY1+' '+@type31+'('+@length+')  NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY2+' '+@type32+' NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY3+' '+@type33+' NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN ((@type31 = 'int' OR @type31 = 'sysname' OR @type31 = 'smallint') AND @type32 <> 'int' AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint')) 
																															THEN   'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY1+' '+@type31+' NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY2+' '+@type32+'('+@length+')  NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY3+' '+@type33+' NOT NULL '+CHAR(13)+CHAR(10)+'GO'
															WHEN ((@type31 = 'int' OR @type31 = 'sysname' OR @type31 = 'smallint') AND (@type32 = 'int' OR @type32 = 'sysname' OR @type32 = 'smallint') AND (@type33 = 'int' OR @type33 = 'sysname' OR @type33 = 'smallint') AND @TYPE33 <> 'int') 
																															THEN   'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY1+' '+@type31+' NOT NULL '
																															+'ALTER TABLE '+@tableName+' ALTER COLUMN '+@KEY2+' '+@type32+' NOT NULL '
																															+'  ALTER TABLE '+@tableNAme+' ALTER COLUMN '+@KEY3+' '+@type33+'('+@length+')  NOT NULL '+CHAR(13)+CHAR(10)+'GO'
														END
													PRINT @SQL

											/*Print Statements*/
											SELECT @SQL =  'BEGIN ALTER TABLE '+@tableName+' ADD CONSTRAINT '+@PKNAME+' PRIMARY KEY  ('+@Key1+','+@key2+','+@key3+') END'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+'GO'

											PRint @SQL

											UPDATE tmp SET PROCESSED = 1 WHERE [ID] = @I

											SELECT @I = MIN(ID) From tmp Where Processed = 0
											SET @COUNTER = @Counter + 1	
									END
--================================================================================================================================================================

END
			DROP TABLE tmp
--================================================================================================================================================================

END
COMMIT TRANSACTION
GO
SET NOCOUNT OFF
GO
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================

Rate

Share

Share

Rate