While setting up for a large migration project involving a third party I had to move a load of tables out of the way to accommodate a sudden change in approach. This happens fairly often with our dev environments. So I thought I’d share a couple of approaches I use to do this. First up , renaming tables using T-SQL and then renaming tables with dbatools
WARNING!!!!!
Renaming any SQL Server needs to be done carefully. Any existing references to the object will break. So any view or stored procedure referencing the table will stop working until you fix it.
This can be a useful trick if you’ve been told you can remove some tables. Rename them, leave them for a day, and then if anyone screams you can rename them back
Renaming tables with T-SQL
This one is good for:
- simple rename options
- when you don’t have dbatools available
To do this we’re going to generate the SQL statements using system views and then running them.
Lets say we have a bunch of tables you want to rename. For the first example we want to prefix a bunch of tables whose names contain ‘user’ with ‘tmp_’ while other testing goes on in the database
The key to this is using sys.all_objects. To get our list of tables to rename, we use some simple T-SQL:
select name from sys.all_objects where name like '%user%' and type_desc='USER_TABLE';
Adding something to specify the type of object is ALWAYS a good idea, just to avoid trying to rename a stored procedure or system table.
Now use the T-SQL to build up sp_rename statements:
select ''exec sp_rename '''+name+''', ''tmp_'+name+''';' where name like '%user%' and type_desc='USER_TABLE';
Which will give you a nice little list of SQL statements that you can then copy and paste into a query window to run.
Renaming tables with dbatools
Whilst the dbatools module doesn’t have a Rename-DbaTable command (yet!), you can still use dbatools to rename tables with a little bit of added Powershell
The first part of the pipeline is going to be grab all the tables from the database using Get-DbaDbTable. Then pass that into Where-Object to filter down to the tables we want. Then into a ForEach loop for the actual rename which we do with the SMO rename() method:
$instance = 'server1\instance'
$db = 'testdb'
Get-DbaDbTable -Sqlinstance $instance -Database $db | Where-Object {$_.name -like '*user*'} | ForEach {$_.Rename('tmp_'+$_.name)}
Being Powershell there’s a lot of options you can use to create the new name, which makes this more flexible that the T-SQL version above.