2

Often in my c# code, I can change the name of a variable, and all references to it will change as well.

Is there a similar feature that allows me to do this with objects in SQL servers? Some of my tables, columns, and stored procedures are in need of renaming, but of course I want to keep it all functioning.

4 Answers 4

1

Q: Are there apps and features to do this?

Yes.

Q: Should you use them?

Probably not. The problem with SQL is that it's extraordinarily hard to catch all of the references to object names.

One example is dynamic SQL - the technique of building strings, and then executing the string as a query. My stored procedure might have code like this:

DECLARE @StringToExecute
SET @StringToExecute = 'SELECT * FROM dbo.'
SET @StringToExecute = @StringToExecute + 'MyTableName;'
EXEC (@StringToExecute);

If you want to rename dbo.MyTableName as something else, you're going to have to look at strings inside stored procedures and app code. That's really hard to do because this is also valid:

DECLARE @StringToExecute
SET @StringToExecute = 'USE Database1;'
SET @StringToExecute = @StringToExecute = 'SELECT * FROM dbo.'
SET @StringToExecute = @StringToExecute + 'MyTableName;'
EXEC (@StringToExecute);
SET @StringToExecute = 'USE Database2;'
SET @StringToExecute = @StringToExecute = 'SELECT * FROM dbo.'
SET @StringToExecute = @StringToExecute + 'MyTableName;'

That builds two different queries for two different MyTableName's in two different databases.

Another example is linked server queries. Any of your other SQL Servers may be set up with linked servers, synonyms, views, etc to query your existing objects.

So object name changes and table refactoring is really something you can only do when:

  • Your app is fairly young
  • You have very tight control over the queries
  • You know everywhere that queries come in from
0

If you are talking about Linq to SQL and changing the LINQ object definitions in visual studio, then the typical way to update these is to delete and re-add them in the designer then find their references and update them to the new names. If you use DI and something like the repository pattern, this is pretty easy as the references are all in one area and not spread out all over your code.

0

If you want to buy something, looks like Red Gate has something right up your alley.

0

I advise you to try SQL Complete It allows you to improve code readability by giving meaningful and self-explaining names to tables, columns, temporary tables and columns, views, stored procedures, and functions. SQL Complete will automatically correct all references to the renamed objects.

With the help of the rename functionally, you can also change the names of local and SQLCMD variables in a couple of seconds. Preview code changes in a convenient tree-view of the Rename dialog window.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .