SQL to identify what proc''s use a specified table
-
Looking for a SQL query to identify what Stored Proc's use a specified table. My table name is 'XYZ'. Thx
BT -
SELECT SO.Name
FROM SysDepends SD
INNER JOIN SysObjects SO
ON SD.ID = SO.ID
INNER JOIN SysObjects SO2
ON SD.DepID = SO2.ID
WHERE SO2.Name = 'XYZ' AND SO.xtype = 'P'
-
SELECT DISTINCT so.name FROM sysobjects so INNER JOIN syscomments sc ON sc.id = so.id
WHERE so.type = 'p' AND sc.text like '%XYZ%'
BT -
Syscomments is much safer to use. You might get too many hits but you will get ALL hits for sure.
-
That should work too. I also should have used DISTINCT. Your solution may return stored procedure names that do not actually reference the table. Let's say that you have the following tables: Order, OrderDetail, OrderHistory. If you want to use your method and search for all stored procedures that reference the Order table, your query would return all stored procedures that contain the word 'Order'. This means that it will return all stored procedures referencing any of the 3 tables I've listed as well as any other time the word 'Order' appears. Do you use ORDER BY clauses in any stored procedures? Get my point?
-
That's what I said, or unclearly implied. The problem with sysdepends is that it cannot be trusted to list all dependancies so that's why I go with syscomments!
-
Remi, I did not read you post prior to submitting mine. You are correct. SysDepends may not be 100% accurate.
-
Let me put it this way :
SysDepends
may not be 100%IS NOT accurate.Less headeaches that way
. -
This may be a li'l overkill, but it searches all databases and handles multiple search values as well.
SET NOCOUNT ON
/* Declare Variables */
DECLARE @Database VARCHAR(100)
DECLARE @TextToFind VARCHAR(100)
DECLARE @Lit VARCHAR(1)
DECLARE @Select VARCHAR(5000)
SET @Lit = ''''
/* Create Work Table for Search-by objects */
IF EXISTS (SELECT * FROM tempdb..SysObjects WHERE id = OBJECT_ID('tempdb..#ValuesSearchBy'))
DROP TABLE #ValuesSearchBy
CREATE TABLE #ValuesSearchBy(
TextToFind VARCHAR(100) NOT NULL
)
/* Insert the names of the objects you wish to find, with the % sign */
INSERT #ValuesSearchBy (TextToFind) VALUES('%MaxDistNumBySSNTermDate%')
--INSERT #ValuesSearchBy (TextToFind) VALUES('%CurrentActiveDistNumBySSN%')
--INSERT #ValuesSearchBy (TextToFind) VALUES('%CurrentTermDateBySSN%')
--INSERT #ValuesSearchBy (TextToFind) VALUES('%CurrentDistributorBySSNDistNum%')
/* Create Work Table for objects */
IF EXISTS (SELECT * FROM tempdb..SysObjects WHERE id = OBJECT_ID('tempdb..#Values'))
DROP TABLE #Values
CREATE TABLE #Values(
ObjName VARCHAR(100) NOT NULL,
DatabaseName VARCHAR(30) NOT NULL,
SearchByObject VARCHAR(100) NOT NULL
)
---------------------------------------------
DECLARE db_Cursor CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name not in ('Login','master','model','msdb','tempdb','Vab','Vap','VAP_DB','VCMAuth','VCMConfig')
ORDER BY name
DECLARE Search_Cursor CURSOR FOR
SELECT TextToFind
FROM #ValuesSearchBy
ORDER BY TextToFind
OPEN db_Cursor
FETCH NEXT FROM db_Cursor INTO @DataBase
WHILE @@FETCH_STATUS <> -1
BEGIN
OPEN Search_Cursor
FETCH NEXT FROM Search_Cursor INTO @TextToFind
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @Select = 'USE ' + @Database + ' ' +
'INSERT #Values
SELECT DISTINCT CONVERT(VARCHAR(70),SysO.name) AS ObjName,' +
@Lit +
@Database +
@Lit +
' AS DatabaseName, ' +
@Lit +
@TextToFind +
@Lit +
' FROM ' +
'Syscomments SysC' +
' JOIN Sysobjects SysO' +
' ON SysC.ID = SysO.ID ' +
' WHERE'
SET @Select = @Select + ' text' + ' LIKE ' + @Lit + @TextToFind + @Lit
EXECUTE (@SELECT)
FETCH NEXT FROM Search_Cursor INTO @TextToFind
END
CLOSE Search_Cursor
FETCH NEXT FROM db_Cursor INTO @DataBase
END
DEALLOCATE Search_Cursor
CLOSE db_Cursor
DEALLOCATE db_Cursor
SELECT SearchByObject,
DatabaseName,
ObjName
FROM #Values
ORDER BY
SearchByObject,
DatabaseName,
ObjName
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply