Renaming a table column and distributing it to dependent views

If we want to change the name MyColumnNameto MyAlteredColumnName...

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

MyTable
  MyColumnName

and a view that refers to the following column:

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

As a result, we perform the following procedure:

  • Delete view
  • Changing the name MyTable.MyColumnName to MyTable.MyAlteredColumnName
  • Recreating the view with reference to the name MyAlteredColumnName

We do this with a network-mitrator.

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

+3
source share
4

. ALTER VIEW DROP CREATE.

, Red-Gate SQL Refactor, (, ). , .

+5

sp_refreshview:

EXEC sp_refreshview @viewName

, , SQL.

( - ), ...

+2

SELECT *, sp_refreshview, OMG_Ponies. . , SELECT * , .

. .

Ah, :

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

, , . db .

( , , - , ).

+2
source

I use a third-party tool for this, it has not failed me yet. This is ApexSQL Refactor, here is a practical tutorial

How to rename a column without splitting the SQL database

0
source

Source: https://habr.com/ru/post/1762025/


All Articles