SSMS: How to find columns with similar names, but different data types

Question

How to find columns with similar names, but different data types within the predefined scope of tables?

The Goal: to verify columns metadata within a project.

For example: column deal_id is used in 3 tables.

I want to be sure that deal_id metadata is everywhere the same.

To avoid following:

• table1.deal_id is nvarchar(50)
• table2.deal_id is varchar(50)
• table3.deal_id is nvarchar(60)

Answer 1

Please try this (query returns the information_schema.columns for the columns with the same name but different metadata ):

;with
    t1 as       -- get tables with the same column names
    (   select *
            , count(*) over(partition by column_name) clm_cnt
        from information_schema.columns        
        where TABLE_NAME like '%'  -- specify scope of your tables  here, otherwise it will look within entire DB
    ),
    t2 as  -- get flags - if amount of distinct types/lengths/etc are >1
    (   select *
            ,cast(clm_cnt - count(*) over(partition by column_name,COLUMN_DEFAULT          ) as bit) is_diff_COLUMN_DEFAULT            
            ,cast(clm_cnt - count(*) over(partition by column_name,IS_NULLABLE             ) as bit) is_diff_IS_NULLABLE             
            ,cast(clm_cnt - count(*) over(partition by column_name,DATA_TYPE               ) as bit) is_diff_DATA_TYPE               
            ,cast(clm_cnt - count(*) over(partition by column_name,CHARACTER_MAXIMUM_LENGTH) as bit) is_diff_CHARACTER_MAXIMUM_LENGTH
            ,cast(clm_cnt - count(*) over(partition by column_name,CHARACTER_OCTET_LENGTH  ) as bit) is_diff_CHARACTER_OCTET_LENGTH  
            ,cast(clm_cnt - count(*) over(partition by column_name,NUMERIC_PRECISION       ) as bit) is_diff_NUMERIC_PRECISION       
            ,cast(clm_cnt - count(*) over(partition by column_name,NUMERIC_PRECISION_RADIX ) as bit) is_diff_NUMERIC_PRECISION_RADIX 
            ,cast(clm_cnt - count(*) over(partition by column_name,NUMERIC_SCALE           ) as bit) is_diff_NUMERIC_SCALE           
            ,cast(clm_cnt - count(*) over(partition by column_name,DATETIME_PRECISION      ) as bit) is_diff_DATETIME_PRECISION      
            ,cast(clm_cnt - count(*) over(partition by column_name,COLLATION_NAME          ) as bit) is_diff_COLLATION_NAME          
        from t1
        where clm_cnt>1
    )
select * 
from t2 
where 1=1
and 
(   0
    --| is_diff_COLUMN_DEFAULT   
    --| is_diff_IS_NULLABLE       
    | is_diff_DATA_TYPE                    
    | is_diff_CHARACTER_MAXIMUM_LENGTH 
    | is_diff_CHARACTER_OCTET_LENGTH   
    | is_diff_NUMERIC_PRECISION        
    | is_diff_NUMERIC_PRECISION_RADIX  
    | is_diff_NUMERIC_SCALE            
    | is_diff_DATETIME_PRECISION       
    | is_diff_COLLATION_NAME          
)>0