-- MigrateMyDatabase (migratemydatabase.eu): structure of this SQL Server database, for the free migration scan. -- READ-ONLY. It reads the system catalog only: no table data, no code text. It creates one temporary table -- (#mmdb, in tempdb) that disappears when you close the query window. -- -- How to run (SQL Server 2008 or newer, Azure SQL Database, Azure SQL Managed Instance, Amazon RDS, Cloud SQL): -- SSMS / Azure Data Studio: open this file, select the database of your application, press Execute. -- Then click the result grid, press Ctrl+A, Ctrl+C and paste it into the page (or save it as a file). -- sqlcmd: sqlcmd -S -d -E -i mmdb-sqlserver.sql -o mmdb-result.txt -y 0 -- -- Permissions: a login with VIEW DEFINITION on the database (db_owner works). Without it, code objects are -- counted but not checked. Reading SQL Agent jobs needs access to msdb; without it the jobs are reported as unknown. -- -- What is collected: database version, edition, size and settings; schemas, tables (row counts and sizes), -- columns (types, defaults), indexes, keys, check constraints; for views, procedures, functions and triggers -- only the number of lines and which SQL features they use (keywords such as MERGE or CURSOR), never the code. SET NOCOUNT ON; SET QUOTED_IDENTIFIER ON; IF OBJECT_ID('tempdb..#mmdb') IS NOT NULL DROP TABLE #mmdb; CREATE TABLE #mmdb (n int IDENTITY(1, 1) PRIMARY KEY, k varchar(16) NOT NULL, f1 nvarchar(max), f2 nvarchar(max), f3 nvarchar(max), f4 nvarchar(max), f5 nvarchar(max), f6 nvarchar(max), f7 nvarchar(max), f8 nvarchar(max), f9 nvarchar(max), f10 nvarchar(max), f11 nvarchar(max), f12 nvarchar(max), f13 nvarchar(max), f14 nvarchar(max), f15 nvarchar(max), f16 nvarchar(max)); DECLARE @major int = CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') AS varchar(32)), 4) AS int); DECLARE @sql nvarchar(max); -- ---------- meta and database ---------- INSERT #mmdb (k, f1, f2, f3) VALUES ('meta', 'sqlserver', '1', CONVERT(varchar(19), GETUTCDATE(), 126)); INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14) SELECT 'db', DB_NAME(), LEFT(@@VERSION, CHARINDEX(CHAR(10), @@VERSION + CHAR(10)) - 1), CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(64)), CAST(SERVERPROPERTY('ProductLevel') AS nvarchar(64)), CAST(SERVERPROPERTY('Edition') AS nvarchar(128)), CAST(SERVERPROPERTY('EngineEdition') AS nvarchar(8)), CAST(d.compatibility_level AS nvarchar(8)), d.collation_name, CAST(SERVERPROPERTY('Collation') AS nvarchar(128)), (SELECT CAST(CAST(SUM(CAST(size AS bigint)) * 8 / 1024.0 AS decimal(18, 1)) AS nvarchar(32)) FROM sys.database_files WHERE type = 0), (SELECT CAST(CAST(SUM(CAST(size AS bigint)) * 8 / 1024.0 AS decimal(18, 1)) AS nvarchar(32)) FROM sys.database_files WHERE type = 1), d.recovery_model_desc, CAST(d.is_encrypted AS nvarchar(1)), (SELECT CAST(COUNT(*) AS nvarchar(8)) FROM sys.databases WHERE database_id > 4) FROM sys.databases d WHERE d.database_id = DB_ID(); -- ---------- schemas ---------- INSERT #mmdb (k, f1, f2) SELECT 'schema', s.name, CAST((SELECT COUNT(*) FROM sys.objects o WHERE o.schema_id = s.schema_id AND o.is_ms_shipped = 0) AS nvarchar(16)) FROM sys.schemas s WHERE s.schema_id < 16384 AND s.name NOT IN ('sys', 'INFORMATION_SCHEMA', 'guest') AND EXISTS (SELECT 1 FROM sys.objects o WHERE o.schema_id = s.schema_id AND o.is_ms_shipped = 0); -- ---------- tables (columns that only exist in newer versions are read through dynamic SQL) ---------- SET @sql = N' INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7, f8) SELECT ''table'', s.name, t.name, CAST((SELECT SUM(p.rows) FROM sys.partitions p WHERE p.object_id = t.object_id AND p.index_id IN (0, 1)) AS nvarchar(32)), CAST(CAST((SELECT SUM(CAST(a.total_pages AS bigint)) FROM sys.partitions p JOIN sys.allocation_units a ON a.container_id = CASE WHEN a.type IN (1, 3) THEN p.hobt_id ELSE p.partition_id END WHERE p.object_id = t.object_id) * 8 / 1024.0 AS decimal(18, 1)) AS nvarchar(32)), CASE WHEN EXISTS (SELECT 1 FROM sys.key_constraints kc WHERE kc.parent_object_id = t.object_id AND kc.type = ''PK'') THEN ''1'' ELSE ''0'' END, CAST(' + CASE WHEN COL_LENGTH('sys.tables', 'is_memory_optimized') IS NOT NULL THEN N't.is_memory_optimized' ELSE N'0' END + N' AS nvarchar(1)), CAST(' + CASE WHEN COL_LENGTH('sys.tables', 'temporal_type') IS NOT NULL THEN N't.temporal_type' ELSE N'0' END + N' AS nvarchar(1)), CAST(' + CASE WHEN COL_LENGTH('sys.tables', 'is_filetable') IS NOT NULL THEN N't.is_filetable' ELSE N'0' END + N' AS nvarchar(1)) FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.is_ms_shipped = 0' + CASE WHEN COL_LENGTH('sys.tables', 'is_external') IS NOT NULL THEN N' AND t.is_external = 0' ELSE N'' END + N';'; EXEC sp_executesql @sql; -- ---------- columns ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16) SELECT 'col', s.name, o.name, c.name, CAST(c.column_id AS nvarchar(8)), TYPE_NAME(c.user_type_id), CASE WHEN ty.is_user_defined = 1 THEN ISNULL(TYPE_NAME(c.system_type_id), '') ELSE '' END, CAST(c.max_length AS nvarchar(8)), CAST(c.precision AS nvarchar(8)), CAST(c.scale AS nvarchar(8)), CAST(c.is_nullable AS nvarchar(1)), CAST(c.is_identity AS nvarchar(1)), CASE WHEN cc.object_id IS NULL THEN '0' WHEN cc.is_persisted = 1 THEN '2' ELSE '1' END, LEFT(dc.definition, 200), ISNULL(c.collation_name, ''), CAST(c.is_sparse AS nvarchar(1)) + CAST(c.is_filestream AS nvarchar(1)) + CAST(c.is_rowguidcol AS nvarchar(1)), CASE WHEN o.type = 'V' THEN 'V' ELSE 'U' END FROM sys.columns c JOIN sys.objects o ON o.object_id = c.object_id AND o.type = 'U' AND o.is_ms_shipped = 0 JOIN sys.schemas s ON s.schema_id = o.schema_id JOIN sys.types ty ON ty.user_type_id = c.user_type_id LEFT JOIN sys.computed_columns cc ON cc.object_id = c.object_id AND cc.column_id = c.column_id LEFT JOIN sys.default_constraints dc ON dc.object_id = c.default_object_id; -- ---------- indexes (heaps excluded) ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10) SELECT 'index', s.name, t.name, i.name, i.type_desc, CAST(i.is_unique AS nvarchar(1)), CAST(i.is_primary_key AS nvarchar(1)), CAST(i.is_unique_constraint AS nvarchar(1)), CAST(i.has_filter AS nvarchar(1)), STUFF((SELECT ',' + c.name + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE '' END FROM sys.index_columns ic JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0 AND ic.key_ordinal > 0 ORDER BY ic.key_ordinal FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, ''), CAST((SELECT COUNT(*) FROM sys.index_columns ic WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1) AS nvarchar(8)) FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id AND t.is_ms_shipped = 0 JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE i.type > 0 AND i.is_hypothetical = 0; -- ---------- foreign keys ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11) SELECT 'fk', s.name, OBJECT_NAME(f.parent_object_id), f.name, OBJECT_SCHEMA_NAME(f.referenced_object_id), OBJECT_NAME(f.referenced_object_id), f.delete_referential_action_desc, f.update_referential_action_desc, CAST(f.is_disabled AS nvarchar(1)), CAST(f.is_not_trusted AS nvarchar(1)), STUFF((SELECT ',' + c.name FROM sys.foreign_key_columns fc JOIN sys.columns c ON c.object_id = fc.parent_object_id AND c.column_id = fc.parent_column_id WHERE fc.constraint_object_id = f.object_id ORDER BY fc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, ''), STUFF((SELECT ',' + c.name FROM sys.foreign_key_columns fc JOIN sys.columns c ON c.object_id = fc.referenced_object_id AND c.column_id = fc.referenced_column_id WHERE fc.constraint_object_id = f.object_id ORDER BY fc.constraint_column_id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') FROM sys.foreign_keys f JOIN sys.schemas s ON s.schema_id = f.schema_id WHERE f.is_ms_shipped = 0; -- ---------- check constraints ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5) SELECT 'check', s.name, OBJECT_NAME(ck.parent_object_id), ck.name, LEFT(ck.definition, 400), CAST(ck.is_disabled AS nvarchar(1)) FROM sys.check_constraints ck JOIN sys.schemas s ON s.schema_id = ck.schema_id WHERE ck.is_ms_shipped = 0; -- ---------- code objects: line count and feature keywords only, never the code ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5, f6, f7) SELECT 'module', s.name, o.name, RTRIM(o.type), CASE WHEN m.definition IS NULL THEN '' ELSE CAST(LEN(m.definition) - LEN(REPLACE(m.definition, CHAR(10), '')) + 1 AS nvarchar(16)) END, ISNULL(OBJECT_NAME(o.parent_object_id), ''), CASE WHEN tr.is_instead_of_trigger = 1 THEN '1' ELSE '0' END, CASE WHEN m.definition IS NULL THEN 'NODEF,' ELSE '' END + CASE WHEN x.d LIKE '%DECLARE % CURSOR%' OR x.d LIKE '%CURSOR FOR%' OR x.d LIKE '%CURSOR LOCAL%' OR x.d LIKE '%CURSOR FAST[_]FORWARD%' THEN 'CURSOR,' ELSE '' END + CASE WHEN x.d LIKE '%MERGE%USING%' THEN 'MERGE,' ELSE '' END + CASE WHEN x.d LIKE '%OUTPUT%INSERTED.%' OR x.d LIKE '%OUTPUT%DELETED.%' THEN 'OUTPUT,' ELSE '' END + CASE WHEN x.d LIKE '%sp[_]executesql%' OR x.d LIKE '%EXEC(%' OR x.d LIKE '%EXEC (%' OR x.d LIKE '%EXECUTE(%' OR x.d LIKE '%EXECUTE (%' THEN 'DYNSQL,' ELSE '' END + CASE WHEN x.d LIKE '%xp[_]cmdshell%' THEN 'XPCMDSHELL,' ELSE '' END + CASE WHEN x.d LIKE '%sp[_]send[_]dbmail%' THEN 'DBMAIL,' ELSE '' END + CASE WHEN x.d LIKE '%OPENQUERY%' OR x.d LIKE '%OPENDATASOURCE%' THEN 'OPENQUERY,' ELSE '' END + CASE WHEN x.d LIKE '%OPENROWSET%' OR x.d LIKE '%BULK INSERT%' THEN 'FILEACCESS,' ELSE '' END + CASE WHEN x.d LIKE '%INTO #%' OR x.d LIKE '%TABLE #%' THEN 'TEMPTABLE,' ELSE '' END + CASE WHEN x.d LIKE '%DECLARE @% TABLE%' THEN 'TABLEVAR,' ELSE '' END + CASE WHEN x.d LIKE '%@@IDENTITY%' OR x.d LIKE '%SCOPE[_]IDENTITY%' OR x.d LIKE '%IDENT[_]CURRENT%' THEN 'IDENTITYFN,' ELSE '' END + CASE WHEN x.d LIKE '%BEGIN TRY%' THEN 'TRYCATCH,' ELSE '' END + CASE WHEN x.d LIKE '%RAISERROR%' THEN 'RAISERROR,' ELSE '' END + CASE WHEN x.d LIKE '%BEGIN TRAN%' OR x.d LIKE '%SAVE TRAN%' THEN 'TRANSACTION,' ELSE '' END + CASE WHEN x.d LIKE '%SELECT TOP%' OR x.d LIKE '%SELECT DISTINCT TOP%' OR x.d LIKE '%DELETE TOP%' OR x.d LIKE '%UPDATE TOP%' THEN 'TOP,' ELSE '' END + CASE WHEN x.d LIKE '%NOLOCK%' OR x.d LIKE '%READUNCOMMITTED%' THEN 'NOLOCK,' ELSE '' END + CASE WHEN x.d LIKE '%FOR XML%' OR x.d LIKE '%OPENXML%' OR x.d LIKE '%.nodes(%' OR x.d LIKE '%.value(%' THEN 'XML,' ELSE '' END + CASE WHEN x.d LIKE '%FOR JSON%' OR x.d LIKE '%OPENJSON%' OR x.d LIKE '%JSON[_]VALUE%' THEN 'JSON,' ELSE '' END + CASE WHEN x.d LIKE '%PIVOT%' THEN 'PIVOT,' ELSE '' END + CASE WHEN x.d LIKE '%CROSS APPLY%' OR x.d LIKE '%OUTER APPLY%' THEN 'APPLY,' ELSE '' END + CASE WHEN x.d LIKE '%GOTO %' THEN 'GOTO,' ELSE '' END + CASE WHEN x.d LIKE '%WAITFOR%' THEN 'WAITFOR,' ELSE '' END + CASE WHEN x.d LIKE '%NEXT VALUE FOR%' THEN 'SEQUENCE,' ELSE '' END + CASE WHEN x.d LIKE '%SET ROWCOUNT%' THEN 'ROWCOUNT,' ELSE '' END + CASE WHEN x.d LIKE '%*=%' OR x.d LIKE '%=*%' THEN 'OLDJOIN,' ELSE '' END + CASE WHEN x.d LIKE '%CONTEXT[_]INFO%' OR x.d LIKE '%SESSION[_]CONTEXT%' THEN 'CONTEXT,' ELSE '' END + CASE WHEN x.d LIKE '%sys.%' OR x.d LIKE '%sysobjects%' OR x.d LIKE '%INFORMATION[_]SCHEMA%' THEN 'CATALOG,' ELSE '' END + CASE WHEN x.d LIKE '%HASHBYTES%' OR x.d LIKE '%ENCRYPTBY%' OR x.d LIKE '%DECRYPTBY%' THEN 'CRYPTO,' ELSE '' END + CASE WHEN x.d LIKE '%WITH ENCRYPTION%' THEN 'ENCRYPTED,' ELSE '' END + CASE WHEN x.d LIKE '%sp[_]OACreate%' OR x.d LIKE '%sp[_]OAMethod%' THEN 'OLEAUTO,' ELSE '' END + CASE WHEN x.d LIKE '%BEGIN DISTRIBUTED TRAN%' THEN 'MSDTC,' ELSE '' END + CASE WHEN x.d LIKE '%sp[_]configure%' OR x.d LIKE '%RECONFIGURE%' OR x.d LIKE '%DBCC TRACEON%' THEN 'SERVERCONFIG,' ELSE '' END + CASE WHEN x.d LIKE '%sp[_]addmessage%' THEN 'ADDMESSAGE,' ELSE '' END + CASE WHEN x.d LIKE '%' + CHAR(10) + 'USE %' OR x.d LIKE '%;USE %' OR x.d LIKE '%; USE %' OR x.d LIKE 'USE %' THEN 'USEDB,' ELSE '' END + CASE WHEN x.d LIKE '%BACKUP DATABASE%' OR x.d LIKE '%BACKUP LOG%' OR x.d LIKE '%RESTORE DATABASE%' THEN 'BACKUPCMD,' ELSE '' END + CASE WHEN x.d LIKE '%msdb.dbo.sp[_]start[_]job%' OR x.d LIKE '%sp[_]start[_]job%' THEN 'STARTJOB,' ELSE '' END FROM sys.objects o JOIN sys.schemas s ON s.schema_id = o.schema_id LEFT JOIN sys.sql_modules m ON m.object_id = o.object_id LEFT JOIN sys.triggers tr ON tr.object_id = o.object_id CROSS APPLY (SELECT m.definition COLLATE Latin1_General_CI_AS AS d) x WHERE o.is_ms_shipped = 0 AND o.type IN ('V', 'P', 'FN', 'IF', 'TF', 'TR', 'PC', 'FS', 'FT', 'TA', 'AF'); -- database-level (DDL) triggers INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'ddl_trigger', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.triggers WHERE parent_class = 0 AND is_ms_shipped = 0; -- ---------- references to other databases and linked servers ---------- INSERT #mmdb (k, f1, f2, f3, f4, f5, f6) SELECT DISTINCT 'xref', OBJECT_SCHEMA_NAME(d.referencing_id), OBJECT_NAME(d.referencing_id), ISNULL(d.referenced_server_name, ''), ISNULL(d.referenced_database_name, ''), ISNULL(d.referenced_schema_name, ''), ISNULL(d.referenced_entity_name, '') FROM sys.sql_expression_dependencies d WHERE (d.referenced_database_name IS NOT NULL AND d.referenced_database_name <> DB_NAME()) OR d.referenced_server_name IS NOT NULL; -- ---------- features (counts) ---------- INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'clr_assembly', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.assemblies WHERE is_user_defined = 1; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'service_broker_queue', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.service_queues WHERE is_ms_shipped = 0; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'filestream_file', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.database_files WHERE type = 2; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'fulltext_index', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.fulltext_indexes; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'synonym', CAST(COUNT(*) AS nvarchar(16)), CAST(SUM(CASE WHEN PARSENAME(base_object_name, 3) IS NOT NULL AND PARSENAME(base_object_name, 3) <> DB_NAME() THEN 1 ELSE 0 END) AS nvarchar(16)) FROM sys.synonyms; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'partition_scheme', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.partition_schemes; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'alias_type', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 0 AND is_assembly_type = 0; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'clr_type', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.types WHERE is_user_defined = 1 AND is_assembly_type = 1; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'table_type', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.types WHERE is_user_defined = 1 AND is_table_type = 1; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'xml_schema_collection', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.xml_schema_collections WHERE schema_id <> 4; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'change_tracking_table', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.change_tracking_tables; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'windows_principal', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.database_principals WHERE type IN ('U', 'G') AND principal_id > 4; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'sql_user', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.database_principals WHERE type = 'S' AND principal_id > 4 AND name NOT IN ('guest', 'INFORMATION_SCHEMA', 'sys'); INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'linked_server', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.servers WHERE is_linked = 1; -- server-level features that only exist on your own server (not every managed service has them) BEGIN TRY INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'server_trigger', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.server_triggers WHERE is_ms_shipped = 0; END TRY BEGIN CATCH INSERT #mmdb (k, f1, f2, f3) VALUES ('feature', 'server_trigger', '-1', ''); END CATCH; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'ssis_catalog', CASE WHEN DB_ID('SSISDB') IS NULL THEN '0' ELSE '1' END, ''; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'reporting_services', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.databases WHERE name LIKE 'ReportServer%' AND name NOT LIKE '%TempDB'; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'custom_message', CAST(COUNT(*) AS nvarchar(16)), '' FROM sys.messages WHERE message_id > 50000 AND language_id = 1033; INSERT #mmdb (k, f1, f2, f3) SELECT 'feature', 'db_chaining', CAST(is_db_chaining_on AS nvarchar(1)), '' FROM sys.databases WHERE database_id = DB_ID(); IF COL_LENGTH('sys.tables', 'is_remote_data_archive_enabled') IS NOT NULL EXEC sp_executesql N'INSERT #mmdb (k, f1, f2, f3) SELECT ''feature'', ''stretch_table'', CAST(COUNT(*) AS nvarchar(16)), '''' FROM sys.tables WHERE is_remote_data_archive_enabled = 1;'; -- containment exists from SQL Server 2012 on, so the statement is built dynamically SET @sql = N' INSERT #mmdb (k, f1, f2, f3) SELECT ''feature'', ''database_flag'', CAST(is_broker_enabled AS nvarchar(1)) + CAST(is_cdc_enabled AS nvarchar(1)) + CAST(is_published AS nvarchar(1)) + CAST(is_merge_published AS nvarchar(1)) + CAST(is_subscribed AS nvarchar(1)) + CAST(is_trustworthy_on AS nvarchar(1)) + CAST(is_read_committed_snapshot_on AS nvarchar(1)) + ' + CASE WHEN COL_LENGTH('sys.databases', 'containment') IS NOT NULL THEN N'CAST(CASE WHEN containment = 0 THEN 0 ELSE 1 END AS nvarchar(1))' ELSE N'''0''' END + N', ''broker,cdc,published,merge,subscribed,trustworthy,rcsi,contained'' FROM sys.databases WHERE database_id = DB_ID();'; EXEC sp_executesql @sql; IF @major >= 11 EXEC sp_executesql N'INSERT #mmdb (k, f1, f2, f3) SELECT ''feature'', ''sequence'', CAST(COUNT(*) AS nvarchar(16)), '''' FROM sys.sequences;'; IF @major >= 13 AND COL_LENGTH('sys.tables', 'is_external') IS NOT NULL EXEC sp_executesql N'INSERT #mmdb (k, f1, f2, f3) SELECT ''feature'', ''external_table'', CAST(COUNT(*) AS nvarchar(16)), '''' FROM sys.tables WHERE is_external = 1;'; -- SQL Agent jobs with a step in this database (needs msdb access; -1 = could not be read) BEGIN TRY EXEC sp_executesql N'INSERT #mmdb (k, f1, f2, f3) SELECT ''feature'', ''agent_job'', CAST(COUNT(DISTINCT j.job_id) AS nvarchar(16)), '''' FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps st ON st.job_id = j.job_id WHERE st.database_name = DB_NAME();'; END TRY BEGIN CATCH INSERT #mmdb (k, f1, f2, f3) VALUES ('feature', 'agent_job', '-1', ''); END CATCH; INSERT #mmdb (k, f1) VALUES ('end', CAST((SELECT COUNT(*) FROM #mmdb) AS nvarchar(16))); -- ---------- result: one line per row, fields separated by | ---------- SELECT 'MMDB1|' + k + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f1, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f2, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f3, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f4, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f5, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f6, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f7, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f8, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f9, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f10, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f11, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f12, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f13, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f14, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f15, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') + '|' + REPLACE(REPLACE(REPLACE(ISNULL(f16, ''), N'|', N'/'), NCHAR(13), N' '), NCHAR(10), N' ') AS mmdb FROM #mmdb ORDER BY n; DROP TABLE #mmdb;