{"id":602,"date":"2021-08-31T20:37:38","date_gmt":"2021-08-31T19:37:38","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=602"},"modified":"2021-09-05T09:08:45","modified_gmt":"2021-09-05T08:08:45","slug":"how-i-compared-1000s-of-databases-schemas-at-once","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2021\/08\/31\/how-i-compared-1000s-of-databases-schemas-at-once\/","title":{"rendered":"C\u00f3mo comparar el esquema de miles de bases de datos"},"content":{"rendered":"\nCompare database schemas can be tricky, here I show you how I managed to compare thousands of databases’ schemas at once \n\n\n\n

Working with databases is always fun and you never know what can be knocking on your door, even when you think everything is under control.<\/p>\n\n\n\n

As the title of this post states, today I will explain in detail how I managed to compare thousands of databases schemas at once, just by following the popular and well-known saying \u00abHow to Eat an Elephant<\/em>\u00ab<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Background<\/h2>\n\n\n\n

When it comes to choosing the right architecture for your data, there is not \u00abone size fits all\u00bb solution, it depends on multiple factors and nowadays with more and more offerings from cloud providers, the number of possibilities has grown dramatically.<\/p>\n\n\n\n

This wasn’t the case not so long ago, when people used to put everything<\/strong><\/em> in the database, just because … just because.<\/p>\n\n\n\n

In those good old times, there were still choices to make, like for instance, to have one big database for your application or multiple databases sharing the same schema.<\/p>\n\n\n\n

In multi-tenant environments, having one database per customer was<\/s> is very common, because it brings some benefits from the security and sometimes from the performance point of view.<\/p>\n\n\n\n

But also it comes with the challenge of keeping all databases schemas in sync.<\/p>\n\n\n\n

Schema Comparison Tools<\/h2>\n\n\n\n

There are tools that can compare two databases in great detail, but the caveat I found, it is that they can only<\/strong> compare two<\/strong> databases, so while they’ll be fine to compare DEV<=>PROD or QA<=>PROD, they’re not a good fit to compare and find the differences in, literally, thousands of databases<\/p>\n\n\n\n

There it’s where I was, so after some time trying to postpone the inevitable, it was time to roll up my sleeves and get the job done.<\/p>\n\n\n\n

My Approach<\/h2>\n\n\n\n

My approach for a task like this is very simple, just like the saying <\/p>\n\n\n\n

\u00abHow to Eat an Elephant<\/em><\/strong>\u00ab, One Byte at a Time<\/strong><\/em><\/p><\/blockquote>\n\n\n\n

But in my case, one [database] object at time \ud83d\ude42<\/p>\n\n\n\n

First thing was to write the script that generates each object’s definition to be able to compare each other. And for simplicity, I also generate a hash for each object to compare, rather than the text.<\/p>\n\n\n\n

In my case (yours might be different) I wanted to compare all SQL modules (SP’s, UDF’s, TVF’s, triggers, views), all tables and user defined type tables, that gives me a good starting point.<\/p>\n\n\n\n

Thankfully I have written most parts of it at some point in the last 10 years, so I headed to my GitHub repo<\/a> and started to grab pieces from here and there.<\/p>\n\n\n\n

For the purpose of this post, I restored the lightest copy of AdventureWorks 100 times on 2 instances, plus one more that will be the source of truth.
They are named [AdventureWorksLT_001] to [AdventureWorksLT_200] and the master just [AdventureWorksLT]<\/p>\n\n\n\n

The TSQL script to generate all the objects I’m interested on, it looks like this:<\/p>\n\n\n\n

SET NOCOUNT ON;\nDECLARE @object_name\tnvarchar(257) = '' \nDECLARE @sqlstring\t\tnvarchar(MAX)\nDECLARE @dbname\t\t\tsysname = 'AdventureWorksLT'\n\nSET @dbname = NULLIF(@dbname, '')\nSET @object_name = NULLIF(@object_name, '')\n\nIF OBJECT_ID('tempdb..#output') IS NOT NULL DROP TABLE #output\n\nCREATE TABLE #output  (\n    database_name sysname\t\t\tNOT NULL, \n    object_type nvarchar(60)\t\tNOT NULL, \n    object_name nvarchar(257)\t\tNOT NULL, \n    object_definition nvarchar(MAX) NOT NULL\n)\n\nDECLARE dbs CURSOR FORWARD_ONLY READ_ONLY FAST_FORWARD LOCAL\n\tFOR SELECT name \n            FROM sys.databases \n \t\t    WHERE database_id &gt; 4 \n\t\t        AND state = 0\n\t\t        AND (name = @dbname OR (@dbname IS NULL AND name LIKE 'AdventureWorksLT_%'))\n\nOPEN dbs \nFETCH NEXT FROM dbs INTO @dbname \n\nWHILE @@FETCH_STATUS = 0 BEGIN \n\tSET @sqlstring\t= N'USE ' + QUOTENAME(@dbname) + N'\n\nSET NOCOUNT ON;\n\nSELECT DB_NAME() COLLATE DATABASE_DEFAULT AS [database_name] \n\t\t, o.type_desc COLLATE DATABASE_DEFAULT AS [object_type]\n\t\t, OBJECT_SCHEMA_NAME(o.object_id) + ''.'' + OBJECT_NAME(o.object_id) COLLATE DATABASE_DEFAULT AS [object_name] \n\t\t, LTRIM(RTRIM(OBJECT_DEFINITION(o.OBJECT_ID))) AS [object_definition]\n\tFROM sys.objects AS o\n\tWHERE o.type IN (''TR'',''FN'',''P'',''TF'',''V'')\n        AND (o.object_id = OBJECT_ID(@object_name) OR @object_name IS NULL)\n\nUNION ALL\n\nSELECT DB_NAME() COLLATE DATABASE_DEFAULT AS [database_name]\n\t\t, type_desc COLLATE DATABASE_DEFAULT AS [type_desc] \n\t\t, OBJECT_SCHEMA_NAME(object_id) + ''.'' + name COLLATE DATABASE_DEFAULT AS object_name\n\t\t, ''CREATE TABLE '' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + ''.'' + QUOTENAME(name) + ''('' + CHAR(10) +\n\t\t\t(STUFF(\n\t\t\t\t\t(SELECT '', '' + QUOTENAME(c.name) + '' '' + t.name + \n\t\t\t\t\t\t\tCASE WHEN t.name IN (''char'', ''nchar'', ''binay'', ''varchar'', ''nvarchar'', ''varbinay'')\n\t\t\t\t\t\t\t\tTHEN ''('' + CONVERT(SYSNAME, c.max_length) + '')'' \n\t\t\t\t\t\t\t\tELSE ''''\n\t\t\t\t\t\t\tEND +\n\t\t\t\t\t\t\tCASE WHEN c.is_identity = 1 THEN '' IDENTITY'' ELSE '''' END +\n\t\t\t\t\t\t\tISNULL('' COLLATE '' + c.collation_name, '''') +\n\t\t\t\t\t\t\tCASE WHEN c.is_nullable = 0 THEN '' NOT'' ELSE '''' END + '' NULL'' + CHAR(10)\n\t\t\t\t\t\tFROM sys.columns AS c\n\t\t\t\t\t\t\tINNER JOIN sys.types AS t\n\t\t\t\t\t\t\t\tON t.user_type_id = c.user_type_id\n\t\t\t\t\t\tWHERE c.object_id = o.object_id\n\t\t\t\t\t\tORDER BY column_id\n\t\t\t\t\t\tFOR XML PATH('''')), 1, 2, '''')) + '')'' COLLATE DATABASE_DEFAULT AS object_definition\n\tFROM sys.objects AS o\n\tWHERE type IN (''U'')\n\t\tAND OBJECTPROPERTY(object_id, ''IsMsShipped'') = 0\n\t\tAND (object_id = OBJECT_ID(@object_name) OR @object_name IS NULL)\n\nUNION ALL\n\nSELECT DB_NAME() COLLATE DATABASE_DEFAULT AS database_name \n\t\t, o.type_desc COLLATE DATABASE_DEFAULT\n\t\t, SCHEMA_NAME(tt.schema_id) + ''.'' + tt.name COLLATE DATABASE_DEFAULT AS object_name\n\t\t, ''CREATE TYPE '' + QUOTENAME(SCHEMA_NAME(tt.schema_id)) + ''.'' + QUOTENAME(tt.name) + '' AS TABLE ('' + CHAR(10) +\n\t\t\t(STUFF(\n\t\t\t\t\t(SELECT '', '' + QUOTENAME(c.name) + '' '' + t.name + \n\t\t\t\t\t\t\tCASE WHEN t.name IN (''char'', ''nchar'', ''binay'', ''varchar'', ''nvarchar'', ''varbinay'')\n\t\t\t\t\t\t\t\tTHEN ''('' + CONVERT(SYSNAME, c.max_length) + '')'' \n\t\t\t\t\t\t\t\tELSE ''''\n\t\t\t\t\t\t\tEND +\n\t\t\t\t\t\t\tCASE WHEN c.is_identity = 1 THEN '' IDENTITY'' ELSE '''' END +\n\t\t\t\t\t\t\tISNULL('' COLLATE '' + c.collation_name, '''') +\n\t\t\t\t\t\t\tCASE WHEN c.is_nullable = 0 THEN '' NOT'' ELSE '''' END + '' NULL'' + CHAR(10)\n\t\t\t\t\t\tFROM sys.columns AS c\n\t\t\t\t\t\t\tINNER JOIN sys.types AS t\n\t\t\t\t\t\t\t\tON t.user_type_id = c.user_type_id\n\t\t\t\t\t\tWHERE c.object_id = tt.type_table_object_id\n\t\t\t\t\t\tORDER BY column_id\n\t\t\t\t\t\tFOR XML PATH('''')), 1, 2, '''')) + '')'' COLLATE DATABASE_DEFAULT AS object_definition\n\tFROM sys.objects AS o\n\t\tINNER JOIN sys.table_types AS tt\n\t\t\tON tt.type_table_object_id = o.object_id\n\tWHERE (tt.type_table_object_id = OBJECT_ID(@object_name) OR @object_name IS NULL)\n' \n\t\n\tINSERT INTO #output (database_name, object_type, object_name, object_definition)\n\tEXECUTE sys.sp_executesql \n        @stmt = @sqlstring\n        , @params = N'@object_name NVARCHAR(257)'\n        , @object_name = @object_name;\n\n\tFETCH NEXT FROM dbs INTO @dbname \nEND \nCLOSE dbs; \nDEALLOCATE dbs; \n\nSELECT @@SERVERNAME AS server_name\n\t\t, database_name\n        , object_type\n\t\t, object_name\n\t\t, object_definition\n        , CONVERT(varchar(100), HASHBYTES('md5', LTRIM(RTRIM(object_definition))),1) AS object_hash \t\t    \n\tFROM #output\n    ORDER BY object_type\n        , object_name\n<\/pre><\/p>\n\n\n\n
Executed in my local instance, the result looks like this:<\/p>\n\n\n\n
\"\"<\/a><\/figure>\n\n\n\n
AS mentioned earlier, each object has got a hash, which later will help to compare them all.<\/p>\n\n\n\n
Time to Eat the Elephant<\/h2>\n\n\n\n
So far so good, I can use this query to target one or multiple databases at once in the same server, but I do have two instances and still row by row comparison will be a killer, so we need something extra, some sort of super power to go through all of them in on go… or just use PowerShell!<\/p>\n\n\n\n
The approach again is One Object at a Time<\/em><\/strong>, why? because once we have compared one object against all databases, we have the whole picture for that object and we know if it has drifted, where has drifted and the most important, we can start figuring out what to do with it. <\/p>\n\n\n\n
I would need:<\/p>\n\n\n\n