April 8, 2002 at 6:40 am
Howdy
What can be done to generate a list of all stored procedures in a given database or on a specific server?
April 8, 2002 at 7:43 am
One way is to run
for Single DB
select [name] from sysobjects where xtype = 'P' and base_schema_ver != 16 order by [name]
for All DB
sp_MSFOrEachDB 'select ''?'' AS DB , [name] from ?..sysobjects where xtype = ''P'' and base_schema_ver != 16 order by [name]'
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 04/08/2002 07:45:06 AM
April 8, 2002 at 7:57 am
Hi
That's the trick.
Thanks a lot.
April 9, 2002 at 3:17 pm
Actually if you use the category != 2 instead of base_schema_ver != 16 you will get all of the user defined stored procedures in a database.
Single Ex:
select [name]
from sysobjects
where xtype = 'P'
and category != 2
order by [name]
Multiple Ex:
sp_MSFOrEachDB 'select ''?'' AS DB , [name]
from ?..sysobjects
where xtype = ''P''
and category != 2
order by [name]'
April 11, 2002 at 6:33 am
Single DB
select db_name() as [PROCEDURE_QUALIFIER],
user_name(o.uid) as [PROCEDURE_OWNER],
o.name as [PROCEDURE_NAME]
from sysobjects as o
whereobjectproperty(o.id, 'IsProcedure') = 1 and
objectproperty(o.id, 'IsMSShipped') = 0
order by o.name
All DB
execute sp_msforeachdb N'
select ''?'' as [PROCEDURE_QUALIFIER],
user_name(o.uid) as [PROCEDURE_OWNER],
o.name as [PROCEDURE_NAME]
from ?..sysobjects as o
whereobjectproperty(o.id, ''IsProcedure'') = 1 and
objectproperty(o.id, ''IsMSShipped'') = 0
order by o.name'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply