Searching through Stored procedure code using TSQL script

Even if you have a refactoring tool, sometimes i wonder what are all the storedprocedures that uses this table that starts with ‘products_’  or ‘system_’… or all the tables that have a column name ‘status_id’….

so here is the script that scans all the stored procedure code, and check if the table name is found

Note  —this will also return if the table name is in the comment too.. so the logic is, even if you want to search any text (comment) in a stored procedure

 

 

1. ———————————————————————————————–
2. – Parameters
3. ———————————————————————————————–

DECLARE @tableNameLike VARCHAR(100), @columnNameLike VARCHAR(100)

– Filter only stored procedures that uses that table(s) and (optionaly) if it has a column

SET @tableNameLike = ‘enterYourTableName%’ — If you have organized all your tables with

— standard prefix, will be useful to filter

SET @columnNameLike = ‘enterYourColumnName%’ — Only if the table has this column

———————————————————————————————–

– Local Variables

———————————————————————————————–

DECLARE @spname VARCHAR(100),@TABLEname VARCHAR(100)

DECLARE @spnames TABLE(spname VARCHAR(100))

DECLARE @EXISTS INT

DECLARE @sp_start INT, @sp_END INT ,@t_start INT , @t_END INT,@sp_INdex INT , @t_INdex INT

———————————————————————————————–

– DROP before Creating Temporary Tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

– Get all the Stored procedure names

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #spnames

FROM sys.objects

WHERE type = ‘P’

– Get all the table names that passes the filter parameter

SELECT row_number() OVER(ORDER BY name) AS rownum,name

INTO #TABLEnames

FROM

sysobjects T

JOIN

INformation_schema.columns C

on

C.TABLE_NAME = T.name

WHERE T.name like @tableNameLike AND T.xtype = ‘U’

AND C.column_name like @columnNameLike

– SELECT * FROM #spnames

– SELECT * FROM #TABLEnames

———————————————————————————————–

– Get and Set Loop Index

———————————————————————————————–

SELECT @sp_start = 1,@sp_END = max(rownum)+1

FROM #spnames

SELECT @t_start =1,@t_END = max(rownum)+1 FROM #TABLEnames

– SELECT @sp_Start,@sp_END,@t_start,@t_END

———————————————————————————————–

– Loop through each stored procedure name

———————————————————————————————–

while @sp_start < @sp_END

BEGIN

– Get the stored procedure name

SELECT @spname = [name]

FROM

#spnames

WHERE rownum = @sp_start

– Loop through each tablename to check if it is used in the stored procedure code

while @t_start < @t_END

BEGIN

– Get the table name

SELECT @TABLEname = [name]

FROM

#TABLEnames

WHERE

rownum = @t_start

– Check if the table name exists in the stored procedure code

– I am using the SUM(CHARINDEX(xxx)) since the stored procedure code may span

– multiple rows

SELECT @EXISTS = SUM(CHARINDEX(@TABLEname,[text]))

FROM syscomments

WHERE id = object_id(@spname)

– If it is greater than zero then it is used in the stored procedure code

IF @EXISTS > 0

BEGIN

INSERT INTO @spnames

SELECT @spname

END

SET @t_start = @t_start+1 – Increment the table index

END

SET @sp_start = @sp_start+1 – Increment the storedprocedure index

SET @t_start = 1 – Reset the table index

END

———————————————————————————————–

– To list all the spnames (remember there will be duplicates)

———————————————————————————————–

SELECT DISTINCT * FROM @spnames

———————————————————————————————–

– Drop the temporary tables

———————————————————————————————–

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#spnames’)

)

DROP TABLE #spnames

IF EXISTS (

SELECT * FROM tempdb.dbo.sysobjects o

WHERE o.xtype IN (‘U’)

AND o.id = object_id(N‘tempdb..#TABLEnames’)

)

DROP TABLE #TABLEnames

———————————————————————————————–

– END

———————————————————————————————–

IceRocket Tags: TSQL Stored procedure SQL Server 2005/2008 programming

TSQL String Range Comparison with wildcard

A simple note, and couldn’t find straight answer when i googled!!. so here it is..

This is to get result from a table when filtering in string ranges that will have a wild card for ex: ‘*’

declare @sv varchar(17),@ev varchar(17)
declare @sindex int, @eindex int

set @sv = ‘ABCDE11A09′
set @ev = ‘ABCDE11A79′

– Check the * pos if exists,if doesn’t exists return the len +1 , assuming the * at the last
select @sindex = case when charindex(‘*’,@sv)> 0 then charindex(‘*’,@sv) else len(@sv)+1 end
select @eindex = case when charindex(‘*’,@ev)> 0 then charindex(‘*’,@ev) else len(@ev)+1 end

– remove the * from the range strings
select @sv = case when charindex(‘*’,@sv)> 0 then substring(@sv,0,@sindex) else @sv  end
select @ev = case when charindex(‘*’,@ev)> 0 then substring(@ev,0,@eindex) else @ev  end

select @sindex,@eindex,@sv,@ev
select mycolumn,substring(VIN,0,@sindex), substring(VIN,0,@eindex) from mytable
where
    substring(mycolumn,0,@sindex) >= @sv AND
    substring(mycolumn,0,@eindex) <= @ev
ORDER BY mycolumn

IceRocket Tags: TSQL,SQL SERVER,Programming