better than ms_dependencies

I often have to figure out what procedures, views or functions will be affected by a change in Vision, like a custom field or going multicompany for example where a new level is added on top of the organization structure. Since my clients often have custom reports written on top of their Vision database which make use of WBS or the Organization table (profit centers), it’s good to have a way to figure out what will be affected by a certain change… here is a batch of code which is better than ms_dependencies (which is not always accurate)

If you’re pretty good at t-sql you should have no problem working this to your needs. If you need some help feel free to send me an email.
Just run this against the database you are concerned with.
————————— code batch starts here ——————————-
set nocount on
declare @numObjects int
declare @ProcName nvarchar(100)
declare @ProcSortOrder int
declare @MyCursor CURSOR
declare @ProcUser varchar(100)
select @ProcUser = ‘sa’
declare @myobjects TABLE
(
SortOrder int identity,
myObjectName varchar(100),
myObjectCode varchar(7500)
)
Insert @myobjects(myObjectName)
select SysObjects.Name
from SysObjects, SysComments
where SysObjects.type in (‘P’,’V’,’FN’,’IF’,’TF’)
and (sysobjects.name like ‘fmv%’) — this applies only if you preface your custom objects with something to separate them from the standard objects
and (SysObjects.Category = 0)
and (SysObjects.ID = SysComments.ID)
and (sysComments.text like ‘%organization%’ and syscomments.text like ‘%insert%’)
group by sysobjects.name
order by SysObjects.Name ASC
set nocount off
set @numobjects = (select count(*) from @myobjects)
print ‘/*——————————–‘
print ‘FOUND: ‘ + cast(@numobjects as varchar) + ‘ objects ‘
print ‘——————————–*/’
declare @idnumber int, @maxnumber int
set @maxnumber = (select max(sortorder) from @myobjects)
set @idnumber = 1
print ‘/* ————— object list ———————— */’
while @maxnumber >= @idnumber
begin
select @procname = myObjectName from @myobjects where sortorder = @idnumber
set @idnumber = @idnumber + 1
print @procname
end
print ‘/* ————— end object list ——————– */’
print ”
print ”
print ”
SET @MyCursor = CURSOR FAST_FORWARD
FOR
select myObjectName,
SortOrder = (select count(*)
from @myobjects B
WHERE (A.myObjectName B.myObjectName)
and (REPLACE(UPPER(B.myObjectCode),B.myObjectName,”)
LIKE ‘%’ + upper(A.myObjectName) + ‘%’)
)
from @myobjects A
group by myObjectName
order by SortOrder Desc
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder
WHILE @@FETCH_STATUS = 0
BEGIN
print ‘———————————————————————–‘
print ‘– object: ‘ + @procName
print ‘———————————————————————–‘
exec sp_helptext @ProcName
print ‘———————————————————————–‘
print ‘– ///// END ‘ + @procName
print ‘———————————————————————–‘
print ”
print ”
print ”
/* PRINT @ProcName + ‘ ‘ + cast(@ProcSortOrder as varchar(20)) */
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
————————– code batch ends here ———————————

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s