debugsqlMinor
Compare and auto fix conflicts between two databases T-sql
Viewed 0 times
fixdatabasesautosqltwobetweenandcompareconflicts
Problem
Consider we have a database for an windows application it's for 2 years ago, now we have changed many things in database so there is differences and we don't know what are the differences.
I don't want to use compare tools like Red Gate compare tool.
What's the best way to see new columns are in new database :
Query result like :
Thank you
Edit :
I need a query after this to fix the conflicts so both databases be the same :
Add NewDb columns to OldDb,delete OldDb columns that are not in NewDb and update all columns to NewDb version ( if two columns datatype or something is different in two databases. set the NewDb's to OldDb too.)
I don't want to use compare tools like Red Gate compare tool.
What's the best way to see new columns are in new database :
Query result like :
New Db Name | Schema | Table Name | Column Name | Default Value | IsNullable | DataType
NewDb | dbo | myTable | newColumn | '' | No | nvarchar(30)Thank you
Edit :
I need a query after this to fix the conflicts so both databases be the same :
Add NewDb columns to OldDb,delete OldDb columns that are not in NewDb and update all columns to NewDb version ( if two columns datatype or something is different in two databases. set the NewDb's to OldDb too.)
Solution
Columns in old database but not in new , or default value, nullable and data type changed:
Columns in new database but not in old :
You can change
I wrote this question and answer because I've needed this and I've spent so much of time on this.
So I hope everyone can find and use this easily :).
I'm looking forward for opinions for make this better.
SELECT t1.*,
t2.*
FROM [OldDb].[INFORMATION_SCHEMA].[COLUMNS] t1
LEFT JOIN [NewDb].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL
OR ( t1.TABLE_NAME = t2.TABLE_NAME
AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
AND t1.COLUMN_NAME = t2.COLUMN_NAME
AND ( t1.DATA_TYPE != t2.DATA_TYPE
OR t1.IS_NULLABLE != t2.IS_NULLABLE
OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
ORDER BY t1.COLUMN_NAMEColumns in new database but not in old :
You can change
[OldDb] place with [NewDb] in queryI wrote this question and answer because I've needed this and I've spent so much of time on this.
So I hope everyone can find and use this easily :).
I'm looking forward for opinions for make this better.
Code Snippets
SELECT t1.*,
t2.*
FROM [OldDb].[INFORMATION_SCHEMA].[COLUMNS] t1
LEFT JOIN [NewDb].[INFORMATION_SCHEMA].[COLUMNS] t2
ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE t2.COLUMN_NAME IS NULL
OR ( t1.TABLE_NAME = t2.TABLE_NAME
AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
AND t1.COLUMN_NAME = t2.COLUMN_NAME
AND ( t1.DATA_TYPE != t2.DATA_TYPE
OR t1.IS_NULLABLE != t2.IS_NULLABLE
OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
ORDER BY t1.COLUMN_NAMEContext
StackExchange Database Administrators Q#175883, answer score: 4
Revisions (0)
No revisions yet.