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://qa.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
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
