Want to become a Vibe Coder? Join Vibe Coding Training here
x
C# Corner
Tech
News
Videos
Forums
Jobs
Books
Events
More
Interviews
Live
Learn
Training
Career
Members
Blogs
Challenges
Certification
Contribute
Article
Blog
Video
Ebook
Interview Question
Collapse
Feed
Dashboard
Wallet
Learn
Achievements
Network
Refer
Rewards
SharpGPT
Premium
Contribute
Article
Blog
Video
Ebook
Interview Question
Register
Login
Get The Stored Procedure By Table Name
WhatsApp
Arun Kumar
Jul 26
2016
893
0
0
SELECT
DISTINCT
so.
name
FROM
syscomments sc
INNER
JOIN
sysobjects so
ON
sc.id=so.id
WHERE
sc.TEXT
LIKE
'%IRF%'
DECLARE
@ObjectName SYSNAME
SET
@ObjectName =
'IRF'
DECLARE
@ObjectType
VARCHAR
(5)
SET
@ObjectType =
NULL
DECLARE
@ObjectID
AS
BIGINT
SELECT
TOP
(1) @ObjectID = object_id
FROM
sys.objects
WHERE
name
= @ObjectName
AND
type =
ISNULL
(@ObjectType, type)
;
WITH
DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
AS
(
SELECT
DISTINCT
sd.object_id,OBJECT_NAME(sd.object_id),
ReferencedObject = OBJECT_NAME(sd.referenced_major_id),ReferencedObjectID = sd.referenced_major_id
FROM
sys.sql_dependencies sd
JOIN
sys.objects so
ON
sd.referenced_major_id = so.object_id
WHERE
sd.referenced_major_id = @ObjectID
UNION
ALL
SELECT
sd.object_id,OBJECT_NAME(sd.object_id),OBJECT_NAME(referenced_major_id),object_id
FROM
sys.sql_dependencies sd
JOIN
DependentObjectCTE do
ON
sd.referenced_major_id = do.DependentObjectID
WHERE
sd.referenced_major_id <> sd.object_id
)
SELECT
DISTINCT
DependentObjectName
FROM
DependentObjectCTE
Stored Procedure
Table Name
Up Next
Get The Stored Procedure By Table Name