Post reply

Passing Parameters in a DBCC SHOWCONTIG statement

  • December 10, 2007 at 10:39 am

    #181961

    I am modifying the delivered BOL DBCC SHOWCONTIG script so it can work with all tables, not just those owned by the dbo role.

    I added a new parameter and now want to include it into the DBCC SHOWCONTIG script, it parses correctly but doesn't execute

    here is the original line

    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

    WITH TABLERESULTS, ALL_INDEXES');

    I want to add @tableschema I tried this:

    EXEC ('DBCC SHOWCONTIG (''' + @tableschema + '''.''' + @tablename + ''')

    WITH TABLERESULTS, ALL_INDEXES')

    My syntax is incorrect on the amount of quotes I need to pass the @tableschema through

    thanks

  • December 10, 2007 at 10:56 am

    #758686

    here u go...

    declare @test-2 varchar(2000)

    set @test-2 = 'dbo.TAccountVendorContact'

    DBCC SHOWCONTIG (@test) WITH TABLERESULTS, ALL_INDEXES

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

  • December 10, 2007 at 11:34 am

    #758704

    Thanks but I cannot do that...I am not giving you all the information but the top of the query declares all the tablenames from a given database via a cursor.

    So I cannot have a set command since I need it to loop through all tables.

    any other ideas?

  • December 10, 2007 at 11:48 am

    #758708

    limitation is you can't concatinate 2 variables in a dbcc command.....

    concatinate them before in another variable and then just pass that variable....

    set @test-2 = @tableschema + '.' + @tablename

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

  • December 10, 2007 at 12:00 pm

    #758715

    Prakash

    Thank you! the double variable was it..I altered the script to declare test, put the SET in the cursor loop and all works well!!

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

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