Archive

Archive for November, 2011

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
%d bloggers like this: