I am working on a customized maintenance solution using the sys.dm_db_index_physical_stats
view. I currently have it being referenced from a stored procedure. Now when that stored procedure runs on one of my databases, it does what I want it to do and pulls down a listing of all records regarding any database. When I place it on a different database it pulls down a listing of all records relating to only that DB.
For example (code at bottom):
- Query run against Database 6 shows [requested] information for databases 1-10.
- Query run against Database 3 shows [requested] information for only database 3.
The reason I want this procedure specifically on database three is because I'd prefer to keep all maintenance objects within the same database. I'd like to have this job sit in the maintenance database and work as if it were in that application database.
Code:
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
Best Answer
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:
Now, in your maintenance database, create a wrapper that uses dynamic SQL to set the context correctly:
(The reason the database name can't really be
NULL
is because you can't join to things likesys.objects
andsys.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.
And you can always create a
synonym
in each database so you don't even have to reference the name of the maintenance database:Another way would be to use dynamic SQL, however this, too, will only work for one database at a time:
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:
Then the procedure: