BillPowell
asked on
sp_MSforeachdb and sp_MSforeachtable
Hello,
Im trying to create a script that dynamically runs on a server iterates through each database and each table to defragment indexes. Below is my unsuccessful attempt so far. Can anyone help me fix this.
Thanks
exec sp_MSforeachdb 'use [@] exec sp_MSforeachtable
Alter Index All On ''@.?'' Reorganize', '@'
Im trying to create a script that dynamically runs on a server iterates through each database and each table to defragment indexes. Below is my unsuccessful attempt so far. Can anyone help me fix this.
Thanks
exec sp_MSforeachdb 'use [@] exec sp_MSforeachtable
Alter Index All On ''@.?'' Reorganize', '@'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good advice David, those procedures dont give enough flexibility for what I need. I have created a script (below) to isolate only the user databases that I want to perform index maintenance. The only little snag is that Im having trouble getting the script to switch database context (ie Use DBNAME) before generating each set of Alter Index statements. If you run this on your machine you will see that all of the Alter Index statements reference only tables from the current database. Can you help me over this hump please?
--INDEX MAINTENANCE PROCEDURES
DECLARE @SQL NVarchar(Max)
DECLARE @DBNAME NVarchar(128)
DECLARE db_cursor CURSOR READ_ONLY
FOR SELECT name FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb',' tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM db_cursor INTO @DBNAME
SET @SQL = 'USE ' + @DBNAME + CHAR(13)
Exec sp_ExecuteSQL @SQL --This line should switch the database context
PRINT @SQL
SET @SQL = ''
SELECT @SQL = @SQL + 'ALTER INDEX ALL ON ' + TABLE_NAME + ' REORGANIZE ' + CHAR(13) FROM INFORMATION_SCHEMA.TABLES
PRINT @SQL
Exec sp_ExecuteSQL @SQL
END
CLOSE db_cursor
DEALLOCATE db_cursor
--INDEX MAINTENANCE PROCEDURES
DECLARE @SQL NVarchar(Max)
DECLARE @DBNAME NVarchar(128)
DECLARE db_cursor CURSOR READ_ONLY
FOR SELECT name FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM db_cursor INTO @DBNAME
SET @SQL = 'USE ' + @DBNAME + CHAR(13)
Exec sp_ExecuteSQL @SQL --This line should switch the database context
PRINT @SQL
SET @SQL = ''
SELECT @SQL = @SQL + 'ALTER INDEX ALL ON ' + TABLE_NAME + ' REORGANIZE ' + CHAR(13) FROM INFORMATION_SCHEMA.TABLES
PRINT @SQL
Exec sp_ExecuteSQL @SQL
END
CLOSE db_cursor
DEALLOCATE db_cursor
Each dynamic SQL invocation is in a separate batch, so the context switches back when the dynamic SQL ends. Instead, the USE must appear in the same invocation as the ALTER(s), something like this:
...
BEGIN
FETCH NEXT FROM db_cursor INTO @DBNAME
SET @SQL = N'USE ' + @DBNAME + NCHAR(10)
SELECT @SQL = @SQL +
N'ALTER INDEX ALL ON ' + TABLE_NAME + N' REORGANIZE ' + NCHAR(10)
FROM INFORMATION_SCHEMA.TABLES
PRINT @SQL
Exec sp_ExecuteSQL @SQL
END
PLEASE NO pts for this, as David did most of the work :-) .
...
BEGIN
FETCH NEXT FROM db_cursor INTO @DBNAME
SET @SQL = N'USE ' + @DBNAME + NCHAR(10)
SELECT @SQL = @SQL +
N'ALTER INDEX ALL ON ' + TABLE_NAME + N' REORGANIZE ' + NCHAR(10)
FROM INFORMATION_SCHEMA.TABLES
PRINT @SQL
Exec sp_ExecuteSQL @SQL
END
PLEASE NO pts for this, as David did most of the work :-) .
ASKER
Hey Scott,
That still doesnt work. Try to run that on your own workstation. You will still get references to only the current databases tables even though the appropriate use statements are generated.
That still doesnt work. Try to run that on your own workstation. You will still get references to only the current databases tables even though the appropriate use statements are generated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Scott,
I could not get that to work as the variable declaration repeats itself. I tried a different way to go about this and my initial testing has been successful. Take a look and let me know if you spot any issues. I tried to get this to work using only a Table variable but it seems that any sql executed within sp_Executesql cannot see variables that are declared outsite of the string being executed.
CREATE TABLE #AllTables(CatalogName NVarchar(128),SchemaName NVarchar(128),TableName NVarchar(128))
DECLARE @SQL NVARCHAR(Max)
SET NOCOUNT ON
SET @SQL = ''
SELECT @SQL = @SQL + 'INSERT INTO #AllTables SELECT TABLE_CATALOG,TABLE_SCHEMA ,TABLE_NAM E FROM ' +
name + '.INFORMATION_SCHEMA.TABLE S WHERE TABLE_TYPE = ''BASE TABLE''' + NCHAR(10) FROM master.sys.databases
Exec sp_ExecuteSQL @SQL
SET @SQL = ''
SELECT @SQL = @SQL + 'ALTER INDEX ALL ON ' + CatalogName + '.' +
SchemaName + '.' + TableName + ' REORGANIZE' + NCHAR(10) FROM #AllTables
WHERE CatalogName NOT IN ('master','model','msdb',' tempdb')
Select @SQL
Exec sp_ExecuteSQL @SQL
DROP Table #AllTables
I could not get that to work as the variable declaration repeats itself. I tried a different way to go about this and my initial testing has been successful. Take a look and let me know if you spot any issues. I tried to get this to work using only a Table variable but it seems that any sql executed within sp_Executesql cannot see variables that are declared outsite of the string being executed.
CREATE TABLE #AllTables(CatalogName NVarchar(128),SchemaName NVarchar(128),TableName NVarchar(128))
DECLARE @SQL NVARCHAR(Max)
SET NOCOUNT ON
SET @SQL = ''
SELECT @SQL = @SQL + 'INSERT INTO #AllTables SELECT TABLE_CATALOG,TABLE_SCHEMA
name + '.INFORMATION_SCHEMA.TABLE
Exec sp_ExecuteSQL @SQL
SET @SQL = ''
SELECT @SQL = @SQL + 'ALTER INDEX ALL ON ' + CatalogName + '.' +
SchemaName + '.' + TableName + ' REORGANIZE' + NCHAR(10) FROM #AllTables
WHERE CatalogName NOT IN ('master','model','msdb','
Select @SQL
Exec sp_ExecuteSQL @SQL
DROP Table #AllTables
>> I could not get that to work as the variable declaration repeats itself. <<
? The @SQL2 declaration is inside the dynamic SQL, which means the variable is destroyed as soon as the code completes.
It does look like your replacement code should work, though with a slightly higher overhead probably because of the temp table.
? The @SQL2 declaration is inside the dynamic SQL, which means the variable is destroyed as soon as the code completes.
It does look like your replacement code should work, though with a slightly higher overhead probably because of the temp table.
ASKER
? The @SQL2 declaration is inside the dynamic SQL, which means the variable is destroyed as soon as the code completes.
Your right, my bad. I did not implement your code properly when I got that error. Now both solutions are working. Its just a matter of deciding which I hate more: cursors or temp tables.
Thanks guys
Bill
Your right, my bad. I did not implement your code properly when I got that error. Now both solutions are working. Its just a matter of deciding which I hate more: cursors or temp tables.
Thanks guys
Bill
exec sp_MSforeachdb 'use [@] execute sp_MSForEachTable ''print ''''?'''' '' ', '@'
Note that this will also iterate over the tempdb table, which you shouldn't do.
Given the time that the index rebuilds take, and the small number of tables and databases, and that these procedures are implimented as cursors, I'd suggest writing this one yourself as two cursors - an inner one over the tables and an outer one over the databases.
HTH
David