Thursday, March 7, 2013

Generate DML Script by using TabileDiff Generator For Deployment Purposes

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Prerequisites:
1.Make sure you have the TableDiff utility installed as part of SQL Server Server Installation.
2.Versions supported -SQL 2005,SQL 2008,SQL 2008R2,SQL 2012 64 Bit versions.
If you have installed 32 bit then change the exe path in the code accordingly(C:\Program Files (x86)\...
3.Make sure commandshell option is enabled from the SQL Server .

Description:
1.Performs a table diff on the source and destination data and Generates the DML SQL Script which can be used for Deployment.
2.Only generates the file when it finds the difference and each table is generated in a separate file.Format(<Tablename>_<DATETIMESTAMP>_diff.sql
3.Files are generated in the SQL Server box that the script is running.
3.All the parameters except TableName is mandatory
4.Also you can view the Result in the output window and troubleshoot if there is any error.

EXAMPLE: ExeC usp_TableDiffGenerator 'DEVSERVER','QASERVER','Employee','Employee','dbo',EmployeeDetails,'E:\temp\'
*/

CREATE PROCEDURE [usp_TableDiffGenerator]
@SourceServer VARCHAR(100) , @DesinationServer VARCHAR(100),@SourceDB VARCHAR(100),@DestinationDB
VARCHAR(100),@Schema VARCHAR(50),@Table VARCHAR(50),@Filepath VARCHAR(100)
AS

BEGIN
SET
QUOTED_IDENTIFIER ON
DECLARE @TableNames as table (id int identity(1,1),tableName varchar(100))
DECLARE @file_results TABLE (file_exists int, file_is_a_directory int, parent_directory_exists int )
DECLARE @sTableDiff nvarchar(1000)
DECLARE @tableName varchar(100)
DECLARE @counter int
DECLARE @maxCount int
DECLARE @Time VARCHAR(20)
DECLARE @TableDiffexecutablePath VARCHAR(500)
DECLARE @FileName varchar(255)
DECLARE @FileName1 varchar(255)
DECLARE @Folder varchar(255)
DECLARE @File_Exists INT
DECLARE @folder_exists INT

SET @Time= CONVERT(VARCHAR(10),GETDATE(),120)+ '_'+ REPLACE(CONVERT(VARCHAR(8),GETDATE(),114),':','')

IF COALESCE(@SourceServer,'')='' OR COALESCE(@DesinationServer,'')='' OR COALESCE(@SourceDB,'')='' OR COALESCE(@DestinationDB,'')='' OR COALESCE(@Schema,'')=''
OR COALESCE(@Filepath,'')=''
BEGIN
SELECT 'Parameters are Mandatory'
RETURN
END
--Check if the folder exist
INSERT INTO @file_results (file_exists, file_is_a_directory, parent_directory_exists)
exec master.dbo.xp_fileexist @Filepath
select @folder_exists = file_is_a_directory from @file_results if @folder_exists=0
BEGIN
SELECT
'Folder Does not Exist.'
RETURN
END

--SQL2012
SELECT @FileName1='C:\Program Files\Microsoft SQL Server\110\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName1, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName1
IF @File_Exists<>1-- File not Found--SQL2008 AND SQL 2008 R2
BEGIN
SELECT @FileName='C:\Program Files\Microsoft SQL Server\100\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName
IF @File_Exists <>1 -- File not Found--SQL20005
BEGIN
SELECT @FileName='C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe'
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT
SET @TableDiffexecutablePath=@FileName
IF @File_Exists <>1 -- File not Found
BEGIN
SELECT 'TableDiff.exe Does not Exist.'
RETURN
END
END
ENDIF @Table IS NOT NULL--Specific Table
BEGIN
INSERT INTo @TableNames SELECT @Table
SET @maxCount=1
END
ELSE
BEGIN
INSERT INTo @TableNames
SELECT so.[name] as table_name
FROM sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id WHERE type = 'U' and sc.[name] =@Schema

SELECT @maxCount = COUNT(*)
FROM sys.objects so
inner join sys.schemas sc ON so.schema_id = sc.schema_id WHERE type = 'U' and sc.[name] =@Schema
END
--Initialize the Counter
 SET @counter = 1
WHILE @counter <= @maxCount
BEGIN
SELECT @tableName = tableName
FROM @TableNames
WHERE id = @counter

SET @sTableDiff= '""'+ @TableDiffexecutablePath + '" -sourceserver '+ @SourceServer +' -sourcedatabase '+ @SourceDB+ ' -sourceschema '+ @Schema + ' -sourcetable ' + @tableName + ' -destinationserver '+ @DesinationServer+ ' -destinationdatabase ' + @DestinationDB + ' -destinationschema '+ @Schema + ' -destinationtable ' + @Schema +  '.'+ @tableName + ' -f ' + @Filepath + @tableName + '_'+ @Time + '_Diff.sql"'

EXEC XP_CMDSHELL @sTableDiff
Set @counter = @counter + 1
END
END

No comments:

Post a Comment