/*
----------------------------------------------------------------------------------------------
INTRO:
----------------------------------------------------------------------------------------------
Based on the article http://www.eggheadcafe.com/articles/20030609.asp by Robbe Morris,
I made an script to get the compilation order of functions and stored procedures (more precise).
As the original, this is not based on sysdepends table (The use of that table does not ensure a correct order).
Instead, I read the table syscomments where the code of the objects is stored.
Besides of giving the exact order of compilation, it returns only one line per sp.
----------------------------------------------------------------------------------------------
RETURNS:
----------------------------------------------------------------------------------------------
ProcedureName => name of stored procedure or function
SortOrder => compilation order
Type => Stored Procedure or Function
This script can return also these fields:
ProcedureId => internal id of the sp
ProcedureCode => the code of the stored proc (it can be splitted in several lines)
NbRuns smallint => field used only to run the script
----------------------------------------------------------------------------------------------
REQUIREMENTS:
----------------------------------------------------------------------------------------------
this script requires function dbo.StripSQLComments to run!
----------------------------------------------------------------------------------------------
How it works:
----------------------------------------------------------------------------------------------
This script (attached), looks inside the code of each object (in systables) for references to
other stored procedures and functions.
The first places in the ordered list of compilation are taken by the objects that don’t need any other object
compiled before, then, all the objects which depends on the sp & fc with an order already assigned
(practically it means already compiled). And so on.
I came up with this idea after reading on the internet an article from Robbe D. Morris.
(http://www.eggheadcafe.com/articles/20030609.asp)
*/
----------------------------------------------------------------------------------------------
--SOURCE
----------------------------------------------------------------------------------------------
--create a table to record the results...
if exists (select * from dbo.sysobjects where id = object_id(N'[StoredProcs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [StoredProcs]
GO
create table StoredProcs(
SortOrder int,
ProcedureId int,
ProcedureName varchar(100),
ProcedureCode varchar(7500),
NbRuns smallint,
Type varchar(2)
)
GO
set nocount on
-----------------------------------------------
--get all stored procs and functions names
--we remove the name of the stored proc from the code and we also remove comments
-----------------------------------------------
insert Into StoredProcs
select 0,SysObjects.ID,upper(SysObjects.Name),
dbo.StripSQLComments (replace(upper(SysComments.Text),upper(SysObjects.Name),'')),0,
case SysObjects.type when 'P' then 'P' else 'F' end
from SysObjects, SysComments
where SysObjects.type IN ('P','FN', 'IF', 'TF') --PROCS & FC
and SysObjects.ID = SysComments.ID
and (SysObjects.Category = 0)
and upper(SysObjects.Name) not like 'SP_SEL_%'
-----------------------------------------------
--get position
-----------------------------------------------
declare @m_ProcedureName varchar(100)
declare @m_dependencies smallint
--only to start
-- select @m_ProcedureName = min (ProcedureName) from StoredProcs
select @m_ProcedureName = 'DRP_PLANNING_EXECUTE_PRODUCTID'
--condition on nbRuns to avoid unexpected infinite loops
while ((select count(*) from StoredProcs where SortOrder = 0) > 0
and (select max(NbRuns) from StoredProcs )< 15)
begin
select @m_dependencies = count(*)
from StoredProcs THIS, StoredProcs OTHERS
WHERE (THIS.ProcedureName <> OTHERS.ProcedureName)
and (THIS.ProcedureCode LIKE '%' + upper(OTHERS.ProcedureName) + '%')
and OTHERS.SortOrder = 0
and THIS.ProcedureName = @m_ProcedureName
--if the sp doesn't have dependencies
--or if it has only dependencies already compiled
--it's ready to be compiled (here compilation is equivalent to assignment of sortOrder)
if @m_dependencies = 0
begin
update StoredProcs set SortOrder = (select max(SortOrder)+1 from StoredProcs)
where ProcedureName = @m_ProcedureName
end
--if it's not ready to compile, increase nbRuns to be processed later (after all the rest)
else
begin
update StoredProcs set NbRuns = NbRuns + 1
where ProcedureName = @m_ProcedureName
end
--find next proc not ordered yet
select top 1 @m_ProcedureName = ProcedureName from StoredProcs
where SortOrder = 0
order by NbRuns, ProcedureName
end
set nocount off
-----------------------------------------------
--return ordered list of sp & fc
-----------------------------------------------
select distinct ProcedureName, SortOrder, Type--,ProcedureId, ProcedureCode
from StoredProcs
order by SortOrder
-----------------------------------------------
--if you want to clean the db...
-----------------------------------------------
--DROP table StoredProcs
-----------------------------------------------
-- StripSQLComments
-----------------------------------------------
create function dbo.StripSQLComments (@SOURCE_CODE varchar(4000))
returns varchar(4000)
as
begin
declare @START int
declare @END int
declare @CLEAN_SOURCE_CODE varchar(4000)
declare @TEMP_SOURCE_CODE varchar(4000)
declare @FIRST_RUN bit
--removing comment blocks (/* */)--------------------
set @START = 1
set @END = 1
set @CLEAN_SOURCE_CODE = ''
set @TEMP_SOURCE_CODE = @SOURCE_CODE
set @FIRST_RUN = 1
while (@END > 0 and @START > 0)
begin
if @FIRST_RUN = 0
begin
select @TEMP_SOURCE_CODE = substring(@TEMP_SOURCE_CODE,@START+2,len(@TEMP_SOURCE_CODE)-@START+1+2)
end
select @END = PATINDEX ( '%/*%' ,@TEMP_SOURCE_CODE)
if @END = 0
begin
select @CLEAN_SOURCE_CODE = @CLEAN_SOURCE_CODE +
substring(@TEMP_SOURCE_CODE,1,len(@TEMP_SOURCE_CODE))
end
else
begin
if @END > 1
begin
select @CLEAN_SOURCE_CODE = @CLEAN_SOURCE_CODE +
substring(@TEMP_SOURCE_CODE,1,@END-1)
end
select @TEMP_SOURCE_CODE = substring(@TEMP_SOURCE_CODE,@END+2,len(@TEMP_SOURCE_CODE)-(@END+2)+1)
select @START = PATINDEX ( '%*/%' ,@TEMP_SOURCE_CODE )
end
set @FIRST_RUN = 0
end
--removing comment lines (-- )--------------------
set @START = 1
set @END = 1
set @TEMP_SOURCE_CODE = @CLEAN_SOURCE_CODE
set @CLEAN_SOURCE_CODE = ''
set @FIRST_RUN = 1
while (@END > 0 and @START > 0)
begin
if @FIRST_RUN = 0
begin
select @TEMP_SOURCE_CODE = substring(@TEMP_SOURCE_CODE,@START+1,len(@TEMP_SOURCE_CODE)-@START+1+1)
end
select @END = PATINDEX ( '%--%' ,@TEMP_SOURCE_CODE)
if @END = 0
begin
select @CLEAN_SOURCE_CODE = @CLEAN_SOURCE_CODE +
substring(@TEMP_SOURCE_CODE,1,len(@TEMP_SOURCE_CODE))
end
else
begin
if @END > 1
begin
select @CLEAN_SOURCE_CODE = @CLEAN_SOURCE_CODE +
substring(@TEMP_SOURCE_CODE,1,@END-1)
end
select @TEMP_SOURCE_CODE = substring(@TEMP_SOURCE_CODE,@END+2,len(@TEMP_SOURCE_CODE)-(@END+2)+1)
select @START = PATINDEX ( '%'+char(13)+'%' ,@TEMP_SOURCE_CODE )
end
set @FIRST_RUN = 0
end
return @CLEAN_SOURCE_CODE
end