Now that the SQL management packs rely heavily on DLLs it’s not easy to determine what the workflows are doing or how they do it. These are the queries used for many of the workflows within this management pack.

-------------------------------------------------------
--DISCOVERIES
-------------------------------------------------------

--GetDBUserPolicies
USE msdb
 
SELECT            								
	p.[name] AS [PolicyName]
	,CASE 
		WHEN pc.[name] IS NOT NULL THEN pc.[name] 
		ELSE N'<Default>' 
		END AS [PolicyCategory]								
	,p.[description] AS [Description]
	,c.[name] AS [Condition]
	,CAST(LEFT(c.facet, 4000) AS NVARCHAR(4000)) AS [Facet]
	,p.help_link AS [HelpLink]    								
	--,p.help_text AS [HelpText]
	,p.is_enabled AS [IsEnabled]			
	--,cf.obj_name AS [ObjectName]
	,CAST(CASE WHEN pc.[name] LIKE '%error%' THEN 1 ELSE 0 END AS BIT)	AS IsErrorPolicy	
FROM syspolicy_policies p
	JOIN syspolicy_conditions c ON c.condition_id = p.condition_id
	JOIN syspolicy_object_sets os ON os.object_set_id = p.object_set_id
	JOIN syspolicy_target_sets ts ON ts.object_set_id = os.object_set_id
	JOIN syspolicy_target_set_levels tsl ON tsl.target_set_id = ts.target_set_id
	JOIN syspolicy_policy_categories pc ON pc.policy_category_id = p.policy_category_id
	--LEFT OUTER JOIN syspolicy_conditions cf ON cf.condition_id = tsl.condition_id
WHERE c.facet = 'Database' AND p.is_system = 0;

USE master

SELECT 
	[name] AS [DatabaseName]
FROM sys.databases d
WHERE source_database_id IS NULL AND
	NOT ([name] IN ('master','model','msdb','tempdb') OR is_distributor = 1);



--AgentJobDiscovery
DECLARE @xml xml = @excludeList
;WITH excludeList
AS
(
	SELECT T.db.value('(./@name)[1]', 'nvarchar(max)') AS jobName
	FROM @xml.nodes('ExcludeList/AgentJob') AS T(db)
)
SELECT sjv.job_id,
		sjv.name,
		sjv.originating_server,
		sjv.description,
		category = ISNULL(sc.name, FORMATMESSAGE(14205)),
		owner = msdb.dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid),
		sjv.enabled
FROM msdb.dbo.sysjobs_view sjv
		JOIN msdb.dbo.syscategories sc 
			ON (sjv.category_id = sc.category_id)
		left JOIN excludeList el
			ON ( el.jobName LIKE '%*%' and sjv.name = el.jobName)
			or sjv.name LIKE REPLACE(REPLACE(REPLACE(REPLACE(el.jobName, '%', '[%]'), '[', '[[]'), '_', '[_]'), '*', '%')
		where el.jobName is null
ORDER BY sjv.job_id;



--DatabaseList
DECLARE @xml xml = @excludeList
;WITH excludeList
AS
(
	SELECT T.db.value('(./@name)[1]', 'nvarchar(max)') AS dbName
	FROM @xml.nodes('ExcludeList/Database') AS T(db)
)
SELECT 
	d.[name] AS [DatabaseName]
	,d.collation_name AS [Collation]
	,d.recovery_model_desc AS [RecoveryModel]
	,DATABASEPROPERTYEX(d.NAME, 'Updateability') AS [Updateability]
	,d.user_access_desc UserAccess
	,(SELECT (CASE WHEN MAX(mf.growth) > 0 THEN 1 ELSE 0 END) FROM sys.master_files mf WHERE d.database_id = mf.database_id AND mf.type = 1) as LogAutogrow
	,(SELECT (CASE WHEN MAX(mf.growth) > 0 THEN 1 ELSE 0 END) FROM sys.master_files mf WHERE d.database_id = mf.database_id AND mf.type = 0) as DatabaseAutogrow
	,SUSER_SNAME(d.owner_sid) as OwnerName
FROM sys.databases d
LEFT JOIN excludeList el
	ON (el.dbName NOT LIKE '%*%' AND DB_ID(el.dbName) = d.database_id)
	OR d.[name] LIKE REPLACE(REPLACE(REPLACE(REPLACE(el.dbName, '%', '[%]'), '[', '[[]'), '_', '[_]'), '*', '%')
