troubleshooting Question

need to find (StoredProc NAME, StoredProcTEXT, StoredProcID, DatabaseContainingStoredProc) ?

Avatar of matu007
matu007 asked on
Microsoft SQL Server
4 Comments1 Solution673 ViewsLast Modified:
Will need help again. How do i write an SQL statement to return the following info:
(
Stored Procedure Name,
Stored Procedure Text,
Stored Procedure ID,
Stored Procedure Database
)

I tried the following query:
Basically its hard to relate the sysdatabases table sysobjects or syscomments.........

Select C.text, O.id, O.name,  master.dbo.sysdatabases.dbid
From sysobjects as O
     Inner Join  syscomments as C
        On O.id = C.id
        And
        C.Text = 'P'
     
     Inner Join master.dbo.syslockinfo
            On   O.id =
                  master.dbo.syslockinfo.rsc_objid

     Inner Join master.dbo.sysdatabases
            On master.dbo.syslockinfo.rsc_dbid =
                   master.dbo.sysdatabases.dbid

Order By C.id
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros