DROP PROCEDURE dbo.USP_CompareDB
GO
CREATE PROCEDURE [dbo].[USP_CompareDB]
@dbName_Main NVARCHAR(100) ,
@dbName_Sub NVARCHAR(100) ,
@table_list NVARCHAR(MAX) = NULL
AS BEGIN
-- exec USP_CompareDB 'DBName','DBName_Prod_v','Table_1,Table_2'
-- ====================================================Header=======================================================
-- Name : [USP_CompareDB]
-- Purpose : Procedure USP_CompareDB is useful for comparing two Similar databases
-- ** The first database is refered to as the Main Database and the
-- second database is refered to as the Sub Database
-- ------------------------------------------------------------------------------------------------------------------
-- Orig Auth : Vasudeva Varma Revu
-- Orig Date : Tue, May 25 2010
-- ------------------------------------------------------------------------------------------------------------------
-- Arguments :
-- @dbName_Main -- Name of the main database with reference to which the comparision should go on
-- @dbName_Sub -- Name of the sub database with respect to
-- @table_list -- List of tables if any or else NULL
-- ( if given NULL all the tables in the specified database is considered)
-- ------------------------------------------------------------------------------------------------------------------
-- Return Value
-- Results a table with the info of a table_name,column_name and
-- the change regarding that column as reason
-- ------------------------------------------------------------------------------------------------------------------
-- Initially verifying the existence of databases and then proceeding furthur
IF EXISTS
(
SELECT 1 FROM sys.databases
WHERE
Name like @dbName_Main
)
BEGIN
IF EXISTS
(
SELECT 1 FROM sys.databases
WHERE
Name like @dbName_Sub
)
BEGIN
DECLARE @mainDB_columnsInfo TABLE(Table_Name NVARCHAR(200),
Column_Name NVARCHAR(200),
Is_Nullable NVARCHAR(50),
Data_Type NVARCHAR(100),
VStatus INT)
DECLARE @subDB_columnsInfo TABLE(Table_Name NVARCHAR(200),
Column_Name NVARCHAR(200),
Is_Nullable NVARCHAR(50),
Data_Type NVARCHAR(100),
VStatus INT)
DECLARE @dbMain_constraint_list TABLE(Table_Name NVARCHAR(200),
Column_Name NVARCHAR(200),
Constraint_Name NVARCHAR(300),
Constraint_Type NVARCHAR(100))
DECLARE @dbSub_constraint_list TABLE(Table_Name NVARCHAR(200),
Column_Name NVARCHAR(200),
Constraint_Name NVARCHAR(300),
Constraint_Type NVARCHAR(100))
DECLARE @resTB TABLE(Table_Name NVARCHAR(200),
Column_Name NVARCHAR(200),Reason NVARCHAR(300))
DECLARE @tablename NVARCHAR(200),
@columnname NVARCHAR(200),
@isnullable NVARCHAR(50),
@datatype NVARCHAR(100),
@db_constraint_type1 NVARCHAR(100) ,
@db_constraint_type2 NVARCHAR(100),
@db_constraint_name NVARCHAR(500)
IF @table_list is null
BEGIN
-- Verifying the table list if table list is not given then performing the operation for all the tables
SELECT @table_list = COALESCE(
@table_list + ',''' +
CAST(Name AS VARCHAR) + '''', '''' +
CAST(Name AS VARCHAR)+ '''' )
FROM sys.tables
END
ELSE
BEGIN
SET @table_list = REPLACE(QUOTENAME(REPLACE(@table_list,',',''','''),CHAR(39)),'''''','''')
END
SET @table_list = ' and table_name in ('+@table_list +')'
-- Extracting the databases info all columns info including the nullable field and datatype
-- Main database column information
INSERT INTO @mainDB_columnsInfo
EXEC('SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, 0
FROM '+@dbName_Main+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME NOT LIKE ''SYSDIAGRAMS''
' + @table_list)
-- Sub database columns information
INSERT INTO @subDB_columnsInfo
EXEC('SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE, 0
FROM '+@dbName_Sub+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME NOT LIKE ''SYSDIAGRAMS''
' + @table_list )
-- Main database constraints and their details (on what column and for what table they are defined )
INSERT INTO @dbmain_constraint_list
EXEC(' SELECT CUSG.TABLE_NAME, CUSG.COLUMN_NAME, TCON.CONSTRAINT_NAME, TCON.CONSTRAINT_TYPE
FROM '+@dbName_Main+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCON INNER JOIN
'+@dbName_Main+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CUSG
ON TCON.CONSTRAINT_NAME = CUSG.CONSTRAINT_NAME ')
-- Sub database constraints and their details (on what column and for what table they are defined )
INSERT INTO @dbsub_constraint_list
EXEC(' SELECT CUSG.TABLE_NAME, CUSG.COLUMN_NAME, TCON.CONSTRAINT_NAME, TCON.CONSTRAINT_TYPE
FROM '+@dbName_Sub+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCON INNER JOIN
'+@dbName_Sub+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CUSG
ON TCON.CONSTRAINT_NAME = CUSG.CONSTRAINT_NAME ')
-- Traversing each and every column of an individual table from the main database
DECLARE TC CURSOR FOR SELECT Table_Name, Column_Name, Is_Nullable, Data_Type
FROM @mainDB_columnsInfo
OPEN TC
FETCH TC INTO @tablename ,@columnname ,@isnullable ,@datatype
WHILE @@FETCH_STATUS = 0
BEGIN
SET @db_constraint_type1 = NULL
SET @db_constraint_type2 = NULL
-- Verifying the existance of table and a column combination in the sub database
IF(NOT EXISTS(
SELECT * FROM @subDB_columnsInfo
WHERE TABLE_NAME = @tablename AND COLUMN_NAME = @columnname))
BEGIN
-- If the column for that table does not exists then inserting a record into the resTB with below reason
INSERT INTO @resTB
(Table_Name, Column_Name, Reason) VALUES
(@tablename, @columnname, ' COLUMN for the specified TABLE doesn''t EXISTS in SUB_DATABASE')
END
ELSE
BEGIN
-- The column for that table exists so verifying the datatype specifications
IF(NOT EXISTS(
SELECT * FROM @subDB_columnsInfo
WHERE TABLE_NAME = @tablename AND
COLUMN_NAME = @columnname AND DATA_TYPE = @datatype))
BEGIN
-- if datatype mismatches for that table-column then inserting into resTB with the below reason
INSERT INTO @resTB
(Table_Name, Column_Name, Reason ) VALUES
(@tablename, @columnname, ' DATATYPE MISMATCH for the COLUMN of this TABLE in SUB_DATABASE ')
END
-- Consequently verifying the IS_NULLABLE field for the table-column
IF(NOT EXISTS(
SELECT * FROM @subDB_columnsInfo
WHERE TABLE_NAME = @tablename AND
COLUMN_NAME = @columnname AND IS_NULLABLE = @isnullable))
BEGIN
-- If IS_NULLABLE property mis matches then inserting a record into the resTB with below reason
INSERT INTO @resTB
(Table_Name, Column_Name, Reason) VALUES
(@tablename, @columnname, 'IS_NULLABLE PROPERTY MISMATCH for the specified TABLE-COLUMN combination')
END
-- Updating the subDB columns information so that they are visited once
UPDATE @subDB_columnsInfo
SET VSTATUS = 1
WHERE TABLE_NAME = @tablename AND
COLUMN_NAME = @columnname
END
-- Listing out the constraints list of individual table and verifying them
DECLARE CC CURSOR FOR SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM @dbmain_constraint_list
WHERE COLUMN_NAME = @columnname AND
TABLE_NAME = @tablename
OPEN CC
FETCH NEXT FROM CC INTO @db_constraint_name,@db_constraint_type1
WHILE @@FETCH_STATUS = 0
BEGIN
-- Verifying the constraint for the same table and column combination
IF NOT EXISTS(
SELECT * FROM @dbSub_constraint_list
WHERE COLUMN_NAME = @columnname AND
TABLE_NAME = @tablename AND
CONSTRAINT_TYPE = @db_constraint_type1 )
-- if the specified constraint does not exists for the same table and column combination then
-- inserting into the resTB a record with the
BEGIN
INSERT INTO @resTB
(Table_Name, Column_Name, Reason ) VALUES
(@tablename, @columnname, 'CONSTRAINT "'+@db_constraint_name + '" not defined on COLUMN in SUB DATABASE ')
END
FETCH NEXT FROM CC INTO @db_constraint_name,@db_constraint_type1
END
CLOSE CC
DEALLOCATE CC
FETCH TC INTO @tablename ,@columnname,@isnullable,@datatype
END
CLOSE TC
DEALLOCATE TC
-- Inserting those list of records from the sub database which have not been touched
-- That implies the columns list is explicitly in sub database but not in main database
INSERT INTO @resTB
(Table_Name, Column_Name, Reason )
SELECT TABLE_NAME, COLUMN_NAME, 'COLUMN not present in MAIN DATABASE but present in SUB DATABASE'
FROM @subDB_columnsInfo
WHERE VSTATUS = 0
-- Resultant table with contains all the defects and their reasons after comparing those two database
IF EXISTS ( SELECT * FROM @resTB )
BEGIN
SELECT Table_Name, Column_Name, Reason
FROM @resTB
ORDER BY TABLE_NAME, REASON
END
ELSE
BEGIN
SELECT ' Both the databases Match at every instance ' as Result_Message
END
END -- END OF DB_SUB CHECK IF BLOCK
ELSE
BEGIN
SELECT 'Database " ' + @dbName_Sub +'" does not Exists ' AS ErrorMessage
END
END -- END OF DB_MAIN CHECK IF BLOCK
ELSE
SELECT 'Database "' + @dbName_Main +'" does not Exists' AS ErrorMessage
END
No comments:
Post a Comment