Добрый день друзья!

Нужна Ваша помощь сделал скрипт с отображением баз Always On



1. Не могу дотумкатся как вытащить прям все базы даже без Always On
2. В db_state сделать условие чтобы менялся текст NULL на Secondary ONLINE на Primary
3. Доклеить 1 поле с Extended_Properties

Den_KPДобрый день друзья!

Нужна Ваша помощь сделал скрипт с отображением баз Always On



1. Не могу дотумкатся как вытащить прям все базы даже без Always On
2. В db_state сделать условие чтобы менялся текст NULL на Secondary ONLINE на Primary
3. Доклеить 1 поле с Extended_Properties

Для Опций есть отдельный скрипт
-----SELECT Logs-------------
IF OBJECT_ID('tempdb.dbo.#dbdetailes', 'U') IS NOT NULL
DROP TABLE #dbdetailes;
create table #dbdetailes
(database_name varchar(200)
,log_size_mb varchar(200)
,row_size_mb varchar(200)
,total_size_mb varchar(200)
)
insert into #dbdetailes
SELECT
database_name = DB_NAME(database_id)
, CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as log_size_mb
, CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) as row_size_mb
, CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) as total_size_mb
FROM sys.master_files
GROUP BY database_id
--------tempdb for extended properties--------------
IF OBJECT_ID('tempdb.dbo.#properties', 'U') IS NOT NULL
DROP TABLE #properties;
CREATE TABLE #properties
(DatabaseName nvarchar(200) ,
PropertyName nvarchar(200) ,
PropertyValue nvarchar(200) )

EXEC sp_MSforeachdb
'INSERT INTO #properties SELECT ''?'' AS DatabaseName,
CAST(name AS nvarchar), CAST(Value AS nvarchar)
FROM [?].sys.extended_properties WHERE class=0'
--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbis', 'U') IS NOT NULL
DROP TABLE #dbis;
create table #dbis
(DatabaseName nvarchar(200)
,InformationSystem nvarchar(200)
)
insert into #dbis
select
DatabaseName
,PropertyValue as InformationSystem
from #properties
where PropertyName like 'Information System'

--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbru', 'U') IS NOT NULL
DROP TABLE #dbru;
create table #dbru
(DatabaseName nvarchar(200)
,ResponsibleUnit nvarchar(200)
)
insert into #dbru
select
DatabaseName
,PropertyValue as ResponsibleUnit
from #properties
where PropertyName like 'Responsible Unit'

--------------------------------------------------------
IF OBJECT_ID('tempdb.dbo.#dbrp', 'U') IS NOT NULL
DROP TABLE #dbrp;
create table #dbrp
(DatabaseName nvarchar(200)
,ResponsiblePerson nvarchar(200)
)
insert into #dbrp
select
DatabaseName
,PropertyValue as ResponsiblePerson
from #properties
where PropertyName like 'Responsible Person'
-------Result----------------
select
@@servername AS Instance
,s.DatabaseName as DatabaseName
,s.InformationSystem as InformationSystem
,u.ResponsibleUnit as ResponsibleUnit
,p.ResponsiblePerson as ResponsiblePerson
,d.log_size_mb as log_size_mb
,d.row_size_mb as row_size_mb
,d.total_size_mb as total_size_mb

from #dbis s
join #dbru u on s.DatabaseName = u.DatabaseName
join #dbrp p on s.DatabaseName = p.DatabaseName
join #dbdetailes d on s.DatabaseName = d.database_name