Binaryworld - generate script for Stored procs, Search for string with in stored pro ... [ SQL Server -> Stored Procedure ]

The following script will search for all stored proc and user defined function for specific substring. So if you looking for which proc is using "CREATE INDEX" code you can easily pinpoint that using this code.

It will list size of all tables or table with specific name (use wildcard)
It will count lines of code for all procs or procs with specific name (use wildcard)

Click here to copy the following block

/*
All Copyrights 2007 reserved by Nayan Patel

Description :

- search a specific string with in stored proc(s)
- count lines for specified proc(s)
- script stored proc by name or search pattern for code

Usage :

exec usp_sql_tools 'SCRIPT','%' --//Script all procs
exec usp_sql_tools 'SCRIPT','ins%' --//Script all procs staring with "ins"
exec usp_sql_tools 'SCRIPT','%','%USE DW_Data%' --//Script all procs staring which contains USE DW_Data inside the code

exec usp_sql_tools 'SEARCH','%','%#tmp_tables%'
exec usp_sql_tools 'SEARCH','%','%USE DW%'

exec usp_sql_tools 'COUNTLINE','ins_%' --// Count Lines of all proc staring with ins_

exec usp_sql_tools @command='TABLESIZE',@search_obj_name='DW_%',@order_by='EstRowCount',@asc_desc='desc'

*/

alter proc usp_sql_tools
@command varchar(100)='SEARCH' -- COUNTLINE, SCRIPT, TABLESIZE
,@search_obj_name varchar(100)='%'
,@search_pattern varchar(100)='%'
,@order_by varchar(128)=null
,@asc_desc varchar(128)='asc'
as

Declare @obj_name varchar(128)
Declare @obj_type varchar(10)

if @command in ('SEARCH','COUNTLINE','SCRIPT')
begin
Declare obj_Cur cursor
For
select name,type from sysobjects where type in ('P','FN','TR','V')

--select * from sysobjects where type='P'

create table #tmp (line_number int identity(1,1), line_text nvarchar(max))
create table #tmpAll (obj_name varchar(100),line_number int,line_text nvarchar(max),obj_type varchar(20))

open obj_Cur

fetch next from obj_Cur into @obj_name,@obj_type

while @@fetch_status <> -1
begin

insert into #tmp(line_text)
select '--/################## ' + @obj_name + ' ####################/'

insert into #tmp(line_text)
exec sp_helptext @obj_name

insert into #tmp(line_text)
select 'GO'

insert into #tmpAll(obj_name,line_number,line_text,obj_type)
select @obj_name
,line_number
, line_text
,obj_type=case
when @obj_type ='P' then 'Procedure'
when @obj_type='V' then 'VIEW'
when @obj_type in ('IF','FN') then 'FUNCTION'
when @obj_type='TR' then 'Trigger'
else @obj_type
  end
from #tmp

truncate table #tmp

--select count(*) from #tmp
fetch next from obj_Cur into @obj_name,@obj_type

end

close obj_Cur
deallocate obj_Cur

end

--select top 1000 * from #tmpAll
--////////////////////////////////////////////////////////////////////
if @command='SEARCH'
begin
select * from #tmpall where line_text like @search_pattern and obj_name like @search_obj_name order by obj_name
end

--////////////////////////////////////////////////////////////////////

if @command='COUNTLINE'
begin
select obj_name,count(*) lines from #tmpall where obj_name like @search_obj_name
and obj_name in (select distinct obj_name from #tmpall where line_text like @search_pattern )
group by obj_name order by obj_name
end

--////////////////////////////////////////////////////////////////////

if @command='SCRIPT'
begin
select line_text as line_text from #tmpall where obj_name like @search_obj_name
and obj_name in (select distinct obj_name from #tmpall where line_text like @search_pattern )
order by obj_name
end

--////////////////////////////////////////////////////////////////////

if @command='TABLESIZE'
begin
SELECT a.Name
   , SUM(b.RowCnt) EstRowCount
   , SUM(b.Used) * 8 TableSize_KB
   , (SUM(b.Used) * 8.0)/1024 TableSize_MB
into #tmp_TABLESIZE
FROM sysobjects a
LEFT OUTER JOIN sysindexes b
   ON a.ID = b.ID
WHERE a.xtype IN ('U') --'S'
GROUP BY a.Name
ORDER BY 1 ASC

if @asc_desc='ASC'
select * from #tmp_TABLESIZE
where Name like @search_obj_name
order by case
when @order_by= 'EstRowCount' then EstRowCount
when @order_by= 'TableSize_KB' then TableSize_KB
when @order_by= 'TableSize_MB' then TableSize_MB
else EstRowCount
end
else
select * from #tmp_TABLESIZE
where Name like @search_obj_name
order by case
when @order_by='EstRowCount' then EstRowCount
when @order_by='TableSize_KB' then TableSize_KB
when @order_by='TableSize_MB' then TableSize_MB
else EstRowCount
end desc

end

go