WHERE d.source_database_id IS NULL
AND el.dbName IS NULL




--AgentDiscovery
SELECT 
	servicename AS [AgentName]
	,service_account AS [AccountName]
FROM sys.dm_server_services 
WHERE [filename] LIKE '"%\Binn\SQLAGENT.EXE" -i%'



--DBEngineDiscovery
SET NOCOUNT ON;
     
SELECT 
	SERVERPROPERTY('MachineName') AS [MachineName], 
	ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS [InstanceName],
	SERVERPROPERTY('Edition') AS [Edition], 
	SERVERPROPERTY('ProductVersion') AS [Version], 
	CASE WHEN ISNULL(SERVERPROPERTY('IsClustered'), 0) > 0 THEN 'True' ELSE 'False' END AS [IsClustered],
	(SELECT msglangid FROM sys.syslanguages WHERE [langid] = @@LANGID) AS [Language],
	@@MICROSOFTVERSION / 0x01000000 AS [ProductMajorVersion],
	DEFAULT_DOMAIN()[NetBIOSDomainName]



--GetResourcePoolsCD
DECLARE @dbName SYSNAME, @defaultResourcePoolName NVARCHAR(128) = NULL, @sql NVARCHAR(2000), @errorStatusCheck INT, @allowedRoConnection BIT, @requiredRwConnection BIT, @readWriteDbCount INT = 0;

/*
Predefined pools:
 1 - Internal pool - cannot be used for Hekaton
 2 - Default pool
*/
IF @isReadOnlyMode = 1
BEGIN
	WITH dbrp_cte AS(
		SELECT DISTINCT 
			resource_pool_id
		FROM sys.databases
	)
	SELECT 
		rp.[name] AS [Name]
		,pool_id AS PoolId
		,min_cpu_percent AS MinCpuPercent
		,max_cpu_percent AS MaxCpuPercent
		,cap_cpu_percent AS CapCpupercent
		,min_memory_percent AS MinMemoryPercent 
		,max_memory_percent AS MaxMemoryPercent
		,CAST(CASE
			WHEN pool_id = 1 THEN 0
			WHEN pool_id = 2 THEN 1
			WHEN dbrp_cte.resource_pool_id IS NOT NULL THEN 1
			ELSE 0
		END AS BIT) AS [ManagesInMemoryOLTP]
	FROM master.sys.dm_resource_governor_resource_pools rp
	LEFT JOIN dbrp_cte ON dbrp_cte.resource_pool_id = rp.pool_id
END;

SELECT TOP 1 @defaultResourcePoolName = [name] FROM master.sys.dm_resource_governor_resource_pools WHERE pool_id = 2

