Attribute Type differences

Because we all know that maintaining two on-premise environments (e.g. dev & production) can be a pain, sometimes checking certain entities before solution import can be a time saver!

To check the data type differences between the attributes of the same entity, on two different organizations, you can use the following SQL statement:

-- USAGE:
-- EXEC CheckDiffs 'SourceOrganization_MSCRM', 'LinkedServerPath.TargetOrganization_MSCRM', 'account'
-- EXEC CheckDiffs 'SourceOrganization_MSCRM', 'LinkedServerPath.TargetOrganization_MSCRM'
-- Important: The @TargetDatabaseName parameter must be passed including the LinkedServer path as prefix (if the target database is on a different SQL Server than the source database)
-- Last parameter is optional (you can check attribute type differences on a certain entity, or the whole database)
create procedure CheckDiffs
@SourceDatabaseName nvarchar(100), @TargetDatabaseName nvarchar(100), @EntityName nvarchar(100) = NULL
as
BEGIN
	
	declare @sql nvarchar(max)
	
	SET @sql = 'SELECT srcE.Name Entity, srcA.Name Attribute, srcT.Description SourceType, trgT.Description TargetType FROM ' + @SourceDatabaseName + '.MetadataSchema.Attribute srcA JOIN ' + @TargetDatabaseName + '.MetadataSchema.Attribute trgA on srcA.Name = trgA.Name JOIN ' + @SourceDatabaseName + '.MetadataSchema.Entity srcE ON srcA.EntityId = srcE.EntityId JOIN ' + @TargetDatabaseName + '.MetadataSchema.Entity trgE ON trgA.EntityId = trgE.EntityId JOIN ' + @SourceDatabaseName + '.MetadataSchema.AttributeTypes srcT ON srcA.AttributeTypeId = srcT.AttributeTypeId JOIN ' + @TargetDatabaseName + '.MetadataSchema.AttributeTypes trgT ON trgA.AttributeTypeId = trgT.AttributeTypeId WHERE srcE.Name = trgE.Name and srcA.AttributeTypeId <> trgA.AttributeTypeId'

	IF 	@EntityName IS NOT NULL
	BEGIN
		 SET @sql = @sql + ' and srcE.Name = ''' + @EntityName + ''''
	END
	
	--print @sql
	
	exec(@sql)	
END

By running the second example of the CheckDiffs procedure, the result should be: