March 24, 2007 at 4:29 pm
Hi all,
Does anyone know a technique for scanning thru all the stored procedures in a database to identify those that return result sets? Basically what I'm trying to do is to sort the list of stored procedures into two set: (a) those that return result sets (i.e. data inquiry) when called and (b) those that are action processors (e.g. deleting data, etc.). I know that these two things are not mutually exclusive, but do I want to identify all stored procedures that return results sets. I thought I had seen an attribute somewhere in SQL Server 2005 that flagged that a procedure returned a result set but can't find it now . . .
March 25, 2007 at 10:09 am
If your database is a clean one created in 2005 and not an updated SQL Server 2000 database it is a very simple task look for stored proc with JOINs and UNION, both are idempotent that is they are not supposed to alter the state of the data; you could run into a few exceptions. In 2000 JOINs are updated regularly so your search becomes very haphazard meaning checking all JOINS to make sure they are not being updated. All of the above is dependent on your SQL Server developer's knowledge of SQL. Hope this helps.
Kind regards,
Gift Peddie
March 26, 2007 at 3:36 pm
I know that this doesn't help you at this moment, but several years ago, the DBA's where I work made an agreement that all stored procedure would follow a predetermined naming standard. For example, all our stored procs that return data have the string Get in the name 'prc_Get....' All the procs that insert data, have Ins in their name, the ones that update data Upd, etc. Makes it very easy to determine what a proc does before even looking at its internals.
Carlos.
Carlos
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply