May 28, 2012 at 6:37 am
Been trying to solve this with a cursor on the initial query but I seem to be stuck in a loop adding the same record.
I am appealing since I am now crosseyed.
The real motivation is to see how adding an index added to the base table will affect the list of procs which touch the table directly and indirectly, hence I need to be able to get a list of all procs which touch the table indirectly at least of order 2 ( proc references proc references table).
DECLARE @name varchar(50)
--this table holds list of objects which reference my table name.
IF OBJECT_ID('proclist') IS NOT NULL
BEGIN
DROP TABLE proclist
END
CREATE TABLE proclist( NAME varchar(50) )
--this cursor adds the list of procs which reference the procs which reference the table
DECLARE c CURSOR FOR
SELECT DISTINCT CAST(object_name(s2.id) AS VARCHAR(50)) from syscomments s2
where object_name(s2.id) IS NOT NULL AND text LIKE '%mytable%' ORDER BY 1
OPEN c
FETCH NEXT FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO proclist SELECT DISTINCT CAST(object_name(id) AS VARCHAR(50))
FROM syscomments s2
WHERE object_name(id) IS NOT NULL AND text LIKE '%' + @name + '%'
END
FETCH NEXT FROM c INTO @name
CLOSE c
DEALLOCATE c
SELECT DISTINCT name FROM proclist ORDER BY NAME ASC
May 28, 2012 at 6:52 am
MadTester (5/28/2012)
Been trying to solve this with a cursor on the initial query but I seem to be stuck in a loop adding the same record.I am appealing since I am now crosseyed.
The real motivation is to see how adding an index added to the base table will affect the list of procs which touch the table directly and indirectly, hence I need to be able to get a list of all procs which touch the table indirectly at least of order 2 ( proc references proc references table).
DECLARE @name varchar(50)--this table holds list of objects which reference my table name.
IF OBJECT_ID('proclist') IS NOT NULL
BEGIN
DROP TABLE proclist
END
CREATE TABLE proclist( NAME varchar(50) )
--this cursor adds the list of procs which reference the procs which reference the table
DECLARE c CURSOR FOR
SELECT DISTINCT CAST(object_name(s2.id) AS VARCHAR(50)) from syscomments s2
where object_name(s2.id) IS NOT NULL AND text LIKE '%mytable%' ORDER BY 1
OPEN c
FETCH NEXT FROM c INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO proclist SELECT DISTINCT CAST(object_name(id) AS VARCHAR(50))
FROM syscomments s2
WHERE object_name(id) IS NOT NULL AND text LIKE '%' + @name + '%'
END
FETCH NEXT FROM c INTO @name
CLOSE c
DEALLOCATE c
SELECT DISTINCT name FROM proclist ORDER BY NAME ASC
syscomments is depreciated, you should use sys.sql_modules instead going forward.
The below should get you what you need, just need to change the %sysjobs% to your table name your looking at.
DECLARE @tab TABLE (Reference NVARCHAR(10), ObjectName SYSNAME)
DECLARE objCursor CURSOR FOR
SELECT OBJECT_NAME(object_id) AS ObjectName, object_id as ObjectID from sys.sql_modules where definition like '%sysjobs%'
DECLARE @objname SYSNAME, @objid BIGINT
OPEN objCursor
FETCH NEXT FROM objCursor INTO @objname, @objid
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tab SELECT 'Direct',@objname
INSERT INTO @tab SELECT 'Indirect',OBJECT_NAME(OBJECT_ID) FROM sys.sql_modules where definition like '%'+@objname+'%' and object_id <> @objid
FETCH NEXT FROM objCursor INTO @objname, @objid
END
CLOSE objCursor
DEALLOCATE objCursor
SELECT * FROM @tab
May 28, 2012 at 6:57 am
May 28, 2012 at 6:59 am
Ah, yes it was, I didnt notice that.
May 28, 2012 at 8:44 am
MadTester (5/28/2012)
Been trying to solve this with a cursor on the initial query but I seem to be stuck in a loop adding the same record.I am appealing since I am now crosseyed...
There's your problem. Cursors and loops in SQL will definitely make you cross-eyed!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 8:53 am
Here's a simple non-Cursor, non-loop way to do it, to save your eyes:
DECLARE @tab TABLE (Reference NVARCHAR(10), ObjectName SYSNAME)
INSERT INTO @tab
SELECT 'Direct',
OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition like '%sysjobs%'
INSERT INTO @tab
SELECT 'Indirect',
OBJECT_NAME(o2.object_id)
FROM sys.sql_modules o1
JOIN sys.sql_modules o2 ON o2.object_id <> o1.object_id
WHERE o1.definition like '%sysjobs%'
AND o2.definition like '%'+OBJECT_NAME(o1.object_id)+'%'
SELECT * FROM @tab
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2012 at 1:19 am
For anyone looking for this type of solution, this will check up to 10 levels of separation, and also return the number of jumps to get to the base object.
Of course, the 10 and the object name are cutomisable.
Yes, this is necessary in my case cos of a spagetti DB.
DECLARE @name VARCHAR(50) ,@cursorLevel INT,@searchobject VARCHAR(50),@child VARCHAR(50)
DECLARE @level INT , @lastLevel INT
SELECT @lastLevel = 10,@searchobject = 'myfunctionorTableIAmLookingFor'
IF OBJECT_ID('dorantable') IS NOT NULL
BEGIN
DROP TABLE dorantable
END
CREATE TABLE dorantable
(
Parent VARCHAR(50) ,
[LEVEL] INT,
Child VARCHAR(50)
)
--this table holds list of objects which reference my table name.
SET @level = 1
INSERT INTO dorantable
SELECT DISTINCT
OBJECT_NAME(s2.id),
@level,@searchobject
FROM syscomments s2
WHERE OBJECT_NAME(s2.id) IS NOT NULL
AND text LIKE '%'+@searchobject+'%'
WHILE @level < @lastLevel
BEGIN
--SELECT DISTINCT parent,[level] FROM dorantable WHERE [LEVEL] = (@level)
DECLARE c CURSOR FOR SELECT DISTINCT parent FROM dorantable WHERE [LEVEL] = (@level)
OPEN c
FETCH NEXT FROM c INTO @child --previous parent becomes new child.
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO dorantable
SELECT OBJECT_NAME(s2.id) ,@level+1,@child
FROM syscomments s2
WHERE OBJECT_NAME(s2.id) IS NOT NULL
AND text LIKE '%'+@child+'%'
AND OBJECT_NAME(s2.id) NOT IN (SELECT parent FROM dorantable) --dont add table with self references.
-- AND @child NOT LIKE '%'+@searchobject+'%'
FETCH NEXT FROM c INTO @child
END
CLOSE c
DEALLOCATE c
SET @level = @level + 1
END
SELECT DISTINCT
parent ,
[level],
child
FROM dorantable
GROUP BY parent ,
[level],child
ORDER BY [LEVEL] ASC
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply