Ever need to rename all objects in a database? Yeah, okay… it doesn’t happen very often, but when it does, it can be time consuming. This recently came up as something I needed to do. When you consider all the tables involved and you add in defaults, indexes, and foreign keys, well… you can imagine how the number of objects adds up quickly. After doing a few tedious renames, it occurred to me that I could write a script to generate the rename scripts for me. Work smarter, not harder, right? For anyone interested, here’s the script.
Select name , [object_id] , Case When [type] = 'U' Then 'Execute sp_rename N''' + name + ''', N''old_' + name + '''' When [type] IN ('D', 'PK', 'F') Then 'Execute sp_rename N''' + name + ''', N''old_' + name + ''', N''OBJECT''' End As 'renameScript' , Case When parent_object_id > 0 Then 0 Else 1 End As 'sortOrder' From sys.objects Where [type] In ('D', 'PK', 'U', 'F') Union ALL Select i.name , o.[object_id] , 'Execute sp_rename N''' + o.name + '.' + i.name + ''', N''old_' + i.name + ''', N''INDEX''' As 'renameScript' , 0 As 'sortOrder' From sys.indexes As i JOIN sys.objects As o On i.object_id = o.object_id Where i.is_primary_key = 0 -- exclude PKs, we take care of that above AND i.type <> 0 -- exclude heaps AND o.type Not In ('S', 'IT') -- exclude system & internal tables Order By sortOrder;
Be forewarned that I only tested this on a couple of databases, but it seemed to run without problem for tables, indexes, primary keys, defaults, and foreign keys. The sortOrder column is there only to ensure that table renames are performed last. Otherwise, your index renames would fail. This will only run on SQL Server 2005 or 2008. If you have any problems with the script, please let me know.