将数据库名称与SQL Server系统目录中的元数据相关联

Question

我想产生一个报告，显示有关服务器上数据库的表和列的元数据。

理想情况下，我想将数据库本身作为报告中的一列，但找不到SQL Server上各种sys对象之间的合适关系。

到目前为止，我有：

声明@r varchar（50）

set @r ='test1'

声明@t varchar（50）

设置@t ='testStuff'

选择ac。[name]为[column_name]，t。

来自test1.sys。[表]作为t

内部加入test1.sys。[all_columns] ac on t。[object_id] = ac。[object_id]

其中t。[is_ms_shipped] = 0

联盟全部

选择AC。[名称]为[column_name]， T. [名称]为[table_name]，@t为[数据库]，''as [Description]

来自teststuff.sys。[表]作为t

内部加入teststuff.sys。[all_columns] ac on t。[object_id] = ac。[object_id]

其中t。[is_ms_shipped] = 0

通过制作数据库名称的参数，我介绍了我认为不需要的高度可维护性。理想情况下，我希望它作为视图，因为它将自动自动更新每个数据库对象。

目标是实现这样的目标：

声明@d varchar（50）

set @d =（从sys.databases中选择名称，其中名称不在（'master'，'tempdb'，'model'，'msdb'中））

选择ac。[name]为[column_name]，t。

来自系统。[表]作为t
内部加入系统。[all_columns] ac on t。[object_id] = ac。[object_id]

?????上的内部加入sys.database d

其中t。[is_ms_shipped] = 0

有可能执行此操作吗？

Answer 1

尽管您无法使用视图，但您可以使用生成动态SQL语句的脚本或存储过程。下面的示例生成了每个数据库目录视图的所有查询，每个查看都有数据库名称。

DECLARE @SQL nvarchar(MAX);
SELECT @SQL = STUFF(
    (SELECT 'UNION ALL
    SELECT AC.[name] COLLATE Latin1_General_100_CI_AS_KS_WS_SC AS [COLUMN_NAME], T.[name] COLLATE Latin1_General_100_CI_AS_KS_WS_SC AS [TABLE_NAME], N''' + name + N''' AS [Database], '''' AS [DESCRIPTION]
    FROM ' + QUOTENAME(name) + '.sys.[tables] AS T 
    INNER JOIN ' + QUOTENAME(name) + '.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
    WHERE T.[is_ms_shipped] = 0 '
    FROM sys.databases
    WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
    FOR XML PATH, TYPE).value('.', 'nvarchar(MAX)'),1,11,'') + N';';
EXEC(@SQL);

Answer 2

如果使用[INFORMATION_SCHEMA].[COLUMNS]而不是sys视图，它将公开[TABLE_CATALOG]列，可用于加入sys.databases。

Answer 3

您需要使用

1. SP_MSFOREACHDB存储的过程绕过您的服务器上的所有DB

2. 可以使用此T-SQL脚本

   之间的DB-Table-Schema之间的链接

   SELECT DB_NAME () as DataBaseName,
   s.Name as SchemaName,
   t.NAME as TableName
   FROM  sys.tables t
   JOIN  sys.indexes i ON t.object_id = i.object_id
   JOIN  sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
   JOIN  sys.allocation_units a ON p.partition_id = a.container_id
   JOIN sys.schemas s ON t.schema_id = s.schema_id 
   WHERE t.is_ms_shipped = 0 and t.NAME NOT LIKE 'dt%' 
   GROUP BY  t.Name, s.Name, p.Rows,t.create_date 
   ORDER BY  DB_NAME(),s.Name,t.Name