Listing Tables and Indexes

  • SQLEnthusiastic

    Mr or Mrs. 500

    Points: 593

    Comments posted to this topic are about the item Listing Tables and Indexes

  • eddie 52254

    Valued Member

    Points: 52

    Can someone tell me how to rename all DEV tables and it's indexes to LIVE nomenclature? Ex: ProductsDev to ProductsLive and all indexes renamed to Live extension as well.

    Here is a query selecting what DEV tables and their indexes. I just need to put it in a loop for rename. Doing so keeps naming conventions correct and makes a generic method so if indexes are added sql code doesn't have to be maintained.

    Any help is greatly appreciated.

    /* Loop through all DEV tables and it's indexes */

    SELECT b.name 'Table', a.name 'Index'

    FROM sysindexes a, sysobjects b

    WHERE a.id = b.id

    and b.name like '%Dev'

    and (a.name like 'PK%' or a.name like 'IX%')

    and b.type = 'U'

    and indid NOT IN (0, 255)

    order by a.name

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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