--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]