May 15, 2009 at 6:01 am
Hi people:-D
im busy writing a query that retrieves the index fragmentation of each table in a particular database, problem its extremely tedious to run it for one table at a time and to run it for all tables in my database affects db performance while its running....
any advice on how to write my query to retrieve for a few tables at a time...
this is what im working with...
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'Wallet0000');
SET @object_id = OBJECT_ID(N'dbo.AuditCollection');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
GO
May 15, 2009 at 7:21 am
dump the results into a temp table
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'Wallet0000');
SET @object_id = OBJECT_ID(N'dbo.AuditCollection');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT * into #temptable FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
SET @db_id = DB_ID(N'Wallet0000');
SET @object_id = OBJECT_ID(N'dbo.TableName');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
INSERT INTO #temptable
SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');
END;
Select * from #temptable
drop table #temptable
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply