主页 / dba / 问题 / 233746
Accepted
user1930901
Asked: 2019-04-03 04:49:34 +0800 CST

在扩展属性中获取数据库信息和值

  • 772

我需要从 SQL Server 获取每个数据库（连同其信息），我通过以下方式进行：

use master;
select *
from sys.databases
where database_id > 4 
order by name

我还需要从扩展属性中获取特定值。目前我正在手动执行以下 FOR EACH DB 以获取此值：

use <db_name>
select value from sys.extended_properties
where class_desc='DATABASE' and name = 'Application Name'

但是，我需要分别获取第一个查询显示的每个数据库的列中最后一个查询输出的值。这可以做到吗？

sql-server

3 个回答

  1. Best Answer

    您可以使用游标遍历数据库并将结果集存储在临时表中

    CREATE TABLE #TEMP(Databasename nvarchar(500),
                   value sql_variant)

DECLARE @sql NVARCHAR(max);
DECLARE @dbname NVARCHAR(500);
DECLARE c cursor LOCAL FAST_FORWARD FOR SELECT NAME 
                                        FROM sys.databases 
                                        WHERE database_id > 4 
                                        ORDER BY NAME;
OPEN c 
FETCH NEXT FROM c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
use '+QUOTENAME(@dbname)+'


SELECT '''+QUOTENAME(@dbname)+''' as databasename,
        value 
FROM sys.extended_properties
WHERE class_desc=''DATABASE'' and name = ''Application Name''';

INSERT INTO #TEMP
EXEC(@sql);

FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #TEMP;
DROP TABLE #TEMP;

    测试

    USE my_test;  
GO  
EXEC sp_addextendedproperty   
@name = N'Application Name',   
@value = 'ValueHere';  


USE Test;  
GO  
EXEC sp_addextendedproperty   
@name = N'Application Name',   
@value = 'ValueHere';

    结果

    Databasename    value
[my_test]       ValueHere
[Test]          ValueHere
    • 4

  2. 我会使用动态 T-SQL 将结果组合成一个输出。

    DECLARE @cmd nvarchar(max);
SET @cmd = N'';

SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE N'
UNION ALL
' END + 'SELECT d.name
    , AppName = (
        SELECT ep.value 
        FROM ' + QUOTENAME(d.name) + N'.sys.extended_properties ep
        WHERE ep.class_desc = N''DATABASE'' 
            AND ep.name = N''Application Name''
      )
    , d.*
FROM sys.databases d
WHERE d.database_id = ' + CONVERT(nvarchar(11), d.database_id)
FROM sys.databases d
WHERE d.database_id > 4
    AND d.state_desc = N'ONLINE'
    AND d.user_access = 0
ORDER BY d.name;

SET @cmd = @cmd + N'

IF EXISTS (
        SELECT 1
        FROM sys.databases d
        WHERE d.user_access <> 0
)
BEGIN
    DECLARE @msg varchar(1000);
    SET @msg = ''One or more databases are in single user mode, and cannot be accessed by this session.'';
    RAISERROR (@msg, 14, 1);
END;
';

PRINT @cmd;
EXEC (@cmd);

    SELECT上面的查询为实例上的每个在线、多用户、非系统数据库创建一个 T-SQL语句，并使用UNION ALL. 然后它在一次执行中执行所有组合语句。如果任何数据库未处于多用户模式，则会生成错误以表明输出中未包含一个或多个数据库。

    • 3

  3. 补充其他答案 - 您可以使用sp_ineachdb- 由 Aaron Bertrand 编写，是Brent Ozar 的 firstResponderKit 的一部分。

    DECLARE @cmd nvarchar(max) = N' 
  select  db_name(), value from sys.extended_properties
    where class_desc=''DATABASE'' and name = ''Application Name'';';

EXEC master.dbo.sp_ineachdb @command = @cmd, @user_only = 1;
    • 2

相关问题