CODEWITHSUNDEEP
t-sqlsql-server-2005
user1804387
user1804387

Reputation: 519

Query to Rename Tables

In SQL Server 2005 I am looking to rename a lot of tables using a wildcard, for example, the table names are:

OrderItem
OrderItems
OrderItemss

I would like them to be renamed to:

OrderItem2
OrderItems2
OrderItemss2

Can this be done using a wildcard so I don't have to specify the full table name? Thanks.

Upvotes: 0

Views: 2727

Answers (4)

ErikE
ErikE

Reputation: 50271

It's this simple:

DECLARE @SQL nvarchar(max);

SELECT
   @SQL = Coalesce(@SQL + Char(13) + Char(10), '') + 'EXEC sp_rename '''
   + Replace(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME), '''', '''''')
   + ''', ''' + Replace(TABLE_NAME + '2', '''', '''''') + ''';'
FROM INFORMATION_SCHEMA.TABLES T
WHERE
   T.TABLE_TYPE = 'BASE TABLE'
   AND T.TABLE_NAME LIKE 'OrderItem%'
;

EXEC (@SQL);

Upvotes: 1

d.popov
d.popov

Reputation: 4255

You can get the list of all the user tables filtered by what you need for example with this query:

select name from sysobjects where type = 'U' and name like 'user%'

then use the sp_executesql and a cursor to the results of the query to execute dynamic SQL (MSDN) to perform the actual renaming.

Hope that helps.

Upvotes: 0

aweis
aweis

Reputation: 5616

You can use the system tables to select all table names matching you pattern, e.g. 'OrderItem%', or more sophisticated. Then using dynamic sql to update the names using the exec SP_RENAME 'old_name' 'new_name'.

However, this will not take effect on stored procedures querying the tables. Those stored procedures will need to be recompiled after the rename is complete.

Solution Sql

Okay, here is the entire code. It is based on that table names starts with "OrderItem" and that they all shall have the original name with "2" postfixed. I create a table variable holding the found tables before renaming them. If you "uncomment" the select statement and only execute down to that statement you can see what tables are select to be renamed, and to what they will be renamed. NOTE that i am only selecting tables from the dbo schema.

declare @OldTableName nvarchar(max)
declare @NewTableName nvarchar(max)
declare @SqlStmt nvarchar(max)

declare @Tables table
(
    OldTableName nvarchar(max),
    NewTableName nvarchar(max)
)

insert into @Tables (OldTableName, NewTableName)
select
    name as OldTableName ,
    name + '2' as NewTableName
from sys.tables
where name like 'OrderItem%'
    and schema_id = SCHEMA_ID('dbo')

-- select * from @Tables

declare cur cursor for
select OldTableName,
NewTableName
from @Tables

open cur
while (1=1)
begin

    fetch next from cur into @OldTableName, @NewTableName

    if @@FETCH_STATUS <> 0
        break

    set @SqlStmt = N'sp_rename ''' + @OldTableName + ''', ''' + @NewTableName + ''''

    exec sp_executesql @SqlStmt

end

close cur
deallocate cur

NEW 24apr2013

Select statement that includes the schema name and not only looks in dbo schema

select
    s.name + '.' + t.name as OldTableName,
    t.name + '2' as NewTableName
from sys.tables as t
inner join sys.schemas as s on t.schema_id = s.schema_id
where t.name like 'OrderItem%'

Added

Look in sys.tables for all tables (remember to only select those in the schema you want to rename). Insert the names into a temporary table, along with the new table names you give them, then iterate over each of them generating a sql string with the call to the sp_rename and use the exec sp_executesql 'SQL STMT' to make the actual execution.

Upvotes: 2

greg dorian
greg dorian

Reputation: 134

first that all find the number of tables in your data base (I have 3) and put this code in a stored procedure, also it can put parameters!!

 `alter procedure [dbo].[changetableName]
  as
  declare @sql varchar(8000), @table varchar(1000), @oldTable varchar(1000),  
  @newTable     
  varchar(1000);
  declare @Inc integer;
  set @Inc=1;

  set @oldTable = '%OrderItem%'
  set @newTable = 'OrderItem_n'

  while exists(SELECT object_id, t.name AS table_name FROM sys.tables AS t where
  t.name Like @oldTable) and (@Inc<=3)
   begin

    select @table = name from sys.tables 
   where object_id in(select object_id from sys.tables as t where t.name like 
        @oldTable )

 set @sql = 'sp_rename ' + @table + ' , ' + @newTable + CONVERT(varchar(2),@Inc) 

    exec(@sql)
   SET @Inc = @Inc + 1;
  select @sql
  end`

Upvotes: -1

Related Questions