Renaming a column without breaking the scripts and

Question

I want to modify a column name to new name present in a table

but here problem i want to manually modify the column name present in Triggers or SP's.

Is there a any better way of doing it.

To rename a column am using this

sp_RENAME 'Tablename.old_Column', 'new_column' , 'COLUMN';

similarly how can i do it for triggers or SP's.? without opening each script?

Answer 1

1. you can do what @GorDon suggested.

2. Apart from this,you can also play with this query,

select o.name, sc.* from sys.syscomments sc inner join sys.objects o on sc.id=o.object_id where sc.text like '%oldcolumnname%'

this will return list of all proc and trigger.Also you can modify filter to get exact list.then it will be very easy for you to modify,manually.

But whatever you decide,don't simply drop old column. To be safe,even keep back up.

Answer 2

Well, there are a bunch of 3rd party tools that are promising this type of "safe rename", some for free and some are not:

• ApexSQL has a free tool for that, as MWillemse wrote in his answer,
• RedGate have a commercial tool called SQLPrompt that also have a safe renaming feture, However it is far from being free.
• Microsoft have a visual studio add-in called SQL Server Data Tools (or SSDT in the short version), as Dan Guzman wrote in his comment.

I have to say I've never tried any of these specific tools for that specific task, but I do have some experience with SSDT and some of RedGate's products and I consider them to be very good tools. I know nothing about ApexSQL.

Another option is to try and write the sql script yourself, However there are a couple of things to take into consideration before you start:

• Can your table be accessed directly from outside the sql server? I mean, is it possible that some software is executing sql statement directly on that table? If so, you might break it when you rename that column, and no sql tool will help in this situation.
• Are your sql scripting skills really that good? I consider myself to be fairly experienced with sql server, but I think writing a script like that is beyond my skills. Not that it's impossible for me, but it will probably take too much time and effort for something I can get for free.

Should you decide to write it yourself, there are a few articles that might help you in that task:

First, Microsoft official documentation of sys.sql_expression_dependencies.
Second, an article called Different Ways to Find SQL Server Object Dependencies that is written by a 13 years experience DBA, and last but not least, a related question on StackExchange's Database Administrator's website.

You could, of course, go with the safe way Gordon Linoff suggested in his comment, or use synonyms like destination-data suggested in his answer, but then you will have to manually modify all of the columns dependencies manually, and from what I understand, that is what you want to avoid.

Answer 3

This suggestion relates to Oracle DB, however there may be equivalent solutions in other DBMS's.

A temporary solution to your issue is to create a pseudocolumn. This solution looks a little hacky because the syntax for a pseudocolumn requires an expression. The simplest expression I can think of is the case statement below. Let me know if you can make it more simple.

  ALTER TABLE <<tablename>> ADD (
   <<new_column_name>> AS (
    CASE
      WHEN 1=1 THEN <<tablename>>.<<old_column_name>>
    END)
  );

This strategy basically creates a new column on the fly by evaluating the case statement and copying the value of <<old_column_value>> to <<new_column_value>>. Because you are dynamically interpolating this column there is a performance penalty vs just selecting the original column.

The one gotcha is that this will only work if you are duplicating a column once. Multiple pseudocolumns cannot contain duplicate expressions in Oracle.

The other strategy you can consider is to create a view and you can name the columns whatever you want. You can even INSERT/UPDATE/DELETE (execute DML) against views, but this would give you a whole new table_name, not just a new column. You could however rename the old table, and name your view the same as your old table. This also has a performance penalty vs just accessing the underlying table.

Answer 4

1. Renaming the Table column
2. Deleting the Table column
3. Alter Table Keys

Best way use Database Projects in Visual Studio. Refer this links

link 1

link 2

Answer 5

You might want to replace that text in definition. However, you will be needing a dedicated administrator connection in sql server. Versions also vary in setting up a dedicated administrator connection. Setting up the startup parameter by adding ;-T7806 under advanced. And by adding Admin: before the servername upon logging in. By then, you may be able to modify the value of the definition.