2

I have an application that queries and updates two databases at the same time (different from each other) and it has already around 10 to 15 years of usage. So I would like to start clearing up the garbage from them.

One of the things that I want to do is to remove all the stored procedures that are in database B that by mistake were created there (because they had to go in database A).

If I do one by one, is easy because I can tell if the sp uses a table that is not in that database. But I bet there are many of them, so I want to get a query that validates this.

This is the link from microsoft that explains this: http://msdn.microsoft.com/en-us/library/ms190686.aspx. But it doesn't give me any solution! :S

I tried the below method: http://beyondrelational.com/blogs/jacob/archive/2009/01/28/tsql-how-to-revalidate-refresh-recompile-all-stored-procedures-in-a-database.aspx

But it worked for some, but not for all.

So do you guys know any effective method that will work 100% of the time? Tryouts:

  • Recompilation > NOT
  • sp_refreshsqlmodule > works for most, but not all
  • sp_depends > it may work since all the SPs need to have a dependency on a table (at least most of them). UPDATE: This doesn't work either. If I have a sp that looks for tables in both databases, it will resolve those that can, but not all. So I can't rely on "if no dependencies, candidate to delete".

Thanks in advance

Kat Lim Ruiz
  • 2,425
  • 2
  • 26
  • 32
  • 1
    Running `sp_refreshsqlmodule` on all of them should have fixed missing dependencies due to them being created out of order so this information will be more reliable now. – Martin Smith Jan 03 '12 at 13:41
  • I ran it on some sps that I knew had to be deleted, and the command completed successfully. So it seems to "refresh the module" but although it will have an error, it doesn't return any error message – Kat Lim Ruiz Jan 03 '12 at 13:58

2 Answers2

3

There is no guaranteed way.

Deferred name resolution (as in if a table isn't there, give an error) applies at compile time, not create time. Although there is some evidence to show that code may run with missing tables

It has been requested though: see the proposed SET OPTION STRICT ON MS Connect request which comes from Erland Sommarskog

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
0

Please check my answer for the related question here:

I'm looking for a reliable way to verify T-SQL stored procedures. Anybody got one?

I have not checked it with 2 databases, but if worked fine for the objects in my single-database app.

Community
  • 1
  • 1
Daniel Hillebrand
  • 681
  • 9
  • 18