To compare fields between two table

[Sam]

Hello,
I would like to create a stored procedure that would compare the fields
of two tables and their types. If they are different the user is
warned.
How can I do that ?
thx

[David Portas]

SELECT COALESCE(A.table_name,B.table_name),
COALESCE(A.column_name,B.column_name),
A.data_type, B.data_type
FROM information_schema.columns AS A
FULL JOIN information_schema.columns AS B
ON A.column_name = B.column_name
WHERE COALESCE(A.data_type,'')<>COALESCE(B.data_type,'')
AND A.table_schema = 'dbo'
AND A.table_name = 'Table1'
AND B.table_schema = 'dbo'
AND B.table_name = 'Table2'

--
David Portas
SQL Server MVP
--

[Sam]

Thanks.
Could you explain me this request. I don't really understand it.
Especially the COALESCE and FULL JOIN parts.

Thank you again.

[David Portas]

FULL JOIN is rerquired if there is a case where a column exists in one
table and not in the other. In that case the table name, column name
and other attributes will be NULL for the table where the column is
missing so COALESCE is used for the comparison and return values.
COALESCE returns the first non-NULL value among its arguments.

--
David Portas
SQL Server MVP
--