November 21, 2009 at 6:23 am
Hi guys,
I recently made a change to a data structure and I'm in the process of refactoring all the procedures and functions which rely on those tables.
I want to query all procs and functions to see if they rely on the procs/functions I have already changed.
If I do this:
DECLARE @procName VARCHAR(50)
SET @procName = 'ufShowFolderPermissionForUser'
select @procName as RefName, o.name,m.definition
from sys.sql_modules m
left outer join sysobjects o on m.object_id=o.id
where [definition] like '%' + @procName + '%'
I get a list of where this function is being used.
If I use the same code in a stored procedure (below), I get no results back.
Any clues?
Thanks
Paul
IF EXISTS (SELECT name FROM dbo.sysobjects WHERE name = N'usp_FindProcUsage' AND xtype = 'P')
DROP PROCEDURE dbo.usp_FindProcUsage
GO
CREATE PROCEDURE dbo.usp_FindProcUsage
@vcProcList varchar(8000)
AS
DECLARE @tblProcArray TABLE
(
ProcNum INT IDENTITY(1,1),
ProcName varchar(1000)
)
-- load table names into array table
INSERT INTO @tblProcArray
SELECT Element FROM
dbo.split(@vcProcList, ',')
DECLARE procs CURSOR FOR Select ltrim(rtrim(ProcName)) from @tblProcArray
DECLARE @c_procname varchar(1000)
DECLARE @sql varchar(2000)
SELECT * FROM @tblProcArray tpa
OPEN procs FETCH procs INTO @c_procname
while @@FETCH_STATUS <> -1
begin
SELECT @c_procname
select ltrim(rtrim(@c_procname)) as RefName, o.name,m.definition
from sys.sql_modules m
left outer join sysobjects o on m.object_id= o.id
where [definition] like ('%' + @c_procname + '%')
--select @sql = 'select ''' + @c_procname + ''' as RefName, o.name,m.definition '
--select @sql = @sql + ' from sys.sql_modules m '
--select @sql = @sql + ' inner join sysobjects o on m.object_id=o.id'
--select @sql = @sql + ' where [definition] like ''%' + @c_procname + '%'''
--execute (@sql)
FETCH procs INTO @c_procname
end
CLOSE procs DEALLOCATE procs
GO
November 21, 2009 at 10:46 am
I'm sorry, but how is that the same code? You've introduced half a dozen new variables, any one of which could be causing the problems. I'd debug the stored procedure, adding in checks to make sure you're getting everything you think you're getting at each different point within it.
IE.
Check that your split function is splitting the string exactly as you're expecting.
Check to make sure that the new elements match up to valid objects in the database.
Make sure that the different elements are being fetched properly from your cursor.
Make sure it is running in the correct database.
Just look at every different point in the SP to see which part isn't matching up. That should point you to what the problem is.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply