sp_MSForeachdb then sp_MSforeachtable

  • Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm sure it's missing a quote (or has one too many) but I'm a little brain dead at the moment. Server moves this weekend so I know I won't have time to fool with this until Monday, if all goes well. If anyone fixes this or points me in the right direction, thanks. The table schema is below as well. Have a good weekend.

    CREATE PROCEDURE usp_DataRowCounts AS

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    -- Declare local variables

    EXEC master..sp_MSForeachdb '

    USE [?]IF DB_ID(''?'')>4

    BEGIN

    INSERT INTO DBMaint..tblTableData

    EXEC sp_MSforeachtable 'sp_spaceused ''?''

    END'

    GO

    CREATE TABLE tblTableData

    (vchTableName VARCHAR(100) NOT NULL,

    intRows INT NOT NULL,

    vchReservedSpace VARCHAR(100) NOT NULL,

    vchData VARCHAR(100) NOT NULL,

    vchIndexSize VARCHAR(100) NOT NULL,

    vchUnusedSpace VARCHAR(100) NOT NULL)

    -- You can't be late until you show up.

  • tosscrosby (3/28/2008)


    Last posting of the week for me. Trying to get this to work and it's giving a syntax error. I'm sure it's missing a quote (or has one too many) but I'm a little brain dead at the moment. Server moves this weekend so I know I won't have time to fool with this until Monday, if all goes well. If anyone fixes this or points me in the right direction, thanks. The table schema is below as well. Have a good weekend.

    CREATE PROCEDURE usp_DataRowCounts AS

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    -- Declare local variables

    EXEC master..sp_MSForeachdb '

    USE [?]IF DB_ID(''?'')>4

    BEGIN

    INSERT INTO DBMaint..tblTableData

    EXEC sp_MSforeachtable 'sp_spaceused ''?''

    END'

    GO

    CREATE TABLE tblTableData

    (vchTableName VARCHAR(100) NOT NULL,

    intRows INT NOT NULL,

    vchReservedSpace VARCHAR(100) NOT NULL,

    vchData VARCHAR(100) NOT NULL,

    vchIndexSize VARCHAR(100) NOT NULL,

    vchUnusedSpace VARCHAR(100) NOT NULL)

    My first best guess would be that you're using the same substitution character for both ms_foreachdb and ms_foreachtable. ms_foreachdb will replace all the ?'s contained in it's query with the database name.

    However, after correcting that and checking over and over, I'm thinking the USE statement is pissing off the whole process. Since sp_msforeachdb creates a cursor anyways, why not make your own? You can do more that way.

    Try this

    CREATE PROCEDURE usp_DataRowCounts AS

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    declare csr_db cursor for

    select [name] from master..sysdatabases

    where [dbid] >4

    declare @cmd varchar(max)

    declare @dbs varchar(256)

    open csr_db

    fetch next from csr_db into @dbs

    while @@fetch_status = 0

    BEGIN

    set @cmd = @dbs + '..sp_msforeachtable '' insert into DBMaint..tblTabledata(vchtablename,introws,vchreservedspace,vchData,vchIndexSize,vchUnusedSpace) exec sp_spaceused ''''?'''' '''

    exec(@cmd)

    set @cmd = 'update DBMaint..tblTabledata set vchdatabasename = ''' + @dbs + ''' where vchdatabasename is null'

    exec(@cmd)

    fetch next from csr_db into @dbs

    END

    close csr_db

    deallocate csr_db

    I changed the table structure to also include the database name.

    CREATE TABLE tblTableData

    (vchdatabasename varchar(256) NULL,

    vchTableName VARCHAR(100) NOT NULL,

    intRows INT NOT NULL,

    vchReservedSpace VARCHAR(100) NOT NULL,

    vchData VARCHAR(100) NOT NULL,

    vchIndexSize VARCHAR(100) NOT NULL,

    vchUnusedSpace VARCHAR(100) NOT NULL)

    Substitute varchar(8000) for varchar(max) in non SQL2k5 systems



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • I have something similar to what you've created (I'll admit, your's is better looking!) using two cursors, one for the database and then one for the table names.

    Then I kept reading about the undocumented stored procs and also about how cursors are BAD and started to fool around with the FOREACH sprocs. Didn't think about reusing the same substitution characters. I bet that's a huge part of my problem. Is any substitution characther valid? I appreciate the response. Thanks.

    -- You can't be late until you show up.

  • You can use whatever you like for the substitution character, but if you use something other than ? you need to specify it with a second parameter to the sp_msforeachdb or sp_msforeachtable sp with the parameter @replacechar.

    sp_helptext 'sp_msforeachdb'

    or

    sp_helptext 'sp_msforeachtable'

    However, each of the sp_msforeach stored procs creates a cursor anyway and you're stuck with the capabilities programmed into MS's undocumented SPs. By writing your own outer loop via your own cursor instead of MS's... you can slip in the database name, which you'll probably want.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • Agreed. Thanks for your help.

    -- You can't be late until you show up.

  • try this...

    declare @command1 varchar(8000)

    select @command1 =

    '

    IF ''@'' ''master'' AND ''@'' ''model'' AND ''@'' ''msdb'' AND ''@'' ''tempdb''

    begin

    use [@] execute sp_MSForEachTable ''print ''''?'''' ''

    end

    '

    exec sp_MSforeachdb @command1, '@'

    Prakash Heda
    Lead DBA Team - www.sqlfeatures.com
    Video sessions on Performance Tuning and SQL 2012 HA

  • Just to mention that if the database name contains - like test-test then the script will fail.

    The database name needs to be enclosed between square brackets.

    I suggest this modified version

    USE [TempPAST]

    GO

    /****** Object: StoredProcedure [dbo].[usp_DataRowCounts] Script Date: 08/03/2010 12:22:42 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    alter PROCEDURE [dbo].[usp_DataRowCounts] AS

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    declare csr_db cursor for

    select [name] from master..sysdatabases

    where [dbid] >4

    declare @cmd varchar(max)

    declare @dbs varchar(256)

    declare @dbs2 varchar(256)

    open csr_db

    fetch next from csr_db into @dbs

    while @@fetch_status = 0

    BEGIN

    set @dbs2 = '[' + @dbs + ']'

    set @cmd = @dbs2 + '..sp_msforeachtable '' insert into TempPAST..tblTabledata(vchtablename,introws,vchreservedspace,vchData,vchIndexSize,vchUnusedSpace) exec sp_spaceused ''''?'''' '''

    print @cmd

    exec(@cmd)

    set @cmd = 'update TempPAST..tblTabledata set vchdatabasename = ''' + @dbs2 + ''' where vchdatabasename is null'

    print (@cmd)

    exec(@cmd)

    fetch next from csr_db into @dbs

    END

    close csr_db

    deallocate csr_db

    Thanks,

    Davide.

  • I just had to have fun with this.

    I had to try like 15 different things to whack any sort of loop. Anyhow this is really nice and usable. Enjoy :

    USE master

    GO

    SET STATISTICS IO, TIME OFF

    SET NOCOUNT ON

    SET IMPLICIT_TRANSACTIONS ON

    GO

    IF OBJECT_ID('dbo.spaceused', 'U') IS NULL

    BEGIN

    CREATE TABLE dbo.spaceused (

    DbName sysname DEFAULT(''),

    tblName sysname,

    Row_count INT ,

    Reserved VARCHAR(50),

    data VARCHAR(50) ,

    index_size VARCHAR(50),

    unused VARCHAR(50),

    PRIMARY KEY CLUSTERED (DbName, tblName)

    );

    END

    ELSE

    BEGIN

    --DROP TABLE dbo.spaceused

    TRUNCATE TABLE dbo.spaceused

    END

    COMMIT

    GO

    DECLARE @Cmd VARCHAR(8000)

    SET @Cmd = 'USE [?];

    IF ''?'' NOT IN (''tempdb''

    --, ''master'', ''model'', ''msdb''

    )

    BEGIN

    --PRINT ''?''

    DECLARE @InnerCmd VARCHAR(8000)

    SET @InnerCmd = ''

    EXEC sp_spaceused '''''' + CHAR(63) + ''''''''

    INSERT INTO master.dbo.spaceused(tblName, Row_count,Reserved,data,index_size,unused)

    EXEC sp_MSforeachtable @InnerCmd

    UPDATE master.dbo.spaceused SET DbName = ''?'' WHERE DbName = ''''

    END

    '

    --PRINT @Cmd

    --EXEC sp_MSforeachtable @Cmd

    EXEC sp_MSforeachdb @Cmd

    SELECT

    DbName

    , tblName

    , Row_count

    , CONVERT(BIGINT, REPLACE(Reserved, ' KB', '')) / 1024 AS MB_Reserved

    , CONVERT(BIGINT, REPLACE(data, ' KB', '')) / 1024 AS MB_data

    , CONVERT(BIGINT, REPLACE(index_size, ' KB', '')) / 1024 AS MB_index_size

    , CONVERT(BIGINT, REPLACE(unused, ' KB', '')) / 1024 AS MB_unused

    FROM

    dbo.spaceused

    WHERE Row_count > 0

    ORDER BY

    DbName

    , MB_Reserved DESC

    , Row_count DESC

    COMMIT

  • @ninja's_RGR'us: I really like that solution and have already been putting it to use, but I'm trying to make one slight modification and am having some difficulties. Hopefully someone can help me out here.

    What I'd like to do is use that same solution but find a way to have it detect all linked servers and run the same thing on all the linked servers as well. Unfortunately when using these stored procedures, any variables passed in are only good for the duration of the procedure and we can't pass a variable for the linked server name unless we declare and define it within the same variable that we are passing to sp_MSforeachdb (@Cmd in this case). I know this is do-able but I'm having trouble doing it properly. Any help would be much appreciated.

    Thanks!

    -jared

  • Never done that and I don't think this script is the way to go for that.

    If you start a new thread you'll get a tone more help.

    Good search.

  • To get dB size with Path for each dBs on server.

    SELECT d.name,

    ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs,

    PHYSICAL_NAME

    FROM master.sys.master_files mf

    INNER JOIN master.sys.databases d ON d.database_id = mf.database_id

    WHERE mf.database_id > 4 -- Skip system databases

    GROUP BY d.name, PHYSICAL_NAME

    ORDER BY d.name

  • YOU CAN TRY WITH THIS

    CREATE PROCEDURE usp_DataRowCounts AS

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    EXEC sp_MSForeachdb @replacechar='%', @command1= " USE %

    IF DB_ID('%')>4 begin

    INSERT TEST_table

    EXEC sp_msforeachtable 'sp_spaceused ''?'''

    end"

Viewing 12 posts - 1 through 11 (of 11 total)

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