sql-server – 在调用数据库上下文时执行的中央存储过程

副标题#e#

我正在使用sys.dm_db_index_physical_stats视图开发自定义维护解决方案.我目前从存储过程中引用它.现在,当该存储过程在我的一个数据库上运行时,它会执行我想要它执行的操作,并下拉有关任何数据库的所有记录的列表.当我将它放在不同的数据库上时,它会下拉所有与该数据库相关的记录列表.

例如(底部的代码)：

>针对数据库6的查询运行显示数据库1-10的[请求]信息.
>针对数据库3的查询运行仅显示数据库3的[请求]信息.

我特别想在数据库3上使用此过程的原因是因为我更喜欢将所有维护对象保留在同一个数据库中.我希望将这项工作放在维护数据库中并像在应用程序数据库中一样工作.

码：

ALTER PROCEDURE [dbo].[GetFragStats] 
    @databaseName   NVARCHAR(64) = NULL,@tableName     NVARCHAR(64) = NULL,@indexID       INT          = NULL,@partNumber    INT          = NULL,@Mode          NVARCHAR(64) = 'DETAILED'
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @databaseID INT,@tableID INT

    IF @databaseName IS NOT NULL
        AND @databaseName NOT IN ('tempdb','ReportServerTempDB')
    BEGIN
        SET @databaseID = DB_ID(@databaseName)
    END

    IF @tableName IS NOT NULL
    BEGIN
        SET @tableID = OBJECT_ID(@tableName)
    END

    SELECT D.name AS DatabaseName,T.name AS TableName,I.name AS IndexName,S.index_id AS IndexID,S.avg_fragmentation_in_percent AS PercentFragment,S.fragment_count AS TotalFrags,S.avg_fragment_size_in_pages AS PagesPerFrag,S.page_count AS NumPages,S.index_type_desc AS IndexType
    FROM sys.dm_db_index_physical_stats(@databaseID,@tableID,@indexID,@partNumber,@Mode) AS S
    JOIN 
       sys.databases AS D ON S.database_id = D.database_id
    JOIN 
       sys.tables AS T ON S.object_id = T.object_id
    JOIN 
       sys.indexes AS I ON S.object_id = I.object_id
                        AND S.index_id = I.index_id
    WHERE 
        S.avg_fragmentation_in_percent > 10
    ORDER BY 
        DatabaseName,TableName,IndexName,PercentFragment DESC    
END
GO

解决方法

一种方法是在master中创建系统过程,然后在维护数据库中创建一个包装器.请注意,这一次只适用于一个数据库.

首先,在硕士中：

USE [master];
GO
CREATE PROCEDURE dbo.sp_GetFragStats -- sp_prefix required
  @tableName    NVARCHAR(128) = NULL,@indexID      INT           = NULL,@partNumber   INT           = NULL,@Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(),TableName       = t.name,IndexName       = i.name,IndexID         = s.index_id,PercentFragment = s.avg_fragmentation_in_percent,TotalFrags      = s.fragment_count,PagesPerFrag    = s.avg_fragment_size_in_pages,NumPages        = s.page_count,IndexType       = s.index_type_desc
    -- shouldn't s.partition_number be part of the output as well?
  FROM sys.tables AS t
  INNER JOIN sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID,i.index_id)
    AND t.name = COALESCE(@tableName,t.name)
  CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(),t.[object_id],i.index_id,@Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  -- probably also want to filter on minimum page count too
  -- do you really care about a table that has 100 pages?
  ORDER BY 
    DatabaseName,PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO

现在,在维护数据库中,创建一个使用动态SQL正确设置上下文的包装器：

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,-- can't really be NULL,right?
  @tableName    NVARCHAR(128) = NULL,@Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  DECLARE @sql NVARCHAR(MAX);

  SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
    EXEC dbo.sp_GetFragStats @tableName,@Mode;';

  EXEC sp_executesql 
    @sql,N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',@tableName,@Mode;
END
GO

(数据库名称实际上不能为NULL的原因是因为它们不能连接到sys.objects和sys.indexes之类的东西,因为它们在每个数据库中独立存在.所以如果你想要实例范围的信息,可能有不同的过程.)

现在你可以为任何其他数据库调用它,例如

EXEC YourMaintenanceDatabase.dbo.GetFragStats 
  @DatabaseName = N'AdventureWorks2012',@TableName    = N'SalesOrderHeader';

并且您始终可以在每个数据库中创建同义词,因此您甚至不必引用维护数据库的名称：

USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;

另一种方法是使用动态SQL,但是这也只能一次用于一个数据库：

USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName SYSNAME,@tableName    NVARCHAR(128) = NULL,@Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sql NVARCHAR(MAX) = N'SELECT
    DatabaseName    = @DatabaseName,IndexType       = s.index_type_desc
  FROM ' + QUOTENAME(@DatabaseName) + '.sys.tables AS t
  INNER JOIN ' + QUOTENAME(@DatabaseName) + '.sys.indexes AS i
    ON t.[object_id] = i.[object_id]
    AND i.index_id = COALESCE(@indexID,t.name)
  CROSS APPLY
    ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
        DB_ID(@DatabaseName),@Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
  ORDER BY 
    DatabaseName,PercentFragment DESC;';

  EXEC sp_executesql @sql,N'@DatabaseName SYSNAME,@tableName NVARCHAR(128),@DatabaseName,@Mode;
END
GO

#p#副标题#e##p#分页标题#e#

另一种方法是创建一个视图(或表值函数)来合并所有数据库的表和索引名称,但是您必须将数据库名称硬编码到视图中,并在添加时对其进行维护/删除要允许包含在此查询中的数据库.与其他数据库不同,它允许您一次检索多个数据库的统计信息.

一,观点：

CREATE VIEW dbo.CertainTablesAndIndexes
AS
  SELECT 
    db = N'AdventureWorks2012',[table] = t.name,[index] = i.name
  FROM AdventureWorks2012.sys.tables AS t
  INNER JOIN AdventureWorks2012.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  UNION ALL

  SELECT 
    db = N'database2',[index] = i.name
  FROM database2.sys.tables AS t
  INNER JOIN database2.sys.indexes AS i
  ON t.[object_id] = i.[object_id]

  -- ... UNION ALL ...
  ;
GO

然后程序：

CREATE PROCEDURE dbo.GetFragStats
  @DatabaseName NVARCHAR(128) = NULL,@Mode         NVARCHAR(20)  = N'DETAILED'
AS
BEGIN
  SET NOCOUNT ON;

  SELECT
    DatabaseName    = DB_NAME(s.database_id),TableName       = v.[table],IndexName       = v.[index],IndexType       = s.index_type_desc
  FROM dbo.CertainTablesAndIndexes AS v
  CROSS APPLY sys.dm_db_index_physical_stats
    (DB_ID(v.db),v.[object_id],v.index_id,@Mode) AS s
  WHERE s.avg_fragmentation_in_percent > 10
    AND v.index_id = COALESCE(@indexID,v.index_id)
    AND v.[table] = COALESCE(@tableName,v.[table])
    AND v.db = COALESCE(@DatabaseName,v.db)
  ORDER BY 
    DatabaseName,PercentFragment DESC;
END
GO