CODEWITHSUNDEEP
sqlsql-server
StackHelp
StackHelp

Reputation: 13

How to check if two tables are structurally same?

I have two tables in a SQL Server database. I want to check if these two tables are structurally same. I used UNION/ EXCEPT operator to check this and found that these two tables are not identical.

Here are my questions:

  1. How do we know if two tables are the same in structure?

  2. If they are not same, how do we get the details of differences? If a column is missing in one table how do we get the name of the missing column?

  3. If there is a data type mismatch between a column in these two tables, can we get the name of the column with different data type?

Upvotes: 1

Views: 2642

Answers (5)

I A Khan
I A Khan

Reputation: 8839

Here you can compare Tables

If you want to compare tables with the same database just provide same DATABASENAMESOURCE and DATABASENAMEDESTINATION names

use master
go


DECLARE @DB1 VARCHAR(100) = 'DATABASENAMESOURCE';
DECLARE @Table1 VARCHAR(100) = 'TABLESOURCE';


DECLARE @DB2 VARCHAR(100) = 'DATABASENAMEDESTINATION';
DECLARE @Table2 VARCHAR(100) = 'DESTINATION';

DECLARE @SQL NVARCHAR(MAX);


SET @SQL = 
'
SELECT 
       Table1.DBName,
       Table1.SchemaName,
       Table1.TableName,
       Table1.ColumnName,
       Table1.name DataType,
       Table1.Length,
       Table1.Precision,
       Table1.Scale,
       Table1.Is_Identity,
       Table1.Is_Nullable,
       Table2.DBName,
       Table2.SchemaName,
       Table2.TableName,
       Table2.ColumnName,
       Table2.name DataType,
       Table2.Length,
       Table2.Precision,
       Table2.Scale,
       Table2.Is_Identity,
       Table2.Is_Nullable
FROM   
    (SELECT  
           ''' + @DB1 + ''' DbName,
           SCHEMA_NAME(t.schema_id) SchemaName,
           t.Name TableName,
           c.Name ColumnName,
           st.Name,
           c.Max_Length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @DB1 + '.sys.tables t
           INNER JOIN ' + @DB1 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
    WHERE  t.Name = ''' + @Table1 + ''') Table1 
    FULL OUTER JOIN
    (SELECT 
           ''' + @DB2 + ''' DbName,
           SCHEMA_NAME(t.schema_id) SchemaName,
           t.name TableName,
           c.name ColumnName,
           st.Name,
           c.max_length Length,
           c.Precision,
           c.Scale,
           c.Is_Identity,
           c.Is_Nullable
    FROM   ' + @DB2 + '.sys.tables t
           INNER JOIN ' + @DB2 + '.sys.columns c ON t.Object_ID = c.Object_ID
           INNER JOIN sys.types st ON St.system_type_id = c.System_Type_id AND st.user_type_id = c.user_type_id
    WHERE  t.Name = ''' + @Table2 + ''') Table2
    ON Table1.ColumnName = Table2.ColumnName
ORDER BY CASE WHEN Table1.ColumnName IS NULL THEN 2 ELSE 1 END, Table1.ColumnName
'

EXEC sp_executesql @SQL

Upvotes: 0

MRsa
MRsa

Reputation: 684

Using sys.dm_exec_describe_first_result_set you can get info about columns in table/query ... In this sample you can see how to make comparison of column name, nullability, datatype and identity

SELECT DEV.name as DEV_ColumnName, 
PROD.name as PROD_ColumnName, 
DEV.is_nullable as DEV_is_nullable, 
PROD.is_nullable as PROD_is_nullable, 
DEV.system_type_name as DEV_Datatype, 
PROD.system_type_name as PROD_Datatype, 
DEV.is_identity_column as DEV_is_identity, 
PROD.is_identity_column as PROD_is_identity  
FROM sys.dm_exec_describe_first_result_set (N'SELECT * FROM dbo.TABLE1', NULL, 0) DEV 
FULL OUTER JOIN  sys.dm_exec_describe_first_result_set (N'SELECT * FROM dbo.TABLE2', NULL, 0) PROD 
ON DEV.name = PROD.name

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

In SSDT you can use schema compare to see if there is any differences in columns, tables, datatypes and so forth.

Schema Compare SSDT

If you want to code your way out of it. You can query information_schema catalog

System Information Schema

Upvotes: 1

MiloBellano
MiloBellano

Reputation: 396

If you are using Sql Server Management Studio, right click on table name

script table as > CREATE

and you get DDL of the table, this is just a practical way to see the table as it was created. Then of course, as everybody already said, information_schema has all the info you need.

Upvotes: 0

nityanarayan
nityanarayan

Reputation: 378

as @jarlh commented. you can find the info in information_schema.columns or,
you could use this tool called sql-dbdiff : https://code.google.com/archive/p/sql-dbdiff/

For more idea, you could reffer this discussion: Easiest Way To Diff Two Table Schemas In SQL Server 2008?

Upvotes: 0

Related Questions