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]

Re: To compare fields between two table

SELECT COALESCE(A.tabl e_name,B.table_ name),
COALESCE(A.colu mn_name,B.colum n_name),
A.data_type, B.data_type
FROM information_sch ema.columns AS A
FULL JOIN information_sch ema.columns AS B
ON A.column_name = B.column_name
WHERE COALESCE(A.data _type,'')<>COAL ESCE(B.data_typ e,'')
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]

Re: To compare fields between two table

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]

Re: To compare fields between two table

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
--