Musings of a Data professional

Stuart Moore

Month: August 2021

Bulk renaming SQL Server tables with T-SQL or dbatools

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.

 

Data Platform Discovery Day returns

After the great feedback we got last year for the first Data Platform Discovery day we’re going to be doing another one on September 21st 2021.

We’re still going to have great intro sessions to Data Platform topics help you kickstart your Data Platform career or just so you know what your techies are talking about.

But this year we’re also going to be having Panel discussions between the sessions. We’ll be picking a range of people to discuss Data Platform topics, different ways to start you Data Platform career, and also how people can make the Data Platform community and workplaces better spaces for everyone

Also this year we’re going to be running a single day that goes from 10:00 till 19:00 UTC. This allows us to cover a wide range of Timezones and gives audiences a chance to maybe see speakers they wouldn’t normally get to.

More details will be announced on the web site as we work them out – https://dataplatformdiscoveryday.com/

Our Call for Speakers is now open! So if you have some great intro/level 100 sessions you feel would help a someone starting out then we’d love for you to submit. Do you have something you wish someone would have told you when you first started? Then submit it here – https://sessionize.com/data-platform-discovery-day-2021/

If you’d like to take part in the panels, then please read through the descriptions: DPDD 2021 panel descriptions. We’re looking for anyone who can offer insights or commentary on any of these topics. If that sounds like you then please submit a ‘session’ with the title of the panel your interested in, and use the abstract box to tell us a bit about yourself and why you’d be great on our panel

Call for Speakers closes on the 31st August and we will be notifying everyone successful or unsuccessful as soon as we can.

Powered by WordPress & Theme by Anders Norén