主页 / dba / 问题 / 16895
Accepted
mdoyle
Asked: 2012-04-24 07:03:40 +0800 CST

通过 T-SQL 以编程方式引用数据库

  • 772

我正在编写一个将数据库名称作为参数并返回该数据库索引及其碎片级别的表的存储过程。这个存储过程将存在于我们的 DBA 数据库中（包含 DBA 用于监视和优化事物的表的数据库）。有问题的系统都是 SQL Server 2008 R2，如果这有所不同的话。

我已经完成了基本查询，但一直在尝试提供索引的实际名称。据我所知，该信息包含在每个人的 sys.indexes 视图中。我的具体问题是尝试从另一个数据库的存储过程中以编程方式引用该视图。

为了说明，这是有问题的查询部分：

FROM sys.dm_db_index_physical_stats(@db_id,NULL,NULL,NULL,NULL) p
INNER JOIN sys.indexes b ON p.[object_id] = b.[object_id] 
    AND p.index_id = b.index_id 
    AND b.index_id != 0

从@db_id 标识的数据库执行查询时，查询工作正常，因为它使用了正确的 sys.indexes 视图。但是，如果我尝试从 DBA 数据库中调用它，则它全部为空，因为 sys.indexes 视图用于错误的数据库。

更一般地说，我需要能够做这样的事情：

DECLARE @db_name NVARCHAR(255) = 'my_database';
SELECT * FROM @db_name + '.sys.indexes';

或者

USE @db_name;

我曾尝试使用字符串连接和 OBJECT_NAME/OBJECT_ID/DB_ID 函数的组合来切换数据库或引用其他数据库，但似乎没有任何效果。我很感激社区可能有的任何想法，但我怀疑我将不得不重新设计这个存储过程以驻留在每个单独的数据库中。

在此先感谢您的任何建议。

sql-server

3 个回答

  1. Best Answer

    动态 SQL 对于这些类型的管理任务非常有用。这是我编写的存储过程的片段，它不仅获取碎片整理级别，还生成执行碎片整理的代码：

    select @SQL = 
'
select getdate(),
       ''' + @@ServerName + ''',
       ''' + @DatabaseName + ''',
       so.Name,
       si.Name,
       db_id(''' + @DatabaseName + '''),
       ips.object_id,
       ips.index_id,
       ips.index_type_desc,
       ips.alloc_unit_type_desc,
       ips.index_depth,
       ips.avg_fragmentation_in_percent,
       ips.fragment_count,
       avg_fragment_size_in_pages,
       ips.page_count,
       ips.record_count,
       case
         when ips.index_id = 0 then ''alter table [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
         else ''alter index '' + si.name + '' on [' + @DatabaseName + '].'' + ss.name + ''.['' + so.name + ''] rebuild with (online = on)''
       end
  from sys.dm_db_index_physical_stats(db_id(''' + @DatabaseName + '''),null,null,null, ''' + @SampleMode + ''') ips
  join [' + @DatabaseName + '].sys.objects so  on so.object_id = ips.object_id
  join [' + @DatabaseName + '].sys.schemas ss  on ss.schema_id = so.schema_id
  join [' + @DatabaseName + '].sys.indexes si  on si.object_id = ips.object_id
                      and si.index_id  = ips.index_id
order by so.Name, ips.index_id
'

exec (@SQL)
    • 10

  2. 动态 SQL 的替代方案是SQLCMD，它可以从命令行、代理作业步骤、Invoke-Sqlcmd Powershell cmdlet 调用或在SSMS中启用。您在 SQLCMD 语法中的示例将是：

    :SETVAR DatabaseName MyDatabase

SELECT * FROM $(DatabaseName).sys.indexes;

    SQLCMD 模式是我希望早先知道的那些特性之一。在很多情况下都很方便。

    • 8

  3. 通常很难从包含在不同 DB 中的过程中引用一组表。如果您将程序安装在 Master 中，作为系统程序，那么它可以在其他 DB 上下文中使用，而无需尝试引用自身。

    我认为：如果您的过程以“sp_”开头，那么它会变得普遍可见，并且如果您在“sys.sp_%”模式中定义它，那么它可以在其他数据库上下文中使用。

    这将提供一种在多个 DB 中操作的替代方式，而无需动态插入 DB_name。

    • 0

相关问题