Renaming table column and having it propagate to dependent views

Question

If we want to change the name of MyColumnName to MyAlteredColumnName...

...and we have a SQL Server 2008 table that looks like:

MyTable
  MyColumnName

and a view that references the underlying column:

CREATE VIEW MyDependentView WITH SCHEMABINDING
AS
SELECT ..., MyTable.MyColumnName

We end up following this procedure:

1. Dropping the View
2. Altering MyTable.MyColumnName to MyTable.MyAlteredColumnName
3. Recreating the View with a reference to MyAlteredColumnName

We do this with migrator dot net.

Is there a better way to do this? Is there T-SQL that will alter a view column name? Or any support in SQL Server 2008 for automagically tying the columns together?

Answer 1

Without the use of a third-party tool, this is one of the only ways to do it. You can obviously also use ALTER VIEW instead of a DROP and CREATE.

It should be noted that Red-Gate makes a tool called SQL Refactor which will automate this sort of change (no I do not work for them). I'm sure there are other similar database refactoring tools out there.

Answer 2

Use sp_refreshview:

EXEC sp_refreshview @viewName

If you want to refresh all your views, you'll have to iterate over a loop of them, which means dynamic SQL.

And if you layered them (a view is dependent on another view - bad), you'll have to refresh the parent first...

Answer 3

If it's a SELECT * view, you can call sp_refreshview, as OMG_Ponies suggested. It will recompile the view and update the column metadata appropriately. This is one area where judicious use of SELECT * could have benefits, if used appropriately within a coherent scheme.

Otherwise, you must redefine the view. Any explicit references to the old column name will now raise an error.

Ah, one more alternative:

EXEC sp_rename 'MyTable.MyColumnName', 'MyAlteredColumnName'
ALTER TABLE MyTable ADD MyColumnName AS MyAlteredColumnName
EXEC sp_rename 'MyView.MyColumnName', 'MyAlteredColumnName'

It's a hack, and it's dangerous, since the stored view definition will now be out of sync with the view metadata. And you have littered the db with superfluous computed columns.

But it will work (until you forget what you did, or someone else has to maintain the system, and things start to break mysteriously).