Fix dependencies during or after renaming

The previous post regarding dependencies reminded me of another bugger I come
up with from time to time. SQL Server allows us to rename objects via a
system stored procedure (I usually use an ADP front-end that presumably calls
the same procedure), but after the rename, I find singnificant strangeness.

For instance, triggers are still connected to the tables, but the trigger text
still refers to the old table name. Views and stored procedures dependent on
the table are broken, and are not fixed by even by restarting the server,
though I thought the cached plans were supposed to be dumped on shut-down,
then recreated on demand later.

Is there some tool or technique to rename a table and automatically detect and
properly drop/rebuild dependent triggers, views, stored procedures,
relationships, and anything else I'm not thinking of?

Oh yeah, and there are extended properties to deal with as well, but I guess
that's intractible unless we know what client build them (e.g. Access ADP),
and what it expects to be recorded there, but I suppose it would be nice to
just dump them rather than leave them there possibly corrupted from a client's
persective.Steve Jorgensen wrote:

> The previous post regarding dependencies reminded me of another bugger I come
> up with from time to time. SQL Server allows us to rename objects via a
> system stored procedure (I usually use an ADP front-end that presumably calls
> the same procedure), but after the rename, I find singnificant strangeness.
> For instance, triggers are still connected to the tables, but the trigger text
> still refers to the old table name. Views and stored procedures dependent on
> the table are broken, and are not fixed by even by restarting the server,
> though I thought the cached plans were supposed to be dumped on shut-down,
> then recreated on demand later.
> Is there some tool or technique to rename a table and automatically detect and
> properly drop/rebuild dependent triggers, views, stored procedures,
> relationships, and anything else I'm not thinking of?
> Oh yeah, and there are extended properties to deal with as well, but I guess
> that's intractible unless we know what client build them (e.g. Access ADP),
> and what it expects to be recorded there, but I suppose it would be nice to
> just dump them rather than leave them there possibly corrupted from a client's
> persective.

One thing I know about views, if you rename a view, you best double
click it in EM and rename it in the create statement as well otherwise
it will get renamed back to the old name if ever you script it.
(checks his 4 views, nope, that's not why that happened).|||On Fri, 23 Jan 2004 11:01:27 GMT, Steve Jorgensen <nospam@.nospam.nospam>
wrote:

>The previous post regarding dependencies reminded me of another bugger I come
>up with from time to time. SQL Server allows us to rename objects via a
>system stored procedure (I usually use an ADP front-end that presumably calls
>the same procedure), but after the rename, I find singnificant strangeness.
>For instance, triggers are still connected to the tables, but the trigger text
>still refers to the old table name. Views and stored procedures dependent on
>the table are broken, and are not fixed by even by restarting the server,
>though I thought the cached plans were supposed to be dumped on shut-down,
>then recreated on demand later.

Oh yeah, I guess the views and procedures don't get the table names updated in
the code, so that one's obvious (though I'd still like an automated way to fix
it). What -was- confusing about that was why renaming a -column- causes
problems for stored procedures and views, even after a restart, even if the
procedure or view is uing an asterisk in the select and not directly
referencing the column in question.|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> Oh yeah, I guess the views and procedures don't get the table names
> updated in the code, so that one's obvious (though I'd still like an
> automated way to fix it). What -was- confusing about that was why
> renaming a -column- causes problems for stored procedures and views,
> even after a restart, even if the procedure or view is uing an asterisk
> in the select and not directly referencing the column in question.

Hm, I wrote this procedure:

create proc test_sp as select * from test

Which I subsequently executed. And I found that test had two columns
'mystring' and 'col'.

Then I ran:

sp_rename 'test.mystring', hisstring, 'column'

Without restarting the server, I successfully ran the procedure, and
the column was called 'hisstring'. I then created:

create view test_view as select * from test

And then:

sp_rename 'test.hisstring', herstring, 'column'

I was able to select from the view after this. However in the view
the column was still named 'hisstring'. This is because SQL Server
stores the name of the columns in the view in the system tables. This
can be remedied with:

exec sp_refreshview test_view

Finally, I would like to point out that I, and many with me, find it
to be bad coding practice to use SELECT * in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Steve Jorgensen (nospam@.nospam.nospam) writes:
> The previous post regarding dependencies reminded me of another bugger I
> come up with from time to time. SQL Server allows us to rename objects
> via a system stored procedure (I usually use an ADP front-end that
> presumably calls the same procedure), but after the rename, I find
> singnificant strangeness.
> For instance, triggers are still connected to the tables, but the
> trigger text still refers to the old table name. Views and stored
> procedures dependent on the table are broken, and are not fixed by even
> by restarting the server, though I thought the cached plans were
> supposed to be dumped on shut-down, then recreated on demand later.
> Is there some tool or technique to rename a table and automatically
> detect and properly drop/rebuild dependent triggers, views, stored
> procedures, relationships, and anything else I'm not thinking of?

The major enterprise data-modelling tools offers such features.

If you have your code under version-control - and this is what you should -
you could run some search/replace tool, provided that the old column
name is significant enough.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp