Dropped something and I want to be sure...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dropped something and I want to be sure...

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-04 : 10:33:56

I'm pretty sure, well, sorta sure, actually I'm panicing

I've modified all stored procs to use a "new" table. I want to drop the "old" table.

What's the best way to find out if there are any remaining references???

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-04 : 10:35:53

script the whole db into file and do a search on the file. probably the easiest...

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-06 : 14:00:43

I've always been curious, does anyone know if this is this a reliable technique?


select distinct o.name 
from	sysDepends d 
JOIN	sysObjects o 
	ON d.depid = o.id 
where	depid = object_id('<table I want to drop>')

edit: I guess procs in other databases (or linked servers) may mess that up

BTW what I usually do is run:
Select distinct object_name(id) from sysComments where text like '%<dependent table>%'
in whatever database I suspect may contain references.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-06 : 14:13:20

I think I just answered my own question:


create Proc DropThisProc as Select * from DropThisTable
GO

create table DropThisTable (Col1 int)
GO

select distinct o.name 
from	sysDepends d 
JOIN	sysObjects o 
	ON d.depid = o.id 
where	depid = object_id('DropThisTable')

no record of a dependency because at the time the sp was compiled, the table didn't exist.

Be One with the Optimizer
TG

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-06 : 14:35:47

You can search syscomments for any references select object_name(id) from syscomments where text like '%mytbl%'

You can rename the old table then clear the query plan cache (or better bounce the server) before dropping to see if there are any errors. If there are then create a view to reference the new table if you don't have time to corect the code.

You could probably drop the old table and create a view with it's name to reference the new table and use profiler to log any references to the view.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-06 : 14:48:30

cool, I usually do the sysComments search (as I buried in my earlier post up the thread) but never considered the other 2 methods. thanks, nr.

Be One with the Optimizer
TG

Andraax
Aged Yak Warrior

790 Posts

Posted - 2005-02-06 : 17:10:45

The sysdepends way is no good, primarily because it cannot cope with references contained in dynamic sql. The syscomments way is grest though.

Kristen
Test

22859 Posts

Posted - 2005-02-10 : 01:27:13

"I've modified all stored procs to use a "new" table. I want to drop the "old" table."

We put a VIEW in place to keep anything legacy happy.

Renaming the old table (as NR mentioned) often gives me grief because the FK names, PK name and Constraint names travel with the renamed table, so don't become available for reuse.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources