Compare Columns Of Two Tables In SQL Server

I was working on a project to check and compare columns of a table with another table to make sure the structure was the same. While we DBAs work regularly on multiple databases, we have a general tendency to use sp_help system procedure or directly use ALT + F1 keys to check the table structure. But if we must check the table structure of multiple tables manually, then such methods should be ruled out.

I have a quick script to help you with comparing columns of two tables using information_schema (System Information Schema Views).

A simple format to compare columns of two tables:

SELECT
db_name() AS DBNAME,
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'table1'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'table2'
)


Example:

In my example, I have created two tables as test1 and test2. 

Table structure - test1  looks like:

Compare Columns Two Tables SQL Server 1


Table structure - test2  looks like:

Compare Columns Two Tables SQL Server 2













Now, let's just the query to check the mismatch between the two tables and its output.

SELECT
db_name() AS DBNAME,
c2.table_name,
c2.COLUMN_NAME
FROM [INFORMATION_SCHEMA].[COLUMNS] c2
WHERE table_name = 'test1'
AND c2.COLUMN_NAME NOT IN
( SELECT column_name
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE table_name = 'test2'
)

Compare Columns Two Tables SQL Server 3















The above result gives us the missing column in test2 when compared with the test1 table. 



0 comments:

Post a Comment