Post reply

Help with sp_msforeachtable 'sp_spaceused "?"'

  • April 29, 2010 at 5:04 am

    #1159547

    Jinx-640161 (4/29/2010)

    I get a syntax error in the insert statement - it does not like :@SERVER_DB1.dbo.sp_msforeachtable

    The error I get is: Incorrect syntax near '.'.

    Can one use a variable like this?

    No you can not.. U could still utlize dynamic sql to run the above

    like

    DECLARE @QUERY VARCHAR(4000)

    SET @QUERY = 'INSERT INTO #DB1 EXECUTE '+@SERVER_DB1+'.dbo.sp_msforeachtable ''sp_spaceused "?"'''

    PRINT @QUERY

    exec (@QUERY)

  • April 29, 2010 at 5:17 am

    #1159553

    DECLARE @DatabaseName SYSNAME;

    SET @DatabaseName = QUOTENAME(N'AdventureWorks');

    DECLARE @Data

    TABLE (

    table_name SYSNAME PRIMARY KEY,

    row_count INTEGER NOT NULL,

    reserved VARCHAR(50) NOT NULL,

    data VARCHAR(50) NOT NULL,

    index_size VARCHAR(50) NOT NULL,

    unused VARCHAR(50) NOT NULL

    );

    INSERT @Data

    EXECUTE (

    @DatabaseName + N'.sys.sp_msforeachtable ''sp_spaceused "?"'''

    );

    SELECT *

    FROM @Data;

    edit: ColdCoffee beat me to it

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • April 29, 2010 at 6:08 am

    #1159588

    Perfect!

    Thanks to both of you. 😀

Viewing 3 posts - 1 through 4 (of 4 total)

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