Dynamic SQL. How to use it for all databases
-
Hi,
I have the below SP which defragments indexes in a database. I would like to use it to defragment indexes for all databases.
CREATE PROCEDURE "usp_dbindexdefrag"
as
BEGIN
-- Declare variables
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
DECLARE @rid INT
-- Decide on the maximum fragmentation to allow
SET @maxfrag = 30.0
-- Create the tables list
DECLARE @tables TABLE (
TblName sysname
)
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL,
Rid int IDENTITY(1,1)
)
SET NOCOUNT ON
-- Insert the tables names into the table
INSERT INTO @tables (TblName)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the tables in the database
SET @tablename = (SELECT MIN(TblName) FROM @tables)
WHILE @tablename IS NOT NULL
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
SET @tablename = (SELECT MIN(TblName) FROM @tables WHERE TblName > @tablename)
END
-- Fetch first objects to process
SELECT @rid = (SELECT MIN(Rid) FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0)
SELECT @tablename = ObjectName, @objectid = ObjectId, @indexid = IndexId, @frag = LogicalFrag FROM #fraglist WHERE Rid = @rid
-- Loop through the list of indexes to be defragged
WHILE @rid IS NOT NULL
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
-- Fetch next object
SELECT @rid = (SELECT MIN(Rid) FROM #fraglist WHERE rid > @rid AND LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0)
SELECT @tablename = ObjectName, @objectid = ObjectId, @indexid = IndexId, @frag = LogicalFrag FROM #fraglist
END
SET NOCOUNT OFF
DROP TABLE #fraglist
END
-------------------------------------------------------------------------------------
I tried to wrap it into another SP below, but it was no luck because of dynamic SQL constraints.
CREATE PROCEDURE "usp_serverindexdefrag"
as
begin
declare @s-2 nvarchar(500)
declare @low nvarchar(11)
declare @Dbname varchar(50)
declare @dbs table (Dbname sysname)
set nocount on
insert into @dbs select name from master.dbo.sysdatabases (nolock) where name not in ('master', 'model', 'msdb', 'tempdb')
set @Dbname = (select min(Dbname) from @dbs)
while @Dbname is not null
begin
set @s-2 = 'USE ' + @Dbname + ' EXEC UTIL.dbo.usp_dbindexdefrag'
--print @s-2
exec master.dbo.sp_executesql @s-2
set @Dbname = (select min(Dbname) from @dbs where Dbname > @Dbname)
end
set nocount off
end
-------------------------------------------------------------------------------------
What would be the best way to code such index defragmentation for all databases.
Thanks.
-
Hello,
there is an undocumented procedure sp_MSforeachdb which can be used to perform a task on all databases. I have never needed that, so I can't tell for sure whether it can be used for defragmentation - but it is a possibility. Try to find out more about this procedure.
-
Unfortunately it did not work. If you can't use a cursor to perform the task, you can not use this SP as well.
-
/*
This is a simple example of dynamically building a script and dynamically executing it against multiple databases.
Notice the square brackets [ ] around the @DBName. This would be required for database names that contain embedded spaces, hyphen, etc.
The char(13)'s are not necessary, but help if you need to PRINT @sql to see the statement that will execute.
*/
SET NOCOUNT ON
DECLARE @sql as nvarchar(2000)
, @Name as sysname
, @DBName as nvarchar(128)
DECLARE Database_cursor CURSOR FOR
SELECT [name]
FROM Master.dbo.sysdatabases
ORDER BY [name]
OPEN Database_cursor
FETCH NEXT FROM Database_cursor
INTO @Name
SELECT @DBName=CONVERT(nvarchar(128), @Name)
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'USE [' + @DBName + '] ' + CHAR(13) +
'DECLARE @tablecount AS int ' + CHAR(13) +
'SELECT @tablecount = COUNT(*) FROM sysobjects WHERE xtype=' + CHAR(39) + 'U' + CHAR(39) + ' ' + CHAR(13) +
'PRINT ' + CHAR(39) + @DBName +CHAR(39) + ' + ' + CHAR(39) + ' ' + CHAR(39) + ' + CONVERT(nvarchar(5), @tablecount) '
EXEC dbo.sp_executesql @sql
FETCH NEXT FROM Database_cursor
INTO @Name
SELECT @DBName=CONVERT(nvarchar(128), @Name)
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply