Replication PreScreen for proper Primary Keys

--=================================================================================
--//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
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================
--================================================================================================================================================================