Post reply

How to use locally declared parameters in index rebuild statement

  • April 19, 2016 at 3:23 am

    #310039

    Hi,

    I have a TSQL script in which I am assigning some values to locally declared parameters.

    The parameter values are being displayed correctly. However I now need to use these parameters in my rebuild index statement.

    So if I do:

    print 'table name: ' + @table_Name;

    It is displaying the tablename correctly when I run my script

    But if I use this parameter along with my other parameters in my index rebuild statement it errors:

    Error : Cannot find the object "@db_name..@table_name" because it does not exist or you do not have permissions.

    I am running my index rebuild statement like this:

    alter index [@indexname] on [@db_name]..[@table_name] rebuild;

    So my question is, how can I use locally declared parameters in the index rebuild statement?

    Thanks.

  • April 19, 2016 at 3:31 am

    #1872974

    You need to use dynamic SQL, something like this:

    DECLARE @index sysname

    DECLARE @DB sysname

    DECLARE @schema sysname

    DECLARE @table sysname

    DECLARE @sql nvarchar(1000)

    -- Now assign values to the first four variables

    SET @sql = N'ALTER INDEX ' + @index + N' ON ' + @DB + N'.' + @schema +N'.' + @table + N' REBUILD'

    EXEC sp_executesql @sql

    However, I'd recommend using Ola Hallengren's scripts or something else that's ready-rolled, instead of trying to reinvent the wheel here.

    John

  • April 19, 2016 at 3:45 am

    #1872978

    Thanks for that, will give it a try.

    Yes I will check out Ola's script.

  • April 19, 2016 at 8:15 am

    #1873080

    Also related to the index rebuild statement, how can I rebuild more than one index from the same table using the same alter index statement?

    I have tried using a comma between the index names but this hasn't worked.

    Alter index [Indexname1],

    [Indexname2]

    on database.schema.table rebuild;

    Thanks.

  • April 19, 2016 at 8:19 am

    #1873083

    One index per statement is the limit. You can't combine indexes in one statement.

    The single exception: rebuilding a clustered index automatically rebuilds all nonclustered indexes. But then you still only have the clustered index in your statement.

  • April 19, 2016 at 8:23 am

    #1873086

    Ok thanks for the clarification.

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

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