How to compare multiple tables in SQL Server

Aman Raiyyani
How to compare multiple tables in SQL Server

In SQL Server, you can use the sp_help and sp_columns system stored procedures to compare the table structure of multiple tables.

sp_help: The sp_help stored procedure returns information about the table, such as the name, owner, and columns.

EXEC sp_help ‘table1’

EXEC sp_help ‘table2’

sp_columns: The sp_columns stored procedure returns detailed information about the columns of a table, such as data type, length, and nullability.

EXEC sp_columns ‘table1’

EXEC sp_columns ‘table2’

You can also use the below query to compare the schema of multiple tables:

SELECT AS 'Column Name', AS 'Data Type',
    c.max_length AS 'Max Length',
    c.is_nullable AS 'Is Nullable',
    ISNULL(i.is_primary_key, 0) AS 'Primary Key'
    sys.columns c
    sys.types t ON c.user_type_id = t.user_type_id
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    c.object_id = OBJECT_ID('table1') OR c.object_id = OBJECT_ID('table2')

You can replace the table1 and table2 with your table names. This query returns the column name, data type, max length, nullability, and primary key information for the specified tables.

By using these techniques, you can easily compare the table structure of multiple tables in SQL Server.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…