I had a recent project where we planned to re-factor an existing application’s .Net and TSQL code. I wanted to find the complexity of the stored procedures and arrive at an estimate of the effort required to review all them. I had in mind that something like number of lines of code, number of dependencies and parameters in the stored procedure would give a good starting point to create such an estimate.
I patched together a script that produced a report similar to the example below. I used that output and put in some more formulas to assign a complexity level (simple, medium or complex) and the approximate number of minutes required to review that procedure. I have not included those calculations here because they depend entirely on the estimator’s perception of complexity and the developer’s skill level.
The number of lines of code is a subjective metric here because it depends on the developer’s coding style and use of whitespace. Even then, it could be a useful reference point.
In case this seems useful then the script to create that report is available for download at Technet.
Download and rate the script at: http://gallery.technet.microsoft.com/Calculate-TSQL-Stored-831b683a
CHANGE HISTORY:
2012/Dec/01 – Version 1
2013/Feb/15 – Version 1.1 Added the CASE statement for complexity.
2014/Apr/29 – Version 1.2 Added Database and Schema name to the report.
Included objects that may not be refering to other objects.
Included views, functions and triggers.
Please feel free to share you feedback in the comments.
Looks like this is not retrieving those stored procedures names which have a dynamic sql in it!
Hi Vijay,
The script did not return those stored procedures that had no dependencies.
I’ve updated the script to version 1.2 now to include all stored procedures even if they do not have any dependencies. Please check it out if it serves your purpose.
Note: If the dependencies are a part of the dynamic SQL (e.g. calls to other stored procedures or table/view names etc.) then the SQL Server can not read the dynamic SQL to figure out the dependencies. My script uses the SQL Server catalog views get the dependency data so if it is not in the catalog view then the script will not show it!
I understand and thank you very much for the updated script. That serves the purpose.
great script. be well.
Good script.
I had created encrypted store proc for job details.but lines are not shown in script.
CREATE PROCEDURE Encryjob
WITH ENCRYPTION
AS
select
jobhist.job_id,
jobs.name,
jobhist.step_id,
run_dur_Casted = case
when (len(cast(jobhist.run_duration as varchar(20))) = 5)
then (Left(cast(jobhist.run_duration as varchar(20)),len(jobhist.run_duration)-4)) * 3600
+(substring(cast(jobhist.run_duration as varchar(20)) , len(jobhist.run_duration)-3, 2)) * 60
+ Right(cast(jobhist.run_duration as varchar(20)) , 2)
end
from msdb.dbo.sysjobhistory jobhist
Inner Join msdb.dbo.sysjobs jobs On jobhist.job_id = jobs.job_id
where jobhist.job_id=jobs.job_id
Can you please share the link for Calculate TSQL Stored Procedure Complexity code? I couldn’t find with the link in the post.
Thank you.
Sorry about the broken link! Microsoft shutdown the Technet Gallery. I’ll try to find if I have a copy somewhere.
Please get it from the Web Archive for now –
https://web.archive.org/web/20200526043746mp_/https://gallery.technet.microsoft.com/Calculate-TSQL-Stored-831b683a