Search for tables with schema differences in SQL Server

Do you want to know how to search for tables with schema differences in SQL Server?

You've come to the right place!

Follow me as I show you the TSQL code that will list the tables with schema differences and show you what are the differences.


This code is especially important when you have a set of tables that must have the same schema.

Without further ado, here's the stored procedure that will search for tables that have schema differences:

CREATE PROCEDURE [dbo].[SearchTableSchemaDifferences]
as
begin
set nocount on

print 'Searching for tables with schema differences:'

declare itrTable cursor for
select Table_name from INFORMATION_SCHEMA.Tables
where Table_name like '%SomeBasename%' and table_type = 'BASE TABLE'

declare @itrTableName nvarchar(50)
declare @baseTableName nvarchar(50)
Create TABLE #TempCmpTbl
(
[TableName] nvarchar (50),
[column_name] nvarchar (50),
[data_type] nvarchar (50)
);

open itrTable;
fetch next from itrTable into @itrTableName
while @@Fetch_status = 0
begin

set @baseTableName = SUBSTRING(@itrTableName, 10, LEN(@itrTableName))

insert into #TempCmpTbl ([TableName], [column_name], [data_type])
(
SELECT MIN(TableName) as TableName, column_name, data_type
FROM
(
SELECT 'Table A' as TableName, column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'+@baseTableName
union all
SELECT 'Table B' as TableName,column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableB'+@baseTableName
) tmp
GROUP BY column_name, data_type
HAVING COUNT(*) = 1
)

declare @diff int
select @diff = count(*) from #TempCmpTbl


if @diff > 0
begin
print @baseTableName + ' !'
select * from #TempCmpTbl
print '______'
end

truncate table #TempCmpTbl

fetch next from itrTable into @itrTableName
end

CLOSE itrTable
DEALLOCATE itrTable

print 'Done.'
end

The algorithm works like this:
1. Find the table names to compare the schemas
2. For each table we will identify is there is a data type in the schema of one of the tables that doesn't match.

The most important part of the code is when the table schema comparison occurs:

SELECT MIN(TableName) as TableName, column_name, data_type
FROM
(
SELECT 'Table A' as TableName, column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'+@baseTableName
union all
SELECT 'Table B' as TableName,column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableB'+@baseTableName
) tmp
GROUP BY column_name, data_type
HAVING COUNT(*) = 1

This code is inspired by an article at Jeff's SQL Server Blog.

I could use a parameter for the "where Table_name like" clause... but hey: this is version 1.0.0! :)

Any comments on how to improve this code are more than welcome!

Be a part of our private list!

Enter your e-mail and access The Rabbit Way's exclusive offers.

Enter your Email


Preview | Powered by FeedBlitz

Or read this related articles

Widget by Hoctro | Jack Book