How to compare two tables structure wise
Comparing two table structures is an important work when we have multiple environments for a application and it uses all the environments at different time or in different zones.
This is important when some applications give issues in one environment and doesn't give in another environment.
This is very easy if we buy some tools like Redgate compare which makes the life of DBA easy by comparing the complete objects of a database.
In the below I have shown a very simple way to compare the two table structures.
In the below query, search for "Table1" and "Table2" and change it with your respective table names.
select * from (
SELECT
[column].COLUMN_NAME AS [Column_Name],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].IS_NULLABLE AS [nullable]
FROM
INFORMATION_SCHEMA.TABLES [table] INNER JOIN
INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME ='Table1'
)A
except
select * from (
SELECT
[column].COLUMN_NAME AS [Column_Name],
[column].DATA_TYPE AS [datatype],
[column].CHARACTER_MAXIMUM_LENGTH AS [Character_Length],
[column].NUMERIC_PRECISION AS Numeric_precision,
[column].IS_NULLABLE AS [nullable]
FROM
INFORMATION_SCHEMA.TABLES [table] INNER JOIN
INFORMATION_SCHEMA.COLUMNS [column] ON [table].TABLE_NAME = [column].TABLE_NAME
WHERE
[table].TABLE_TYPE = 'BASE TABLE'
AND [table].TABLE_NAME ='Table2'
)B
