SQLTestUser (3/2/2011)
I am sorry i should have had more details. I was trying to compare data and columns in two DB's where all the tables are identical but some tables in the second db has more columns in some columns, and data associated with that column, please let me know if i can do that without a tool
This will show you tables with columns that are in db2, but not in db1, or are in db1 in a different order. If you don't care about the order, remove the sc.column id everywhere it's referenced in the query.
Change db1 and db2 to your appropriate database names. The database that you suspect has more columns should be the one before the EXCEPT operator.
SELECT TableName = st.NAME,
ColumnName = sc.NAME,
sc.column_id
FROM [db2].sys.tables st
JOIN [db2].sys.columns sc
ON st.object_id = sc.OBJECT_ID
EXCEPT
SELECT TableName = st.NAME,
ColumnName = sc.NAME,
sc.column_id
FROM [db1].sys.tables st
JOIN [db1].sys.columns sc
ON st.object_id = sc.OBJECT_ID
ORDER BY TableName, column_id
Does this get you what you're looking for?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes