sql compare tables
(SELECT * FROM T1 MINUS SELECT * FROM T2) -- Rows that are in T1 but not in T2
UNION ALL
(SELECT * FROM T2 MINUS SELECT * FROM T1); -- Rows that are in T2 but not in T1sql compare tables
(SELECT * FROM T1 MINUS SELECT * FROM T2) -- Rows that are in T1 but not in T2
UNION ALL
(SELECT * FROM T2 MINUS SELECT * FROM T1); -- Rows that are in T2 but not in T1sql compare tables for differences
-- Find the different tables and columns between DB_NBR_1 and DB_NBR_2
-- (just replace DB_NBR_1 and DB_NBR_2)
select isnull(db1.table_name, db2.table_name) as [table],
isnull(db1.column_name, db2.column_name) as [column],
db1.column_name as scripts_col_name
,db1.column_type as scripts_col_type
,db1.column_precision as scripts_col_precision
,db2.column_name as liquid_col_name
,db2.column_type as liquid_col_type
,db2.column_precision as liquid_col_precision
from
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
,y.name as column_type
,y.precision as column_precision
from [DB_NBR_1].sys.tables as tab
inner join [DB_NBR_1].sys.columns as col
on tab.object_id = col.object_id
JOIN sys.types y ON y.user_type_id = col.user_type_id
) db1
full outer join
(select schema_name(tab.schema_id) + '.' + tab.name as table_name,
col.name as column_name
,y.name as column_type
,y.precision as column_precision
from [DB_NBR_2].sys.tables as tab
inner join [DB_NBR_2].sys.columns as col
on tab.object_id = col.object_id
JOIN sys.types y ON y.user_type_id = col.user_type_id
) db2
on db1.table_name = db2.table_name
and db1.column_name = db2.column_name
where (db1.column_name is null or db2.column_name is null)
order by 1, 2, 3Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
