Technical: Microsoft – SQL Server – Compare Database Object Schema / Specifically Column Types

Introduction

Another Day, Another broken build courtesy of Daniel.

Don’t understand this ORM Life; everything has to be so nicely synced – The Database Objects, the edmx file, etc.

Broken Object

So what did I break today!  A database view.  What broke – One column allows null, the other does not.

Stored Procedure

Here is a quick stored procedure that allows me to quickly compare the columns of two entities and make sure that they match.


use [master]
go

if OBJECT_ID('dbo.sp_CompareObjectSchema') is null
begin
	exec('create procedure dbo.sp_CompareObjectSchema as select 1/0 as [undefined]')
end
go

alter procedure dbo.sp_CompareObjectSchema
(
	  @objectName1 sysname
	, @objectName2 sysname
	, @listallAtributes bit = 0
)
as

	select 

		 tblColumn1.name as [columnName]

		, case
			when (tblColumn2.name is not null) 
			 then 'Yes'
			else tblColumn2.name + 'No'
			 end as 'Presence'

		,
			case
				when (tblColumn1.column_id = tblColumn2.column_id)
					then 'Yes'					

			        when (
				      (tblColumn1.column_id != tblColumn2.column_id) 				     )	
					then '1 - ' 
					 + cast(tblColumn1.column_id as sysname)
					 + ' and 2 '
					 + cast(tblColumn2.column_id as sysname)								

				when (
				        (tblColumn2.column_id is null) 						     )	
					then '2 - ' 
						+ ' Not Present '

					else 'Test failed'

				end as 'Column ID'

		,
		   case
			when (tblColumn1.max_length = tblColumn2.max_length)
				then 'Yes'					

			when (
				    (tblColumn1.column_id != tblColumn2.column_id) 					
		  	   )	
				then '1 - ' 
			  	  + cast(tblColumn1.max_length as sysname)
				  + ' and 2 '
				  + cast(tblColumn2.max_length as sysname)								

			else 'Test failed'

		   end as 'ColumnMaxLength'

		,
			case
			    when (tblColumn1.user_type_id = tblColumn2.user_type_id)
				then 'Yes'					

			   when (
			         (tblColumn1.user_type_id != tblColumn2.user_type_id) 				
				 )	
			     then '1 - ' 
			       + cast(TYPE_NAME(tblColumn1.user_type_id) as sysname)
			       + ' and 2 '
			       + cast(TYPE_NAME(tblColumn2.user_type_id) as sysname)								

			   else 'Test failed'

			end as 'ColumnType'

		,
			case
			   when (tblColumn1.is_identity = tblColumn2.is_identity)
				then 'Yes'					

			   when (
			           (tblColumn1.is_identity != tblColumn2.is_identity) 				and (tblColumn1.is_identity = 1) 												
			       )	
				 then '1 - Yes, and 2 - No'

			  when (
			           (tblColumn1.is_identity != tblColumn2.is_identity) 				
				and (tblColumn2.is_identity = 0) 												
			      )	
				then '1 - Yes, and 2 - No'

			   else 'Test failed'

			end as 'Identity'

		,
		     case
				when (tblColumn1.is_nullable = tblColumn2.is_nullable) 
					then 'Yes'					

				when (
						(tblColumn1.is_nullable != tblColumn2.is_nullable) 					
					and (tblColumn1.is_nullable = 0) 												
					  )	
					then '1 is Nullable, and 2 is Not Nullable'

				when (
						   (tblColumn1.is_nullable != tblColumn2.is_nullable) 					
					and (tblColumn2.is_nullable = 0) 												
					)	
					   then '1 is Not Nullable, and 2 is Nullable'

				else 'Failed'

		     end as 'Nullability'

	from   sys.columns tblColumn1

			left outer join sys.columns tblColumn2

				on  tblColumn1.name = tblColumn2.name	
				and tblColumn2.object_id = object_id(@objectName2)			

	where  tblColumn1.object_id = object_id(@objectName1)

	and
		(

		    --column is not in object 2
		    (tblColumn2.name is null)

		    --Identity Test
		    or (tblColumn1.is_identity != tblColumn2.is_identity)

		   --UserType Test
		   or (tblColumn1.user_type_id != tblColumn2.user_type_id)

		   --Nullable differences
		   or (tblColumn1.is_nullable != tblColumn2.is_nullable)	

		   or (@listallAtributes = 1)			

		   )		

	order by
		     tblColumn1.column_id

go

EXEC sys.sp_MS_marksystemobject 'dbo.sp_CompareObjectSchema'
go

/*

	declare @objectName1 sysname
	declare @objectName2 sysname

	set @objectName1 = 'dbo.v_sales'

	set @objectName2 = 'dbo.v_Sales_New'

	use [dbName]

	exec dbo.sp_CompareObjectSchema
		  @objectName1 = @objectName1
		, @objectName2 = @objectName2

*/

Listening…

Took my brother’s big car out this weekend and listened in to Sirius. You know I Looked for the Country Music stations and what a joy I lucked into.

Here is King George singing about the things that matters:

Best Day of my LIFE
http://www.youtube.com/watch?v=0-d3enqUwUc

2 thoughts on “Technical: Microsoft – SQL Server – Compare Database Object Schema / Specifically Column Types

    • Akoslukacs:

      I really need to do other posts on security, personalization, error handling, usage profiling, Client Scripting, and deploying to IIS, etc.

      And, the list goes on.

      I went back and looked at things, and in my quick test when I intentionally miss-typed an Object name (Table or Stored Procedure name), I get an explicit error :

      Resource Not Found for the segment [ object name ]

      If I specify a missing argument when invoking Stored Procedure that is in fact silently eaten up.

      For MS SQL Server, I will suggest using SQL Server Profiler and filtering on Application Name = EntityFramework during quick debug sessions.

      Kind one, thanks for your participation and sharing, as well.

      Daniel

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