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

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
matu007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonKronosCommented:
Try this.

Select C.text, O.id, O.name, DB_NAME()  
From sysobjects as O
     Inner Join  syscomments as C
        On O.id = C.id
     
where o.xtype = 'P'
Order By C.id

amit_gCommented:
Something like this ...

declare @dbname varchar(500)
declare @sql varchar(8000)

declare db_cursor cursor for
select name from master.dbo.sysdatabases

open db_cursor

fetch db_cursor into @dbname

while @@fetch_status = 0
begin
      set @sql = IsNull(@sql + ' union ', '') + 'select ''' + @dbname + ''', name from ' + @dbname + '.dbo.sysobjects where xtype = ''P'''
      print @dbname
      fetch db_cursor into @dbname
end

close db_cursor
deallocate db_cursor

--select @sql
execute(@sql)
matu007Author Commented:
Friends:

I am looking for a way to get the DB_NAME as some ":id" that relates a database object (say a stored procedure) to a
database_id (the database id being extracted from a column of some systable).........

Isnt there some way in the system tables to relate which stored procedure/table/function/view is stored in which database....using referential integrity...Also when i extract the text of the stored procedure from syscomments i do not get the entire SP...

amit_gCommented:
>>the database id being extracted from a column of some systable

AFAIK, it is not available.

>>Also when i extract the text of the stored procedure from syscomments i do not get the entire SP..

Tools->Options->Results tab->change the value in Maximum characters per column (*) to desired value.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.