Finding broken stored procedures

[cmwright]

Over the course of time, developers could alter table structure without realizing the implications of doing so. As the result, stored procedures have a tendency to break.

I was thinking about a way to overcome such an issue. It seems to me that the only way to find the broken stored procedures would be to generate the SQL script and restore the database in question using a different name. Then, when a procedure that wasn't valid tried to be created, it would fail. Once that script ran, a compare of the two databases could be done using a query like this:

select *
from server1.databasename1.dbo.sysobjects
where xtype='p' and name not in (select name from server2.databasename2.dbo.sysobjects where xtype='p' )

The invalid stored procedures would be listed.

Now, let me throw a monkey wrench in the mix. I am in an environment that requires all stored procedures to be stored as encrypted procedures. That means that when you generate the SQL script, all it says is that the stored procedures are encrypted and can't be scripted.

Anyone have any ideas about how to handle finding broken stored procedures in a development environment?

-Chris

 

[Catadmin]

I doubt your plan of attack would work. Even if you could script the Proc, recreating it isn't going to tell you it's broken unless there's a syntax error, which isn't what you're looking for. Exection plans for Procs aren't usually done up until the first time they are run, hence the reason why re-creating them won't "test" the internal code.

The only good way to find broken stored procedures is to try and execute them. In Dev, of course. You don't want to undertake a huge task like this in Production.

Another alternative is to get a data model comparison tool like Erwin and literally compare the Stored Procedures to the available tables & columns in the database.

Sorry I couldn't be more help.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[cmwright]

Hi Catadmin,

Thanks for the reply. I kind of figured that out last night when I noticed that I could create invalid stored procedures without throwing errors. I had always assumed that I could not do that. Interesting.

It's a fairly daunting task to weed out a few stored procedures from thousands. I'd still be interested in hearing other ideas on the topic.

-Chris

 

[Catadmin]

Do you know specific columns or tables that are old? If so, you could do a Text search.

Select *
from Syscomments
WHERE Text like '%<mytable%>'

Just substitute your defunct column/table names for the <mytable> part of the code and it'll get every view & proc that references those. The first three words of the text column (Create View xxx, Create Procedure xxx) will tell you the exact names of the procs you're trying to find.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[cmwright]

That would be a great solution if not for the monkey wrench that I mentioned before. All stored procedures are encrypted, so it looks like gibberish.

I bet if I was really motivated, I could use a stored procedure to decrypt the text, throw it into a table variable, and select the offenders back out.

The encryption is not tough to break for a sysadmin. What do you think?

-Chris

 

[Catadmin]

There is a way to do it, but I recall that it has something to do with reading the information into a temp table, deleting the old procedure and then re-creating the procedure from scratch.

After doing a quick Google and searching my SQL Code folder, I can't find the code for this though. You might want to check on the programming group and see if they remember seeing that item or if they know of such a thing.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.