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

我正在行使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

办理要领

起首,在硕士中：

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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!