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

另一种要领是建设一个视图(或表值函数)来归并全部数据库的表和索引名称,可是您必需将数据库名称硬编码到视图中,并在添加时对其举办维护/删除要应承包括在此查询中的数据库.与其他数据库差异,它应承您一次检索多个数据库的统计信息.

一,概念：

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

（编辑：湖南网）

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