Как получить список всех баз данных с их расширенными свойствами?

#sql-server

Вопрос:

Я работаю над SQL server 2008, я хочу получить все базы данных на сервере с определенным свойством, связанным с базой данных. Скажем, например, у нас есть расширенное свойство с ключом «x» для некоторых баз данных на сервере. я хочу, чтобы результат был

 DatabaseName | x
Db1          | value1
Db2          | value2
Db3          | value3
Db4          | null

где Db4 не имеет расширенного свойства с ключом «x»

любая помощь, большое вам спасибо

1. Что такое свойство ‘x’? Любой пример? Здесь хранятся основные данные о базах данных SELECT * FROM sys.databases

2. Каждая база данных в sql server имеет расширенные свойства в виде пар ключ-значение, я хочу расширенное свойство с ключом «x».

3. Я понял! Надеюсь, мой ответ вам поможет.

Ответ №1:

Вы можете использовать sys.extended_properties (info) и динамический SQL для получения всех расширенных свойств.

 IF OBJECT_ID(N'#extprops') IS NOT NULL DROP TABLE #extprops

CREATE TABLE #extprops (
    dbname nvarchar(max),
    class_desc sql_variant,
    [name] sql_variant,
    [value] sql_variant
)

DECLARE @sql nvarchar(max)

SELECT @sql = (
    SELECT 'USE ' QUOTENAME([name])  ' INSERT INTO #extprops SELECT '''  [name]   ''' as dbname, class_desc, [name], [value] FROM sys.extended_properties;'  CHAR(10)
    FROM sys.databases
    FOR XML PATH('')
)

PRINT @sql

EXEC sp_executesql @sql

SELECT *
FROM #extprops

Вывод:

 dbname          class_desc          name                                    value
msdb            DATABASE            Microsoft_Management_Utility_Version    ___SQLVERSION___NEW___
AdventureWorks  DATABASE            MS_Description                          AdventureWorks 2014 Sample OLTP Database
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Stores (including store contacts) that sell Adventure Works Cycles products to consumers.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Manufacturing failure reasons lookup table.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Primary key for ScrapReason records.
AdventureWorks  OBJECT_OR_COLUMN    MS_Description                          Failure description.
...

Если print @sql вы увидите запрос, подобный этому:

 USE [master] INSERT INTO #extprops SELECT 'master' as dbname, class_desc, [name], [value] FROM sys.extended_properties;
USE [tempdb] INSERT INTO #extprops SELECT 'tempdb' as dbname, class_desc, [name], [value] FROM sys.extended_properties;
USE [model] INSERT INTO #extprops SELECT 'model' as dbname, class_desc, [name], [value] FROM sys.extended_properties;

После этого вы можете использовать любые фильтры для #extprops таблицы.