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
-
———————————————————————————————–
-
– Parameters
-
———————————————————————————————–
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
———————————————————————————————–
Saas Blogs
