One way would be to make a system procedure in master
and then create a wrapper in your maintenance database. Note that this will only work for one database at a time.
First, in 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, @partNumber, @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, TableName, IndexName, PercentFragment DESC;
END
GO
-- needs to be marked as a system object:
EXEC sp_MS_MarkSystemObject N'dbo.sp_GetFragStats';
GO
Now, in your maintenance database, create a wrapper that uses dynamic SQL to set the context correctly:
USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
@DatabaseName SYSNAME, -- can't really be NULL, right?
@tableName NVARCHAR(128) = NULL,
@indexID INT = NULL,
@partNumber INT = NULL,
@Mode NVARCHAR(20) = N'DETAILED'
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'USE ' + QUOTENAME(@DatabaseName) + ';
EXEC dbo.sp_GetFragStats @tableName, @indexID, @partNumber, @Mode;';
EXEC sp_executesql
@sql,
N'@tableName NVARCHAR(128),@indexID INT,@partNumber INT,@Mode NVARCHAR(20)',
@tableName, @indexID, @partNumber, @Mode;
END
GO
(The reason the database name can't really be NULL
is because you can't join to things like sys.objects
and sys.indexes
since they exist independently in each database. So perhaps have a different procedure if you want instance-wide information.)
Now you can call this for any other database, e.g.
EXEC YourMaintenanceDatabase.dbo.GetFragStats
@DatabaseName = N'AdventureWorks2012',
@TableName = N'SalesOrderHeader';
And you can always create a synonym
in each database so you don't even have to reference the name of the maintenance database:
USE SomeOtherDatabase;`enter code here`
GO
CREATE SYNONYM dbo.GetFragStats FOR YourMaintenanceDatabase.dbo.GetFragStats;
Another way would be to use dynamic SQL, however this, too, will only work for one database at a time:
USE YourMaintenanceDatabase;
GO
CREATE PROCEDURE dbo.GetFragStats
@DatabaseName SYSNAME,
@tableName NVARCHAR(128) = NULL,
@indexID INT = NULL,
@partNumber INT = NULL,
@Mode NVARCHAR(20) = N'DETAILED'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(MAX) = N'SELECT
DatabaseName = @DatabaseName,
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
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, i.index_id)
AND t.name = COALESCE(@tableName, t.name)
CROSS APPLY
' + QUOTENAME(@DatabaseName) + '.sys.dm_db_index_physical_stats(
DB_ID(@DatabaseName), t.[object_id], i.index_id, @partNumber, @Mode) AS s
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY
DatabaseName, TableName, IndexName, PercentFragment DESC;';
EXEC sp_executesql @sql,
N'@DatabaseName SYSNAME, @tableName NVARCHAR(128), @indexID INT,
@partNumber INT, @Mode NVARCHAR(20)',
@DatabaseName, @tableName, @indexID, @partNumber, @Mode;
END
GO
Yet another way would be to create a view (or table-valued function) to union the table and index names of all your databases, however you'd have to hard-code the database names into the view, and maintain them as you add/remove databases that you want to allow to be included in this query. This would, unlike the others, allow you to retrieve stats for multiple databases at once.
First, the view:
CREATE VIEW dbo.CertainTablesAndIndexes
AS
SELECT
db = N'AdventureWorks2012',
t.[object_id],
[table] = t.name,
i.index_id,
[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',
t.[object_id],
[table] = t.name,
i.index_id,
[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
Then the procedure:
CREATE PROCEDURE dbo.GetFragStats
@DatabaseName NVARCHAR(128) = NULL,
@tableName NVARCHAR(128) = NULL,
@indexID INT = NULL,
@partNumber INT = NULL,
@Mode NVARCHAR(20) = N'DETAILED'
AS
BEGIN
SET NOCOUNT ON;
SELECT
DatabaseName = DB_NAME(s.database_id),
TableName = v.[table],
IndexName = v.[index],
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
FROM dbo.CertainTablesAndIndexes AS v
CROSS APPLY sys.dm_db_index_physical_stats
(DB_ID(v.db), v.[object_id], v.index_id, @partNumber, @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, TableName, IndexName, PercentFragment DESC;
END
GO