Generally we can get the procedure names in a database
SELECT * FROM sys.procedures
select * FROM sys.objects where type='p'
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
But we can't get the procedures which are used in another procedures.
Is there any solution to get those ?
YES...
We can get the procedure name through below query:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
drop TABLE #temp
IF OBJECT_ID('tempdb..#tempProcedurers') IS NOT NULL
drop TABLE #tempProcedurers
select row_number() OVER(ORDER BY object_id)ID, name,object_id INTO #temp FROM sys.procedures
declare @innerCount int=1,
@outerCount int =0,
@sno int=1,
@SourceName varchar(300)='',
@SearchName varchar(300)=''
CREATE table #tempProcedurers (SourceProc varchar(300),SearchProc varchar(300),text varchar(max))
SELECT @outerCount=count(0) FROM #temp
while @outerCount>=@sno
begin
select @SourceName=name from #temp where ID=@sno
SELECT @innerCount=count(0) FROM #temp
while @innerCount>0
begin
select @SearchName=name from #temp where ID=@innerCount
print @SourceName+' '
PRINT @SearchName
INSERT INTO #tempProcedurers(SourceProc,SearchProc,text)
select o.name,@SearchName,c.text from syscomments c, sysobjects o where o.xtype not in ('S', 'U')
and o.id = c.id AND o.name=''+@SourceName+'' AND c.text LIKE '%'+@SearchName+'%'
set @innerCount=@innerCount-1
end
set @sno=@sno+1
end
SELECT * from #tempProcedurers where SourceProc <> SearchProc