Searching a Stored Procedure definition (probable parse)

Question

Searching a Stored Procedure definition (probable parse)

starrynighter

SSC Rookie

Points: 26
More actions
December 19, 2010 at 7:51 am

#232166

I am involved in a migration project with the following parameters.
- There are stored procedures running under sql/server 2008.
- There are scalar functions
- I have a table (derived from system view tables) in which each row indicates “Stored Procedure SS makes at least one reference to scalar function FN”.
I want to analyse each SP definition as follows:
•Read next T-SQL statement
•Within the statement, count how many instances of FN there are for each FN known to be referenced by that SP
•If there is at least one reference, output one row stating:
SPname, FNname, statement number (starting at one), statement type (the first token of the statement), number of instances of FNname in that statement.
Primarily I need a parser which breaks the SP into individual T-SQL statements.
Where can I find one?
Should SQL Parser Object Library fill this requirement?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Instead of parsing the sproc, wouldn't one of the following object dependency functions be more efficient (introduced in SS2K8)?

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

sys.sql_expression_dependencies

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

starrynighter SSC Rookie Points: 26 More actions · Answer 2

I have used sql_expression_dependencies to derive what I currently have.

Unfortunately these tables do not yield meta-data at the T-SQL statement level.

I need the finer level of granularity to create some migration metrics.

At one level I need to count all the statements that need to be modified and tested.

At another level I need to count how many changes will be made within a statement.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since you already have the objects used in a sproc you could query sys.sql_modules with PATINDEX() in a recursive CTE to find ho often it's referenced.

I honestly doubt you'll be able to easily find the difference between statements to be modified and changes within a statement - you'd need to consider comment blocks or even such nasty things as column names with a identical names like one of the functions you're looking for (e.g. using brackets and spaces)

Counting the number of occurence of a referenced object is a valid approach, I'd say. But I'd question the reliabilty and efficiency of your approach.

But maybe there's a interpreatation issue on my side:

A statement is a single SELECT (or INSERT or SET or whatever). Within such a statement you can call an object more than once (e.g. when using self referencing tables in queries).

A procedure or a function is a block of statements.

Maybe your question needs to be rephrased:

Count all code blocks (procedures, functions, triggers and the like) that need to be modified and tested and count how many changes need to be made within such a code block.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

starrynighter SSC Rookie Points: 26 More actions · Answer 4

I know there are T-sql parsers out there, including the one I referenced in the initial post - I was hoping someone with experience in this could direct me.