Looking for T-SQL Script to find and capture Table structure differences between Prod and DEV environment

Question

Hi All,
I have a requirement to compare Dev and Prod environment Tables and find the difference and store them into a table.

Do you have any T-SQL script to do so?

Answer 1

I prefer MS SSDT = "SQL Server Data Tools" => Schema Compare feature.

How to: Use Schema Compare to Compare Different Database Definitions
How to: Compare and Synchronize the Data of Two Databases

Answer 2

This link helped me and I created my own script:

https://www.mssqltips.com/sqlservertip/4824/easy-way-to-compare-sql-server-table-schemas/

--DROP TABLE [dbo].[Table_Comparison_Result]
--CREATE TABLE [dbo].Table_Comparison_Result ON [PRIMARY]

--SELECT [DEV_Table_Name],
-- [PROD_Table_Name],
-- [DEV_ColumnName],
-- [PROD_ColumnName],
-- [DEV_is_nullable],
-- [PROD_is_nullable],
-- [DEV_Datatype],
-- [PROD_Datatype],
-- [DEV_is_identity],
-- [PROD_is_identity],
-- [CreatedDateTime]
--FROM [dbo].[Table_Comparison_Result]

/Query To Compare Structure of 2 Tables/
DECLARE @VCounterDev BIGINT,
@VMaxDev BIGINT,
@VCounterProd BIGINT,
@VMaxProd BIGINT,
@VCounter BIGINT,
@VMax BIGINT,
@VDevServer Varchar(MAX),
@VProdServerr Varchar(MAX),
@VDevDB Varchar(MAX),
@VProdDB Varchar(MAX)

/Replace these Values/
SET @VDevServer='[Dev_Server]'
SET @VDevDB='[Dev_Database]'

SET @VProdServerr='[Prod_Server]'
SET @VProdDB='[Prod_Database]'

IF OBJECT_ID('tempdb..##DevTables') IS NOT NULL
DROP TABLE ##DevTables

IF OBJECT_ID('tempdb..##ProdTables') IS NOT NULL
DROP TABLE ##ProdTables

IF OBJECT_ID('tempdb..#LoopDev') IS NOT NULL
DROP TABLE #LoopDev

IF OBJECT_ID('tempdb..#LoopProd') IS NOT NULL
DROP TABLE #LoopProd

IF OBJECT_ID('tempdb..#Loop') IS NOT NULL
DROP TABLE #Loop

DECLARE @VDevTSQL1 VARCHAR(MAX),
@VProdTSQL2 VARCHAR(MAX)

SET @VDevTSQL1=
'SELECT DISTINCT
TABLE_SCHEMA + ''.'' + TABLE_NAME AS Dev_Table_Name,
Row_NUMBER() OVER( ORDER BY TABLE_SCHEMA + ''.'' + TABLE_NAME) AS DevRowNumber
INTO ##DevTables
FROM '+@VDevServer+'.'+@VDevDB+'.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_SCHEMA NOT LIKE ''ReportingIntegration%''
ORDER BY 1'

SET @VProdTSQL2=
'SELECT DISTINCT
TABLE_SCHEMA + ''.'' + TABLE_NAME AS Prod_Table_Name,
Row_NUMBER() OVER( ORDER BY TABLE_SCHEMA + ''.'' + TABLE_NAME) AS ProdRowNumber
INTO ##ProdTables
FROM '+@VProdServerr+'.'+@VProdDB+'.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_SCHEMA NOT LIKE ''ReportingIntegration%''
ORDER BY 1'

EXEC(@VDevTSQL1)
EXEC(@VProdTSQL2)

/-------------------------------------------------------------------------------------------------------------------------------------------------/
/Get DEV Only Tables and Details/
SELECT DISTINCT
T1.Dev_Table_Name,
ROW_NUMBER() OVER(ORDER BY T1.Dev_Table_Name) AS ForLoop
INTO #LoopDev
FROM
(
SELECT DISTINCT
Dev1.Dev_Table_Name AS Dev_Table_Name
FROM ##DevTables Dev1
FULL OUTER JOIN ##ProdTables Prod1 ON Dev1.[Dev_Table_Name] = Prod1.[Prod_Table_Name]
WHERE Dev1.[Dev_Table_Name] IS NOT NULL AND Prod1.[Prod_Table_Name] IS NULL
) T1
ORDER BY 1

SET @VCounterDev=(SELECT MIN(ForLoop) FROM #LoopDev)
SET @VMaxDev=(SELECT MAX(ForLoop) FROM #LoopDev)

WHILE @VCounterDev<=@VMaxDev
BEGIN
DECLARE @VDevOnlyTable VARCHAR(MAX),
@VDevTSQL3 VARCHAR(MAX)

SET @VDevOnlyTable= (SELECT Dev_Table_Name FROM #LoopDev WHERE ForLoop=@VCounterDev)

--SELECT @VDevOnlyTable
SET @VDevTSQL3=
'INSERT INTO '+@VDevDB+'.[dbo].[Table_Comparison_Result]
SELECT '''+@VDevDB+'.'+@VDevOnlyTable+''' AS DEV_Table_Name,
NULL AS Prod_Table_Name,
DEV.name AS DEV_ColumnName,
NULL AS PROD_ColumnName,
DEV.is_nullable AS DEV_is_nullable,
NULL AS PROD_is_nullable,
DEV.system_type_name AS DEV_Datatype,
NULL AS PROD_Datatype,
DEV.is_identity_column AS DEV_is_identity,
NULL AS PROD_is_identity,
GETDATE() AS CreatedDateTime
FROM sys.dm_exec_describe_first_result_set(N''SELECT * FROM '+@VDevServer+'.'+@VDevDB+'.'+@VDevOnlyTable+''', NULL, 0) DEV'

--PRINT(@VDevTSQL3)
EXEC(@VDevTSQL3)
SET @VCounterDev=@VCounterDev+1
END

/-------------------------------------------------------------------------------------------------------------------------------------------------/
/Get Prod Only Tables and Details/
SELECT DISTINCT
T1.Prod_Table_Name,
ROW_NUMBER() OVER(ORDER BY T1.Prod_Table_Name) AS ForLoop
INTO #LoopProd
FROM
(
SELECT DISTINCT
Prod1.Prod_Table_Name AS Prod_Table_Name
FROM ##DevTables Dev1
FULL OUTER JOIN ##ProdTables Prod1 ON Dev1.[Dev_Table_Name] = Prod1.[Prod_Table_Name]
WHERE Dev1.[Dev_Table_Name] IS NULL AND Prod1.[Prod_Table_Name] IS NOT NULL
) T1
ORDER BY 1

SET @VCounterProd=(SELECT MIN(ForLoop) FROM #LoopProd)
SET @VMaxProd=(SELECT MAX(ForLoop) FROM #LoopProd)

WHILE @VCounterProd<=@VMaxProd
BEGIN
DECLARE @VProdOnlyTable VARCHAR(MAX),
@VProdTSQL4 VARCHAR(MAX)

SET @VProdOnlyTable= (SELECT Prod_Table_Name FROM #LoopProd WHERE ForLoop=@VCounterProd)

--SELECT @VDevOnlyTable
SET @VProdTSQL4=
'INSERT INTO '+@VDevDB+'.[dbo].[Table_Comparison_Result]
SELECT NULL AS DEV_Table_Name,
'''+@VProdDB+'.'+@VProdOnlyTable+''' AS Prod_Table_Name,
NULL AS DEV_ColumnName,
PROD.name AS PROD_ColumnName,
NULL AS DEV_is_nullable,
PROD.is_nullable AS PROD_is_nullable,
NULL AS DEV_Datatype,
PROD.system_type_name AS PROD_Datatype,
NULL AS DEV_is_identity,
PROD.is_identity_column AS PROD_is_identity,
GETDATE() AS CreatedDateTime
FROM sys.dm_exec_describe_first_result_set(N''SELECT * FROM '+@VProdServerr+'.'+@VProdDB+'.'+@VProdOnlyTable+''', NULL, 0) PROD'

--PRINT(@VProdTSQL4)
EXEC(@VProdTSQL4)
SET @VCounterProd=@VCounterProd+1
END

/-------------------------------------------------------------------------------------------------------------------------------------------------/
/Get Lopping Numbers for Columns and Data Type Mismatch/
SELECT DISTINCT
T1.Table_Name,
ROW_NUMBER() OVER(ORDER BY T1.Table_Name) AS ForLoop
INTO #Loop
FROM
(
SELECT DISTINCT
Dev1.Dev_Table_Name AS Table_Name
FROM ##DevTables Dev1
INNER JOIN ##ProdTables Prod1 ON Dev1.[Dev_Table_Name] = Prod1.[Prod_Table_Name]
) T1
ORDER BY 1

SET @VCounter=(SELECT MIN(ForLoop) FROM #Loop)
SET @VMax =(SELECT MAX(ForLoop) FROM #Loop)

WHILE @VCounter<=@VMax
BEGIN
DECLARE @VDevTableName VARCHAR(MAX),
@VProdTableName VARCHAR(MAX),
@VTSQL5 VARCHAR(MAX)

SET @VDevTableName= (SELECT Table_Name FROM #Loop WHERE ForLoop=@VCounter)
SET @VProdTableName= (SELECT Table_Name FROM #Loop WHERE ForLoop=@VCounter)
--SELECT @VTableName
SET @VTSQL5=
'INSERT INTO '+@VDevDB+'.[dbo].[Table_Comparison_Result]
SELECT '''+@VDevDB+'.'+@VDevTableName+''' AS DEV_Table_Name,
'''+@VProdDB+'.'+@VProdTableName+''' AS Prod_Table_Name,
DEV.name AS DEV_ColumnName,
PROD.name AS PROD_ColumnName,
DEV.is_nullable AS DEV_is_nullable,
PROD.is_nullable AS PROD_is_nullable,
DEV.system_type_name AS DEV_Datatype,
PROD.system_type_name AS PROD_Datatype,
DEV.is_identity_column AS DEV_is_identity,
PROD.is_identity_column AS PROD_is_identity,
GETDATE() AS CreatedDateTime
FROM sys.dm_exec_describe_first_result_set(N''SELECT * FROM '+@VDevServer+'.'+@VDevDB+'.'+@VDevTableName+''', NULL, 0) DEV
FULL OUTER JOIN sys.dm_exec_describe_first_result_set(N''SELECT * FROM '+@VProdServerr+'.'+@VProdDB+'.'+@VProdTableName+''', NULL, 0) PROD ON DEV.name = PROD.name
WHERE (DEV.name <> PROD.name OR DEV.is_nullable <> PROD.is_nullable OR DEV.system_type_name <> PROD.system_type_name OR DEV.is_identity_column <> PROD.is_identity_column OR DEV.name IS NULL OR PROD.name IS NULL)'

--PRINT(@VTSQL5)
EXEC(@VTSQL5)
SET @VCounter=@VCounter+1
END

Answer 3

Hi @Shivendoo Kumar ,

Glad you have found a solution.

The following article is also for your reference:

Different Ways to Compare SQL Server Tables Schema and Data

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

If you have VS (Visual Studio) there is a free tool that you can use to compare schema and data.

https://learn.microsoft.com/en-us/sql/ssdt/how-to-use-schema-compare-to-compare-different-database-definitions?view=sql-server-ver15