Search Text in Stored Procedure (SQL)
set nocount on
declare @ProcName nvarchar(100)
declare @ProcSortOrder int
declare @MyCursor CURSOR
declare @MyCursor2 CURSOR
declare @ProcUser varchar(100)
declare @ProcedureCode varchar(8000)
declare @ScriptText varchar(8000)
select @ProcUser = 'my user'
declare @Script TABLE
(
Script Text
)
declare @StoredProcs TABLE
(
SortOrder int,
ProcedureName varchar(100),
ProcedureCode varchar(7500)
)
Insert Into @StoredProcs (SortOrder,ProcedureName,ProcedureCode)
SELECT TOP 100 0, upper(OBJECT_NAME(id)) , Text
FROM syscomments
WHERE
upper([text]) LIKE '%AssociateDiscountChart%'
AND OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY OBJECT_NAME(id),Text
set nocount off
SET @MyCursor = CURSOR FAST_FORWARD
FOR
-- QUERY to SELECT SP
select ProcedureName,
SortOrder = (select count(*)
from @StoredProcs B
WHERE (A.ProcedureName <> B.ProcedureName)
and (REPLACE(UPPER(B.ProcedureCode),B.ProcedureName,'')
LIKE '%' + upper(A.ProcedureName) + '%')
),
ProcedureCode
from @StoredProcs A
order by SortOrder Desc
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder ,@ProcedureCode
WHILE @@FETCH_STATUS
= 0
BEGIN
INSERT INTO
@Script
SELECT CONVERT(TEXT, '--==========================================
BEGIN =============================================')
INSERT
INTO @Script
EXEC sp_helptext @ProcName
INSERT INTO @Script
SELECT CONVERT(TEXT, 'GO')
INSERT INTO @Script
SELECT CONVERT(TEXT, '--==========================================
END =============================================')
FETCH NEXT FROM @MyCursor
INTO @ProcName,@ProcSortOrder ,@ProcedureCode
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
SELECT * From @Script
No comments:
Post a Comment