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
c.name AS 'Column Name',
t.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'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
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.
