Created
April 17, 2024 04:02
-
-
Save smeans/aa3c7766dc49b691291d3b5e7c2113f5 to your computer and use it in GitHub Desktop.
MS SQL stored proc to diff two objects
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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