Skip to content

Instantly share code, notes, and snippets.

@smeans
Created April 17, 2024 04:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save smeans/aa3c7766dc49b691291d3b5e7c2113f5 to your computer and use it in GitHub Desktop.
Save smeans/aa3c7766dc49b691291d3b5e7c2113f5 to your computer and use it in GitHub Desktop.
MS SQL stored proc to diff two objects
CREATE OR ALTER PROCEDURE DiffObjects
@o1 nvarchar(max),
@o2 nvarchar(max)
AS
BEGIN
SET NOCOUNT ON;
if OBJECT_ID(@o1) is null
begin
select concat(@o1, ' does not exist') message
return
end
if OBJECT_ID(@o2) is null
begin
select concat(@o2, ' does not exist') message
return
end
select distinct iif(c2.object_id is null, OBJECT_NAME(c1.object_id), 'both') object, c1.name, c1.column_id, c2.column_id,
iif(c1.system_type_id <> c2.system_type_id, 'X', '') as system_type_id, iif(c1.user_type_id <> c2.user_type_id, 'X', '') as user_type_id, iif(c1.max_length <> c2.max_length, 'X', '') as max_length, iif(c1.precision <> c2.precision, 'X', '') as precision, iif(c1.scale <> c2.scale, 'X', '') as scale, iif(c1.collation_name <> c2.collation_name, 'X', '') as collation_name, iif(c1.is_nullable <> c2.is_nullable, 'X', '') as is_nullable, iif(c1.is_ansi_padded <> c2.is_ansi_padded, 'X', '') as is_ansi_padded, iif(c1.is_rowguidcol <> c2.is_rowguidcol, 'X', '') as is_rowguidcol, iif(c1.is_identity <> c2.is_identity, 'X', '') as is_identity, iif(c1.is_computed <> c2.is_computed, 'X', '') as is_computed, iif(c1.is_filestream <> c2.is_filestream, 'X', '') as is_filestream, iif(c1.is_replicated <> c2.is_replicated, 'X', '') as is_replicated, iif(c1.is_non_sql_subscribed <> c2.is_non_sql_subscribed, 'X', '') as is_non_sql_subscribed, iif(c1.is_merge_published <> c2.is_merge_published, 'X', '') as is_merge_published, iif(c1.is_dts_replicated <> c2.is_dts_replicated, 'X', '') as is_dts_replicated, iif(c1.is_xml_document <> c2.is_xml_document, 'X', '') as is_xml_document, iif(c1.xml_collection_id <> c2.xml_collection_id, 'X', '') as xml_collection_id, iif(c1.default_object_id <> c2.default_object_id, 'X', '') as default_object_id, iif(c1.rule_object_id <> c2.rule_object_id, 'X', '') as rule_object_id, iif(c1.is_sparse <> c2.is_sparse, 'X', '') as is_sparse, iif(c1.is_column_set <> c2.is_column_set, 'X', '') as is_column_set, iif(c1.generated_always_type <> c2.generated_always_type, 'X', '') as generated_always_type, iif(c1.generated_always_type_desc <> c2.generated_always_type_desc, 'X', '') as generated_always_type_desc, iif(c1.encryption_type <> c2.encryption_type, 'X', '') as encryption_type, iif(c1.encryption_type_desc <> c2.encryption_type_desc, 'X', '') as encryption_type_desc, iif(c1.encryption_algorithm_name <> c2.encryption_algorithm_name, 'X', '') as encryption_algorithm_name, iif(c1.column_encryption_key_id <> c2.column_encryption_key_id, 'X', '') as column_encryption_key_id, iif(c1.column_encryption_key_database_name <> c2.column_encryption_key_database_name, 'X', '') as column_encryption_key_database_name, iif(c1.is_hidden <> c2.is_hidden, 'X', '') as is_hidden, iif(c1.is_masked <> c2.is_masked, 'X', '') as is_masked, iif(c1.graph_type <> c2.graph_type, 'X', '') as graph_type, iif(c1.graph_type_desc <> c2.graph_type_desc, 'X', '') as graph_type_desc
from sys.columns c1
left outer join sys.columns c2 on c2.object_id = iif(c1.object_id = OBJECT_ID(@o1), OBJECT_ID(@o2), OBJECT_ID(@o1))
and c1.name = c2.name
where c1.object_id in (OBJECT_ID(@o1), OBJECT_ID(@o2)) and (c2.object_id is null or (
c1.column_id <> c2.column_id or c1.system_type_id <> c2.system_type_id or c1.user_type_id <> c2.user_type_id or c1.max_length <> c2.max_length or c1.precision <> c2.precision or c1.scale <> c2.scale or c1.collation_name <> c2.collation_name or c1.is_nullable <> c2.is_nullable or c1.is_ansi_padded <> c2.is_ansi_padded or c1.is_rowguidcol <> c2.is_rowguidcol or c1.is_identity <> c2.is_identity or c1.is_computed <> c2.is_computed or c1.is_filestream <> c2.is_filestream or c1.is_replicated <> c2.is_replicated or c1.is_non_sql_subscribed <> c2.is_non_sql_subscribed or c1.is_merge_published <> c2.is_merge_published or c1.is_dts_replicated <> c2.is_dts_replicated or c1.is_xml_document <> c2.is_xml_document or c1.xml_collection_id <> c2.xml_collection_id or c1.default_object_id <> c2.default_object_id or c1.rule_object_id <> c2.rule_object_id or c1.is_sparse <> c2.is_sparse or c1.is_column_set <> c2.is_column_set or c1.generated_always_type <> c2.generated_always_type or c1.generated_always_type_desc <> c2.generated_always_type_desc or c1.encryption_type <> c2.encryption_type or c1.encryption_type_desc <> c2.encryption_type_desc or c1.encryption_algorithm_name <> c2.encryption_algorithm_name or c1.column_encryption_key_id <> c2.column_encryption_key_id or c1.column_encryption_key_database_name <> c2.column_encryption_key_database_name or c1.is_hidden <> c2.is_hidden or c1.is_masked <> c2.is_masked or c1.graph_type <> c2.graph_type or c1.graph_type_desc <> c2.graph_type_desc
))
order by c1.column_id
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment