Find the structure difference between tables of SQL Server in two different databases but on the same server.

The script uses the package dm_exec_describe_first_result_set to find the difference between two similar objects as shown below:

Example shows the difference of the table with the different database but presents on the same Server as:

SELECT 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  
FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM [AdventureWorks2017].HumanResources.Department', NULL, 0) DEV 
FULL OUTER JOIN  sys.dm_exec_describe_first_result_set (N'SELECT * FROM [AdventureWorks2022].HumanResources.Department', NULL, 0) PROD 
ON DEV.name = PROD.name 

The following script is used to find all the structural differences in the database table by specifying the database name but both should be present on the same server.

Following point need to specify or note : (3 points need to fill manually)

1. Specify Variable @dbtestname — Enter the first database used to compare
2. Specify Variable @dbprodname — Enter the Second database used to compare
3. Specify Variable @schemanametest — Enter Schema name (use dbo as default)
4. Specify Variable @schemanameprod — Enter Schema name ( use dbo as default)

Note: Bold in the script needs to fill manually and then the User must have both database access. The script only returns the difference in datatype, identity column, or is null. Both databases should be present on the same server otherwise configure the linked server between server to use this script.

Script:

DECLARE @dbtestname VARCHAR(50) -- database name 
DECLARE @dbprodname VARCHAR(50) -- database name 
DECLARE @schemanametest VARCHAR(256) -- Schema name 
DECLARE @schemanameprod VARCHAR(256) -- Schema name 
DECLARE @TABLENAME VARCHAR(256) -- table name
DECLARE @TESTNAME VARCHAR(256) -- variable 
DECLARE @PRODNAME VARCHAR(256) -- variable 

SET @dbtestname = 'AdventureWorks2017'
SET @dbprodname = 'AdventureWorks2022'

SET @schemanametest = 'dbo'
SET @schemanameprod = 'dbo'

create table #Temp
(
    TABLE_NAME varchar(250), 
    DEV_column_name Varchar(250), 
    PROD_column_name varchar(250), 
    DEV_is_nullable varchar(250), 
    PROD_is_nullable varchar(250),
    DEV_Datatype varchar(250),
    PROD_Datatype varchar(250), 
    DEV_is_identity varchar(250), 
    PROD_is_identity varchar(250)
)

DECLARE db_cursor CURSOR FOR
select table_name from INFORMATION_SCHEMA.tables where Table_type='BASE TABLE' and table_catalog = @dbprodname
UNION
Select table_name from INFORMATION_SCHEMA.tables where Table_type='BASE TABLE' and table_catalog = @dbtestname

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tablename  

WHILE @@FETCH_STATUS = 0  
BEGIN  
SET @TESTNAME = 'SELECT * FROM '+@dbtestname+'.'+@schemanametest+'.'+@tablename
SET @PRODNAME = 'SELECT * FROM '+@dbprodname+'.'+@schemanameprod+'.'+@tablename

insert into #temp
SELECT @tablename as 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  
FROM sys.dm_exec_describe_first_result_set (@TESTNAME, NULL, 0) DEV 
FULL OUTER JOIN  sys.dm_exec_describe_first_result_set (@PRODNAME, NULL, 0) PROD 
ON DEV.name = PROD.name where (DEV.is_nullable <> PROD.is_nullable or DEV.system_type_name <> PROD.system_type_name
or DEV.is_identity_column <> PROD.is_identity_column)

     FETCH NEXT FROM db_cursor INTO @tablename  
END 
select * from #temp
If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End
CLOSE db_cursor  
DEALLOCATE db_cursor