SQL Table

  • Hi, I would like to rename more than one table (1000+) which starts with WD_TABLENAME. I have the below script but is there any better one to do this?

    select

    'exec sp_rename ''' + [name] + '''WD_'',' + [name] + ''';' as SSQLCmd

    into #temp1

    from sys.tables

    declare @sSQL varchar(8000)

    declare cur1 cursor for

    select SSQLCmd from #temp1

    open cur1

    fetch next from cur1 into @sSQL

    while @@FETCH_STATUS = 0

    begin

    print convert(varchar(25),getdate()) + ' - Executing: ' + @sSQL

    print 'Exec (@sSQL);'

    fetch next from cur1 into @sSQL

    end

    close cur1;

    deallocate cur1;

    drop table #temp1;

  • From your example code it's hard to determine what you are changing the table names to. Below is a script that works without any cursors or loops to rename tables that begin with "WD_" to the same name minus the "WD_". E.g. "wd_table_01" becomes "table_01. "

    -- using my tempdb as a sandbox...

    USE tempdb

    GO

    CREATE TABLE wd_table01(x int)

    CREATE TABLE wd_table02(x int)

    CREATE TABLE wd_table03(x int)

    GO

    IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;

    SELECT TABLE_NAME AS tbl

    INTO #tables

    FROM INFORMATION_SCHEMA.TABLES

    WHERE table_name LIKE 'wd_%'

    DECLARE @sql varchar(8000);

    SELECT @sql=

    (SELECT 'exec sp_rename '''+tbl+''', '''+REPLACE(tbl,'wd_','')+''';'+CHAR(10)

    FROM #tables

    FOR XML PATH(''));

    EXEC(@sql);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

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

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