/*

Równoczesne zapytania do grupy serwerów

*/
SELECT * FROM sys.database_files;

/*

Sprawdzenie polityki w konsoli SSMS

*/

USE AdventureWorks2008
GO
CREATE TABLE dbo.Tabela (i int);
GO

USE AdventureWorks2008
GO
CREATE TABLE Production.Tabela (i int);
GO

USE tempdb
GO
CREATE TABLE dbo.Tabela (i int);
GO



/*

Programowe użycie i kontrolowanie polityk 

*/
use msdb
GO

SELECT 
 POL.name        as [Nazwa polityki]
 ,PCO.name       as [Nazwa warunku]
,POS.object_set_name as [Facet]
,[start_date] as [Data uruchomienia]
,[end_date] as [Data zakonczenia]
,PEH.result       as [Wynik]
FROM [syspolicy_policy_execution_history] PEH
JOIN syspolicy_policies POL
ON PEH.policy_id = POL.policy_id
JOIN syspolicy_conditions PCO
ON POL.condition_id = PCO.condition_id
JOIN dbo.syspolicy_object_sets POS 
ON POS.facet_name= PCO.facet;


use msdb
GO
SELECT 
POL.name as [Nazwa polityki]
,PCO.name as [Nazwa warunku]
,POS.object_set_name as [Facet]
,PDE.target_query_expression as [XQuery - like path]
,[start_date] as [Data uruchomienia]
,[end_date] as [Data zakonczenia]
, PDE.result_detail as [Informacje szczegolowe] 
FROM [syspolicy_policy_execution_history] PEH
JOIN dbo.syspolicy_policy_execution_history_details PDE
ON PEH.history_id=PDE.history_id
JOIN syspolicy_policies POL
ON PEH.policy_id = POL.policy_id
JOIN syspolicy_conditions PCO
ON POL.condition_id = PCO.condition_id
JOIN dbo.syspolicy_object_sets POS 
ON POS.facet_name= PCO.facet;


use msdb
GO
SELECT 
[Nazwa polityki]
,[Nazwa warunku]
,[Facet]
,[XQuery - like path]
,[start_date]
,[end_date]
FROM
(
SELECT 
          POL.name      as [Nazwa polityki]
          ,PCO.name     as [Nazwa warunku]
          ,PCO.facet    as [Facet]
          ,PDE.target_query_expression as [XQuery - like path]
          ,[start_date]
          ,[end_date]
          ,ROW_NUMBER() OVER(PARTITION BY POL.name ORDER BY PEH.history_id desc) as R
FROM [syspolicy_policy_execution_history] PEH
JOIN dbo.syspolicy_policy_execution_history_details PDE
ON PEH.history_id=PDE.history_id
JOIN syspolicy_policies POL
ON PEH.policy_id = POL.policy_id
JOIN syspolicy_conditions PCO
ON POL.condition_id = PCO.condition_id
) AS D
WHERE D.R=1;


use msdb
GO
DECLARE @name sysname = NULL, @new_name sysname = NULL;
EXEC [sp_syspolicy_rename_policy] @name = 'Guest Permissions'
  ,@new_name ='Some NEW Policy name';

