469,903 Members | 1,573 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

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

Jul 23 '05 #1
3 8327
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
--

Jul 23 '05 #2
Sam
Thanks.
Could you explain me this request. I don't really understand it.
Especially the COALESCE and FULL JOIN parts.

Thank you again.

Jul 23 '05 #3
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
--

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Simon Gare | last post: by
1 post views Thread by Prakash RudraRaju | last post: by
4 posts views Thread by dfs9 | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.