asked on

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

DonKronos

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_g

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)

matu007

ASKER

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...

ASKER CERTIFIED SOLUTION

amit_g

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial