Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

sp_MSforeachdb and sp_MSforeachtable

Posted on 2007-11-13
10
5,292 Views
Last Modified: 2012-06-27
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', '@'
0
Comment
Question by:BillPowell
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 20276376
Hi,

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
0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
ID: 20276464
Hi,

exec sp_MSforeachdb 'use [@] execute sp_MSForEachTable ''print ''''@'''' + '''','''' + ''''?'''' '' ', '@'

works.

try
exec sp_MSforeachdb 'use [@] execute sp_MSForEachTable ''alter index all on  ''''?'''' Reorganize'' ', '@'

but note the cautions above ...

HTH
  David
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20278082
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 11

Author Comment

by:BillPowell
ID: 20281581
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

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20282058
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 :-) .
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20282818
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.  
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 20283070
True; I think what is catching us is this:

FROM INFORMATION_SCHEMA.TABLES

That always points to the local db.  Please try this instead:

     FETCH NEXT FROM db_cursor INTO @DBNAME
     SET @SQL = N'USE ' + @DBNAME + NCHAR(10)
     SELECT @SQL = @SQL +
          N'DECLARE @SQL2 NVARCHAR(MAX)' + NCHAR(10) +
          N'SELECT @SQL2 = ISNULL(@SQL2 + '';'', N'''') + ' + NCHAR(10) +
          N'    ''ALTER INDEX ALL ON '' + TABLE_NAME + N'' REORGANIZE ''' + NCHAR(10) +
          N'FROM INFORMATION_SCHEMA.TABLES' + NCHAR(10) +
          N'/* PRINT @SQL2 */' + NCHAR(10) +
          N'EXEC(@SQL2)'
     PRINT @SQL
     Exec sp_ExecuteSQL @SQL
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20284667
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_NAME FROM ' +
      name + '.INFORMATION_SCHEMA.TABLES 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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20284786
>> 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.
0
 
LVL 11

Author Comment

by:BillPowell
ID: 20284890
?  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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question