IF @defaultResourcePoolName IS NOT NULL
BEGIN
	IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;
	CREATE TABLE #ResultTable (
		[DatabaseName] SYSNAME
		,[ResourcePoolName] NVARCHAR(128)
		,[AccessStatus] INT
		,[ErrorNumber] INT
		,[ErrorMessage] NVARCHAR(MAX)
		);

	IF OBJECT_ID ('tempdb.dbo.#ReadWriteDbs') IS NOT NULL DROP TABLE #ReadWriteDbs;
	CREATE TABLE #ReadWriteDbs(
		[DatabaseName] SYSNAME
	);

	IF @isReadOnlyMode = 1
	BEGIN
		INSERT INTO #ResultTable
		SELECT
			dbs.[name]
			,rp.[name]
			,1
			,0
			,NULL
		FROM master.sys.databases dbs
			INNER JOIN master.sys.dm_resource_governor_resource_pools rp on dbs.resource_pool_id = rp.pool_id
		WHERE dbs.source_database_id IS NULL
			AND dbs.is_distributor = 0 
			AND dbs.[name] NOT IN ('master', 'msdb', 'model', 'tempdb');
		
		DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
		SELECT 
			d.name,
			CASE 
				WHEN [role] IS NULL OR ([role] = 1 AND primary_role_allow_connections = 2) OR ([role] = 2 AND secondary_role_allow_connections > 1) THEN 1
				ELSE 0
			END AS allowedRoConnection,
			CASE
				WHEN ([role] = 1 AND primary_role_allow_connections = 3) THEN 1
				ELSE 0
			END AS requiredRwConnection
		FROM sys.databases d
			LEFT JOIN sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
			LEFT JOIN sys.availability_replicas ar ON d.replica_id = ar.replica_id
		WHERE d.source_database_id IS NULL
			AND d.resource_pool_id IS NULL
			AND d.is_distributor = 0 
			AND d.[name] NOT IN ('master', 'msdb', 'model', 'tempdb');
	END
	ELSE
	BEGIN
		DECLARE @xmlDoc XML = cast(@readWriteDbs AS XML)
		DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
		SELECT
			xc.value('./n[1]', 'sysname') AS DatabaseName,
			0 AS allowedRoConnection,
			1 AS requiredRwConnection
		FROM @xmlDoc.nodes('/*/d') AS xt(xc)
	END;

	OPEN dbCursor;
	FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;

	WHILE @@Fetch_Status = 0 BEGIN
		IF @isReadOnlyMode = 1 AND @requiredRwConnection = 0 AND @allowedRoConnection = 0 BEGIN
			INSERT INTO #ResultTable([DatabaseName],[AccessStatus],[ErrorNumber]) VALUES (@dbName, 0, -50001)
		END
		ELSE IF @isReadOnlyMode = 1 AND @requiredRwConnection = 1
		BEGIN
			SET @readWriteDbCount = @readWriteDbCount + 1;
			INSERT INTO #ReadWriteDbs(DatabaseName) VALUES(@dbName);
		END
		ELSE BEGIN
			SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
			SELECT TOP 1
				DB_NAME() AS [DatabaseName]
				,@defaultResourcePoolName AS [ResourcePoolName]
				,1 AS [AccessStatus]
				,0 as [ErrorNumber]
				,NULL as [ErrorMessage] 
			FROM sys.filegroups fg
			--JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
			WHERE fg.[type] COLLATE DATABASE_DEFAULT = ''FX'';';
	
			BEGIN TRY
				INSERT INTO #ResultTable
				EXEC sp_executesql @sql, N'@defaultResourcePoolName NVARCHAR(128)', @defaultResourcePoolName = @defaultResourcePoolName
			END TRY
			BEGIN CATCH
				SET @errorStatusCheck = HAS_DBACCESS(@dbName);
				INSERT INTO #ResultTable
				SELECT
					@dbName AS [DatabaseName]
					,@defaultResourcePoolName AS [ResourcePoolName]
					,@errorStatusCheck AS [AccessStatus]
					,ERROR_NUMBER() as [ErrorNumber]
					,ERROR_MESSAGE() as [ErrorMessage];
			END CATCH;
		END; --ELSE END
		FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;
	END;

	CLOSE dbCursor;
	DEALLOCATE dbCursor;

	SELECT * FROM #ResultTable;

	IF @isReadOnlyMode = 1
	BEGIN
		SELECT @readWriteDbCount AS ReadWriteDbCount;
		IF @readWriteDbCount > 0
			SELECT CONVERT(NVARCHAR(MAX),(SELECT DatabaseName AS n
			FROM #ReadWriteDbs
			FOR XML PATH('d'), ROOT('dbNames'))) AS ReadWriteDbs;
	END;

	DROP TABLE #ResultTable;
	DROP TABLE #ReadWriteDbs;
END;



--GetDBFileGroupsCD
--declare @isReadOnlyMode BIT, @readWriteDbs nvarchar(max);

DECLARE @dbName sysname, @sql nvarchar(2000), @errorStatusCheck INT, @allowedRoConnection BIT, @requiredRwConnection BIT, @readWriteDbCount INT = 0;

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;

CREATE TABLE #ResultTable (
	[DatabaseName] SYSNAME
	,[Status] INT
	,[Error] INT
	,[ErrorMessage]  NVARCHAR(MAX)
	,[GroupName] SYSNAME
	,[ReadOnly] BIT
	,[GroupType] NVARCHAR(2)
	,[TypeDesc] NVARCHAR(60)
);

IF OBJECT_ID ('tempdb.dbo.#ReadWriteDbs') IS NOT NULL DROP TABLE #ReadWriteDbs;

CREATE TABLE #ReadWriteDbs(
	[DatabaseName] SYSNAME
);

