Post reply

help how to exclude master, msdb while EXEC master..sp_MSForeachdb 'USE [?]

  • January 3, 2008 at 8:37 am

    #185247

    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,

  • January 3, 2008 at 9:52 am

    #764445

    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

  • January 3, 2008 at 11:51 am

    #764509

    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

  • January 3, 2008 at 1:29 pm

    #764562

    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, MVP, M.Sc (Comp Sci)
    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

  • January 3, 2008 at 1:49 pm

    #764573

    Thanks! Everyone this is the best SQL server community I have come to know

  • January 3, 2008 at 3:34 pm

    #764602

    Good addition Gail. 😉


    My blog: http://jahaines.blogspot.com

  • January 3, 2008 at 3:49 pm

    #764604

    exec master..sp_MSForeachdb

    '

    -- exit if system database

    if ''?'' in (''master'',''model'',''msdb'',''tempdb'') return

    use ?

    print ''Database = ''+db_name()

    exec sp_Reindexing

    '

  • January 23, 2008 at 10:56 am

    #770988

    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

  • May 13, 2009 at 4:08 am

    #993533

    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

  • July 2, 2009 at 4:40 am

    #1019022

    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)

  • July 8, 2009 at 10:23 am

    #1021841

    GilaMonster (1/3/2008)

    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.

    Distribution, ReportServer, ReportServerTempDB > 4

  • July 9, 2009 at 12:41 pm

    #1022608

    You should consider writing your own code for looping through each of the databases as sp_MSForEachDB is UNDOCUMENTED and, therefore, susceptible to changes, or even removal in future releases of SQL Server.

    It is considered bad practice to use undocumented commands in production code.

    Chris

  • November 2, 2011 at 7:39 pm

    #1402483

    Is it possible to use a variable with the if statement when using the proc sp_MSforeachdb. I'm trying to do the following and getting errors

    EXEC master..sp_MSForeachdb '

    USE [?]

    IF ''?'' like '@db_type'

    BEGIN

    declare @sql as varchar(1000)

    select @sql = ''drop schema '' + name from sys.schemas

    where name not in(''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys''

    ,''db_owner'',''db_accessadmin'',''db_securityadmin'',''db_ddladmin'',''db_backupoperator''

    ,''db_datareader'',''db_datawriter'',''db_denydatareader'',''db_denydatawriter'')

    exec (@sql)

    END'

  • November 3, 2011 at 3:09 am

    #1402534

    It's possible, but you have to declare and assign the variable in the dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply