Home > T-SQL > T-SQL: Check column differences between two tables from different databases

T-SQL: Check column differences between two tables from different databases


In this thread, i would like to show some of my queries which i am using to compare two tables from different tables. This query will return will column difference between the tables and can alter the table to match the structure.

 

Script:

DECLARE @TableName VARCHAR (50)

SET @TableName = 'Product'

DECLARE @ColumnName VARCHAR (100)
DECLARE @ColumnMismatch TABLE
(
difcols VARCHAR (500)
)
DECLARE @SourceDatabaseName VARCHAR (100) = '[Training]'
DECLARE @TargetDatabaseName VARCHAR (100)= '[TrainingNew]'
DECLARE @insertsql VARCHAR (max)
DECLARE @SrcInsertsql VARCHAR (max)
DECLARE @TgtInsertsql VARCHAR (max)
DECLARE @sql NVARCHAR (max)

SET @insertsql = 'select COLUMN_NAME from '
+ @SourceDatabaseName
+ '.INFORMATION_SCHEMA.COLUMNS where table_name = '''
+ @TableName + ''' except select COLUMN_NAME from '
+ @TargetDatabaseName
+ '.INFORMATION_SCHEMA.columns where table_name = '''
+ @TableName + ''''

PRINT @insertsql

INSERT INTO @ColumnMismatch
EXEC(@insertSql )

DECLARE altertable CURSOR FOR
SELECT *
FROM   @ColumnMismatch

OPEN altertable

FETCH next FROM altertable INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = N'alter table ' + @TargetDatabaseName
+ '.[dbo].[' + @TableName + '] Add ' + @ColumnName
+ ' varchar(255)'

PRINT @sql

EXEC(@sql )

FETCH next FROM altertable INTO @ColumnName
END

CLOSE altertable

DEALLOCATE altertable

The code above will check the for column difference from source table to Target table. All you have to do is to provide the table name and DB names in the parameter. The Cursor loops the mismatch columns and alter the second table which doesn’t have columns available in first table. By this way, we can the alter tables dynamically.

Happy Querying 😉

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: