SQL Server Portal

Recently, I came across with a nice question in a forum, how to mass/bulk rename sql tables. The issue was, some tables were imported from some sources but their names were not as per the database standards. Therefore standardization was required.

Let me explain this with an example :

Given below are the table names after imports :

1. dbo.test_Item
2. dbo.test_Purchase
3. dbo.test_Inventory
4. dbo.test_Sales etc.

But the table name should be like this :

1. dbo.Item
2. dbo.Purchase
3. dbo.Inventory
4. dbo.Sales etc.

SOLUTION :
Given below is the solution that will dynamically make sql statements to rename the tables. And if you execute the result set of the below query it will rename all your tables having “_” in the name.
You can use replace “_” with any other character as per the requirement.

Declare @char varchar(1)
Set @char='_'
SELECT 'EXEC sp_rename ''' + NAME +  ''', ' + ''''
+ Substring(name ,CharIndex(@char,name)+1,LEN(name)) + ''''
As [Script]
FROM dbo.sysobjects
WHERE xtype = 'U' And CharIndex(@char,name)>0
Order By [Name]
--OUTPUT

Let me know if you have better solution.