Querying SQL Server for the same table structure differences

 Use [Database name]GO    DECLARE @TableName1 NVARCHAR( -); DECLARE @TableName2 NVARCHAR( -); DECLARE @DataName1 NVARCHAR( -); DECLARE @DataName2 NVARCHAR( -); SET @DataName1='Library 1'; SET @DataName2='Library 2'; SET @TableName1='Table 1'; SET @TableName2='Table 2';SET @TableName2=@TableName1; SELECT  ( Case  whenA.colorder=1  ThenD.nameElse NULL End) Table name 1, a.name field name 1, ( Case  when(SELECT Count(*) fromsysobjectsWHERE(Nameinch(SELECTName fromsysindexesWHERE(ID=a.ID) and(indidinch  (SELECTIndid fromSysindexkeysWHERE(ID=a.ID) and(colidinch  (SELECTColid fromsyscolumnsWHERE(ID=a.ID) and(Name=( a.name) ))))) and(xtype= 'PK'))>0  Then '√' Else "' End) Primary key 1,b.name type 1,ColumnProperty(A.id,a.name,'PRECISION') aslength 1,1  asAA into#temp1 fromsyscolumns a Left JoinSystypes b onA.xtype=B.xusertypeInner Joinsysobjects D ona.ID=D.id andD.xtype='U'  andD.name<>'dtproperties'  Left Joinsyscomments E onA.cdefault=e.id Left JoinSys.extended_properties g ona.ID=g.major_id andA.colid=g.minor_id Left JoinSys.extended_properties F onD.id=F.class andf.minor_id=0whereB.name is  not NULL  and(SELECT COUNT(1) fromMaster.. sysdatabases SysaWHERESysa.name=@DataName1)>0    andD.name=@TableName1--where d.name= ' table to query '--if you only query the specified table, add this conditionOrder  byA.name,a.id,a.colorderSELECT  ( Case  whenA.colorder=1  ThenD.nameElse NULL End) Table Name 2, a.name field Name 2, ( Case  when(SELECT Count(*) fromsysobjectsWHERE(Nameinch(SELECTName fromsysindexesWHERE(ID=a.ID) and(indidinch  (SELECTIndid fromSysindexkeysWHERE(ID=a.ID) and(colidinch  (SELECTColid fromsyscolumnsWHERE(ID=a.ID) and(Name=( a.name) ))))) and(xtype= 'PK'))>0  Then '√' Else "' End) Primary key 2,b.name type 2,ColumnProperty(A.id,a.name,'PRECISION') aslength 2,1  asAA into#temp2 fromsyscolumns a Left JoinSystypes b onA.xtype=B.xusertypeInner Joinsysobjects D ona.ID=D.id andD.xtype='U'  andD.name<>'dtproperties'  Left Joinsyscomments E onA.cdefault=e.id Left JoinSys.extended_properties g ona.ID=g.major_id andA.colid=g.minor_id Left JoinSys.extended_properties F onD.id=F.class andf.minor_id=0whereB.name is  not NULL  and(SELECT COUNT(1) fromMaster.. sysdatabases SysaWHERESysa.name=@DataName2)>0    andD.name=@TableName2--where d.name= ' table to query '--if you only query the specified table, add this conditionOrder  byA.name,a.id,a.colorderSELECT *  from (    SELECT  COUNT(1) asTable 1 Number of fields from#temp1) A, (SELECT  COUNT(1) asTable 2 Number of fields from#temp2) bSELECT COUNT(1) from#temp1 A Left JOIN#temp2 b onA. Field Name 1=B. Field Name 2WHEREA. Length 1!=ISNULL(b. Length 2,"')ORA. Type 1!=ISNULL(b. Type 2,"');SELECT COUNT(1) from#temp1 A Right JOIN#temp2 b onA. Field Name 1=B. Field Name 2WHERE ISNULL(A. Length 1,"')!=B. Length 2orB. Type 2!=ISNULL(A. Type 1,"');SELECTA. Field name 1,b. field name 2,a. Type 1,b. Type 2,a. length 1,b. Length 2 into#temp_1 from#temp1 A Left JOIN#temp2 b onA. Field Name 1=B. Field Name 2SELECTA. Field name 1,b. field name 2,a. Type 1,b. Type 2,a. length 1,b. Length 2 into#temp_2 from#temp1 A Right JOIN#temp2 b onA. Field Name 1=B. Field Name 2SELECT *  from#temp_1;SELECT *  from#temp_2;DROP TABLE#temp1;DROP TABLE#temp2;DROP TABLE#temp_1;DROP TABLE#temp_2;GO 

Querying SQL Server for the same table structure differences