Microsoft – SQL Server – Transact – Compare Object Columns

 

 

Prelude

While trying to join two database objects (tables, views), I find myself getting errors with mismatched column types.

 

Error Messages

 

Image:

SquareMetersToDataTypeInt

 

Textual:

Msg 245, Level 16, State 1, Line 232
Conversion failed when converting the nvarchar value ‘square-meters’ to data type int.

 

Code

 

use master
go

if object_id('dbo.sp_CompareObjectColumns') is null
begin

    exec('create procedure dbo.sp_CompareObjectColumns as select 1/0 as [shell]' )

end
go

alter procedure dbo.sp_CompareObjectColumns 
(

      @objectName1         sysname
    , @objectName2         sysname
    , @compareType         sysname = 'columnType'
    , @listDifferencesOnly bit = 1

)
as
begin

    ;with cteObjectColumn1
    as
    (
        select   
                  TOP 100 PERCENT
                  schema_name(tblO.schema_id) as schemaName
                , tblO.name                   as objectName
                , tblC.column_id              as columnID
                , tblC.name                   as columnName
                , tblT.name                   as columnType  

        from   sys.objects tblO

                inner join sys.columns tblC

                    on tblO.object_id = tblC.object_id

                inner join sys.types tblT

                    on tblC.user_type_id = tblT.user_type_id

        where tblO.object_id = object_id(@objectName1)


        order by
                  schema_name(tblO.schema_id) 
                , tblO.name                   
                , tblC.column_id              

    )

    , cteObjectColumn2
    as
    (
        select 
                    TOP 100 PERCENT
                  schema_name(tblO.schema_id) as schemaName
                , tblO.name                   as objectName
                , tblC.column_id              as columnID
                , tblC.name                   as columnName
                , tblT.name                   as columnType  

        from   sys.objects tblO

                inner join sys.columns tblC

                    on tblO.object_id = tblC.object_id

                inner join sys.types tblT

                    on tblC.user_type_id = tblT.user_type_id

        where tblO.object_id = object_id(@objectName2)
        
         order by
                  schema_name(tblO.schema_id) 
                , tblO.name                   
                , tblC.column_id              

    )
    
        
    select 

              tblOC1.columnID
            , tblOC1.columnName
            , tblOC1.columnType

            , tblOC2.columnID
            , tblOC2.columnName
            , tblOC2.columnType

    from   cteObjectColumn1 tblOC1
   
                left outer join cteObjectColumn2 tblOC2

                    on

                        (

                                (@compareType = 'columnType')
                            and (tblOC1.columnID = tblOC2.columnID)

                        )

    where

            (

                   (@listDifferencesOnly = 0)

                or (

                            (@listDifferencesOnly = 1)
                        and (tblOC1.columnType != tblOC2.columnType)  

                   ) 


            )

end
go

exec sp_ms_marksystemobject 'dbo.sp_CompareObjectColumns'
go

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s