IF @isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.name,
		CASE 
			WHEN [role] IS NULL OR ([role] = 1 AND primary_role_allow_connections = 2) OR ([role] = 2 AND secondary_role_allow_connections > 1) THEN 1
			ELSE 0
		END AS allowedRoConnection,
		CASE
			WHEN ([role] = 1 AND primary_role_allow_connections = 3) THEN 1
			ELSE 0
		END AS requiredRwConnection
	FROM sys.databases d
		LEFT JOIN sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		LEFT JOIN sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL;
END
ELSE
BEGIN
	DECLARE @xmlDoc XML = cast(@readWriteDbs AS XML)
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
	SELECT
		xc.value('./n[1]', 'sysname') AS DatabaseName,
		0 AS allowedRoConnection,
		1 AS requiredRwConnection
	FROM @xmlDoc.nodes('/*/d') AS xt(xc)
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;

WHILE @@Fetch_Status = 0 
BEGIN
	IF @isReadOnlyMode = 1 AND @requiredRwConnection = 0 AND @allowedRoConnection = 0 BEGIN
		INSERT INTO #ResultTable([DatabaseName],[Status],[Error],[GroupName]) VALUES (@dbName, 0, -50001, N'')
	END
	ELSE IF @isReadOnlyMode = 1 AND @requiredRwConnection = 1
	BEGIN
		SET @readWriteDbCount = @readWriteDbCount + 1;
		INSERT INTO #ReadWriteDbs(DatabaseName) VALUES(@dbName);
	END
	ELSE BEGIN
		SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
		SELECT 
			DB_NAME() AS [DatabaseName]
			,1 as Status
			,0 as [Error]
			,N'''' as [ErrorMessage] 
			,fg.[name] AS [GroupName]
			,fg.is_read_only AS [ReadOnly]
			,fg.[type] AS [GroupType]
			,fg.type_desc AS [TypeDesc]
		FROM sys.filegroups fg;';
	
		BEGIN TRY
			INSERT INTO #ResultTable
			EXEC sp_executesql @sql
		END TRY
		BEGIN CATCH
			SET @errorStatusCheck = HAS_DBACCESS(@dbName);
			INSERT INTO #ResultTable
			SELECT
				@dbName AS [DatabaseName]
				,CASE 
					WHEN @errorStatusCheck IS NULL THEN 0
					ELSE @errorStatusCheck
				END AS [Status]
				,ERROR_NUMBER() as [Error]
				,ERROR_MESSAGE() as [ErrorMessage] 
				,N'' AS [GroupName]
				,0 AS [ReadOnly]
				,N'' AS [GroupType]
				,N'' AS [TypeDesc];
		END CATCH;
	END; --ELSE END
	FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;
END

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable;

IF @isReadOnlyMode = 1
BEGIN
	SELECT @readWriteDbCount AS ReadWriteDbCount;
	IF @readWriteDbCount > 0
		SELECT CONVERT(NVARCHAR(MAX),(SELECT DatabaseName AS n
		FROM #ReadWriteDbs
		FOR XML PATH('d'), ROOT('dbNames'))) AS ReadWriteDbs;
END;

DROP TABLE #ResultTable;
DROP TABLE #ReadWriteDbs;



--GetDBFilesCD
--declare @isReadOnlyMode BIT, @readWriteDbs nvarchar(max);

DECLARE @dbName sysname, @sql nvarchar(2000), @errorStatusCheck INT, @allowedRoConnection BIT, @requiredRwConnection BIT, @readWriteDbCount INT = 0;

IF OBJECT_ID ('tempdb.dbo.#ResultTable') IS NOT NULL DROP TABLE #ResultTable;

CREATE TABLE #ResultTable (
	[DatabaseName] SYSNAME
	,[Status] INT
	,[Error] INT
	,[ErrorMessage]  NVARCHAR(max)
	,[FileName] SYSNAME
	,[FilePath] NVARCHAR(260)
	,[FileGroupName] NVARCHAR(128)
	,[FileType] NVARCHAR(10)
	);

IF OBJECT_ID ('tempdb.dbo.#ReadWriteDbs') IS NOT NULL DROP TABLE #ReadWriteDbs;

CREATE TABLE #ReadWriteDbs(
	[DatabaseName] SYSNAME
);

IF @isReadOnlyMode = 1
BEGIN
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR 
	SELECT 
		d.name,
		CASE 
			WHEN [role] IS NULL OR ([role] = 1 AND primary_role_allow_connections = 2) OR ([role] = 2 AND secondary_role_allow_connections > 1) THEN 1
			ELSE 0
		END AS allowedRoConnection,
		CASE
			WHEN ([role] = 1 AND primary_role_allow_connections = 3) THEN 1
			ELSE 0
		END AS requiredRwConnection
	FROM sys.databases d
		LEFT JOIN sys.dm_hadr_availability_replica_states dhars ON d.replica_id = dhars.replica_id
		LEFT JOIN sys.availability_replicas ar ON d.replica_id = ar.replica_id
	WHERE d.source_database_id IS NULL;
END
ELSE
BEGIN
	DECLARE @xmlDoc XML = cast(@readWriteDbs AS XML)
	DECLARE dbCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY FOR
	SELECT
		xc.value('./n[1]', 'sysname') AS DatabaseName,
		0 AS allowedRoConnection,
		1 AS requiredRwConnection
	FROM @xmlDoc.nodes('/*/d') AS xt(xc)
END;

OPEN dbCursor;
FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;

WHILE @@Fetch_Status = 0 
BEGIN
	IF @isReadOnlyMode = 1 AND @requiredRwConnection = 0 AND @allowedRoConnection = 0 BEGIN
		INSERT INTO #ResultTable([DatabaseName],[Status],[Error],[FileName]) VALUES (@dbName, 0, -50001, N'')
	END
	ELSE IF @isReadOnlyMode = 1 AND @requiredRwConnection = 1
	BEGIN
		SET @readWriteDbCount = @readWriteDbCount + 1;
		INSERT INTO #ReadWriteDbs(DatabaseName) VALUES(@dbName);
	END
	ELSE BEGIN
		SET @sql = N'USE '+ QUOTENAME(@dbName ,'"') + N';
		SELECT 
			DB_NAME() AS [DatabaseName]
			,DB_ID() as Status
			,0 as [Error]
			,N'''' as [ErrorMessage] 
			,f.[name] AS [FileName]
			,f.physical_name AS [FilePath]
			,fg.[name] as [FileGroupName]
			,CASE f.[type]
				WHEN 0 THEN N''DBFile''
				WHEN 1 THEN N''LogFile''
				WHEN 2 THEN N''Container''
			END AS FileType
		FROM sys.master_files f
		LEFT JOIN sys.filegroups fg ON f.data_space_id = fg.data_space_id
		WHERE f.database_id = DB_ID() AND (fg.[type] IS NULL OR fg.[type] COLLATE DATABASE_DEFAULT IN (N''FG'', N''FX''));';
	
		BEGIN TRY
			INSERT INTO #ResultTable		
			EXEC sp_executesql @sql;
		END TRY
		BEGIN CATCH
			SET @errorStatusCheck = HAS_DBACCESS(@dbName);
			INSERT INTO #ResultTable
			SELECT 
				@dbName as [DatabaseName]
				,CASE 
					WHEN @errorStatusCheck IS NULL THEN 0
					ELSE @errorStatusCheck
				END AS [Status]
				,ERROR_NUMBER() AS [Error]
				,ERROR_MESSAGE() AS [ErrorMessage] 
				,N'' AS [FileName]
				,N'' AS [FilePath]
				,N'' AS [FileGroupName]
				,N'' AS [FileType];
		END CATCH;
	END; --ELSE END
	FETCH NEXT FROM dbCursor INTO @dbName, @allowedRoConnection, @requiredRwConnection;
END;

CLOSE dbCursor;
DEALLOCATE dbCursor;

SELECT * FROM #ResultTable;

IF @isReadOnlyMode = 1
BEGIN
	SELECT @readWriteDbCount AS ReadWriteDbCount;
	IF @readWriteDbCount > 0
		SELECT CONVERT(NVARCHAR(MAX),(SELECT DatabaseName AS n
		FROM #ReadWriteDbs
		FOR XML PATH('d'), ROOT('dbNames'))) AS ReadWriteDbs;
END;

DROP TABLE #ResultTable;
DROP TABLE #ReadWriteDbs;



--AlwaysOnSeedDiscovery
SELECT SERVERPROPERTY('IsHadrEnabled') AS [IsHadrEnabled]