February 9, 2010 at 3:11 pm
Hoping you can help me. The following script allows me to loop through each database to print something.
declare @dbname varchar(255)
declare c_dblist cursor for
select name from master.dbo.sysdatabases order by name
open c_dblist
fetch next from c_dblist into @dbname
while @@fetch_status = 0
begin
print @dbname
exec('use ' + @dbname)
--execute sp_MSforeachtable @command1='DBCC DBREINDEX ([?], '''', 100)' –TRYING TO GET THIS TO RUN ON EACH DATABASE ON A SERVER
select name from sysusers
fetch next from c_dblist into @dbname
end
close c_dblist
deallocate c_dblist
now the moment uncomment the following line
--execute sp_MSforeachtable @command1='DBCC DBREINDEX ([?], '''', 100)', @command2='print [?]'
I can only get the master database to show and the execution will not work.
How can I loop through each database on a server and perform the DBCC task using the sp_MSforeachtable procedure?
February 9, 2010 at 3:32 pm
tim-407748 (2/9/2010)
Hoping you can help me.
</snip>
now the moment uncomment the following line
--execute sp_MSforeachtable @command1='DBCC DBREINDEX ([?], '''', 100)', @command2='print [?]'
</snip>
How can I loop through each database on a server and perform the DBCC task using the sp_MSforeachtable procedure?
try
execute sp_MSforeachtable @command1='DBCC DBREINDEX (''?'', '''', 100)', @command2='print ''?'''
February 9, 2010 at 3:44 pm
thanks grasshopper but it did not do the dbcc command for each table in each data base. i was told that you can not use the USE in a batch. the problem is that the following:
execute sp_MSforeachtable @command1='DBCC DBREINDEX (''?'', '''', 100)', @command2='print ''?'''
will only work on the current database. i am trying to change that in the loop (as you can see) but it is not working.
i added the following to the select statement
WHERE name NOT IN ('master','model','msdb','tempdb','distrbution')
i only have one other database in this server and i still see that the MASTER is the active database
when i run all the code i get Command(s) completed successfully. it did not because the sp_MSforeachtable did not run. i know that if it did i would get a result like the following:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
for each index.
tim
February 9, 2010 at 3:57 pm
Woops, sorry, yes I misunderstood your problem.
Revised.. 🙂
declare @dbname varchar(255), @sql varchar(255)
declare c_dblist cursor for
select name from master.dbo.sysdatabases order by name
open c_dblist
fetch next from c_dblist into @dbname
while @@fetch_status = 0
begin
print @dbname
SET @sql = 'sp_MSforeachtable @command1=''''DBCC DBREINDEX (''''''''?'''''''', '''''''''''''''', 100)'''', @command2=''''print ''''''''?'''''''''''''
SET @sql = @dbname + '.dbo.sp_executesql @statement = N''' + @sql + ''''
exec(@sql)
fetch next from c_dblist into @dbname
end
close c_dblist
deallocate c_dblist
February 10, 2010 at 3:23 pm
How about using "SP_MSFOREACHDB"
February 10, 2010 at 10:02 pm
GT-897544 (2/10/2010)
How about using "SP_MSFOREACHDB"
But where's the fun in that?
DECLARE @CMD NVARCHAR(500)
SET @CMD = '
if db_id(''*'') > 4
USE [*]
EXEC sp_msforeachtable @command1=''DBCC DBREINDEX (''''?'''','''''''', 100)'', @command2=''print ''''?''''''
'
EXEC sp_msforeachdb @CMD, @replacechar = '*'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply