473,320 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 8445
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Gleep | last post by:
Hey Guys, I've got a table called Outcomes. With 3 columns and 15 rows 1st col 2nd col 3rdcol outcome date price There are 15 rows...
1
by: Simon Gare | last post by:
Hi I need to compare a dynamic field in an asp page to a field in another table, if there is no match then i would like to chane the row colour ( see code below). The problem Im having is...
4
by: Maur | last post by:
Hi all, I have 2 tables say t_OLD and t_NEW. The new has corrections for audit purposes. They are identical in all respects (i.e. new is a copy of old and then changes are made to t_new) ...
2
by: Rachel Curran | last post by:
Please can anybody help me with the following: I have two separate excel spreadsheets that I have imported into access, each sheet holds the same fields. Both spreadsheets hold all information...
5
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the...
1
by: Prakash RudraRaju | last post by:
Hi, I have recently migrated MSAccess Tables (nearly 120) to MySQL. To test successful migration I want to compare all tables. I have linked MySQL tables through ODBC connection. I want to...
9
by: geronimo_me | last post by:
Hi, I am atempting to compare part of a field with the whole of another field in access. Is this possible? Basically I have 2 tables with the following info: Table1 Field1 = MR.
0
by: dfs9 | last post by:
In the article "Delete Duplicate Records From Access Tables" By Danny Lesandrini writes the following: This final suggestion is the most flexible and accurate. Given any table, it generates a...
4
by: dfs9 | last post by:
In the article "Delete Duplicate Records From Access Tables" By Danny Lesandrini writes the following: This final suggestion is the most flexible and accurate. Given any table, it generates a...
5
by: Edd E | last post by:
Hi, I have a database to store my analyses (Access 2002, WinXp), the basic structure is: TABLE 1 = Sample Info TABLE 2 = Analysis type 1 TABLE 3 = Analysis type 2 TABLE 4 = Analysis type 3 ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.