Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

help how to exclude master, msdb while EXEC master..sp_MSForeachdb 'USE [?] Expand / Collapse
Author
Message
Posted Thursday, January 03, 2008 8:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 09, 2009 9:02 AM
Points: 6, Visits: 27
Hi everyone,
all that I want to do is reindex user tables not system databases tables.
So I use the following code:

EXEC master..sp_MSForeachdb 'USE [?]
SELECT ''?''
EXEC sp_Reindexing'

Note this cmmend works but it also reindex system tables. My question is how do I exclude the system tables.

Thanks,
Post #438415
Posted Thursday, January 03, 2008 9:52 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:48 PM
Points: 31,423, Visits: 13,737
ms_foreachtalbe only works on system tables.

You can open this in the master database and look at the code. It just runs a cursor that grabs each user table.

where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #438475
Posted Thursday, January 03, 2008 11:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:39 PM
Points: 2,278, Visits: 2,998
EXEC master..sp_MSForeachdb '
USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN
SELECT ''?''
EXEC sp_Reindexing
END
'





My blog: http://jahaines.blogspot.com
Post #438550
Posted Thursday, January 03, 2008 1:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 37,715, Visits: 29,970
Or if you feel like been less verbose (and slightly more cryptic)

IF DB_ID(''?'') > 4

All user dbs have DatabaseIDs greater than 4

Then in your sp_reindexing proc, use the objectproperty that Steve posted to only get non-system tables.

Edit: Greater than 4. Not less than.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #438616
Posted Thursday, January 03, 2008 1:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 09, 2009 9:02 AM
Points: 6, Visits: 27
Thanks! Everyone this is the best SQL server community I have come to know
Post #438627
Posted Thursday, January 03, 2008 3:34 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:39 PM
Points: 2,278, Visits: 2,998
Good addition Gail. ;)



My blog: http://jahaines.blogspot.com
Post #438667
Posted Thursday, January 03, 2008 3:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 2,944, Visits: 10,508
exec master..sp_MSForeachdb
'
-- exit if system database
if ''?'' in (''master'',''model'',''msdb'',''tempdb'') return
use ?
print ''Database = ''+db_name()
exec sp_Reindexing
'

Post #438671
Posted Wednesday, January 23, 2008 10:55 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, May 03, 2013 9:01 AM
Points: 722, Visits: 996
Just out of curosity is it best not to do reindex on master, model, msdb tables.


i know the reindex update statistics.
When do you think it is best to run the UPDATE statsitics command.

I have
Check database integrity
Rebuild Indexes
Backup
Remove history files...

I was wondering when to do update statistics and should this be done on the master, model, msdb tables.

Thanks
Post #446547
Posted Wednesday, May 13, 2009 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 05, 2010 1:42 AM
Points: 1, Visits: 16
Tracey,

Generally, reindexing updates the statistics for the columns which are the part of these indexes.
But, the optimizer creates the statistics automatically as well on the pattern of the statements that are executed on the SQL Server. Updating these statistics is also crucial for the perfromance of the queries on the SQL Server.
Answering your question: ideally, I would say updating the statistics with FULLSCAN (by default the statitics are updated with sampling of 10%) would be a good practice.

Cheers!
Rajat
Post #715775
Posted Thursday, July 02, 2009 4:40 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 26, 2012 10:06 AM
Points: 267, Visits: 385
used meforeach table as well :
-------------------------------

1)

Need to create this SP in all the user databases
----------------------------------------------------

create procedure rebuild_index
as exec sp_msforeachtable 'dbcc dbreindex("?"," ",100)'



2)

EXEC master..sp_MSForeachdb 'USE [?]
IF DB_ID(''?'') > 4
SELECT ''?''
exec rebuild_index'

I think Steve wanted to say the MSforeachxxx does not touch the system tables .BTW SQL Sevrer 2005 does not show system tables but views ..

Regards


Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
Post #746123
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse