Search This Blog

Wednesday, January 12, 2011

Identify Re-Named Stored Procedures

declare @SQL varchar(8000)declare @name varchar(400)declare @buf varchar(8000)declare
declare
declare
set
@id int @colid int @found int @found = 0declare
select
cur cursor read_only for name, syscomments.id, colid FROM syscomments join sysobjects on syscomments.id = sysobjects.id where category = 0 order by name, colid open curset @buf = ''fetch next from cur into @name, @id, @colidwhile (@@fetch_status <> -1)beginselect @buf = cast([text] as varchar(8000)) from syscomments where id = @id and colid = @colidif
patindex('%CREATE%PROCEDURE%',@buf) > 0begin
if patindex('%' + @name + '%', @buf) > 0begin
set @found = 1endelsebegin
set @found = 0select @name 'Wrong Object Name', @id 'Object ID'
o.xtype = ''P'' and o.category = 0 and o.name = '''
select @SQL = ' select c.text AS ''SYSTEM TABLE TEXT'' from sysobjects o join syscomments c on o.id = c.id where+@name+''''exec (@SQL)endendfetch NEXT from cur into @name, @id, @colidend
close
curdeallocate curSELECT * FROM SYSCOMMENTS

No comments:

Post a Comment