SQL stored procedures/information_schema question.

Factual Questions
1

Is there a stored procedure in SQL that indicates which of your database’s stored procedures are created by users, as opposed the default ones?

If it helps… I am making a an interface for database documentation purposes, and I don’t want the users to be able to add/edit documentation for the stored procedures that come with the SQL Enterprise package.

I figured it would be in the [database].Information_Schema.routines or parameters view, but nothing stood out.

thanks for your help.

2

You don’t say what database you’re using, but assuming it’s Oracle, you can do a simple select:



SELECT owner, object_name
  FROM dba_objects
 WHERE object_type = 'PROCEDURE'
   AND owner <> 'whatevertheschemanameis'

DBA_OBJECTS is a view that selects from a couple of SYS tables. This also assumes that your users aren’t logging into the database as the schema owner.

3

if you are using MS SQL, this will get you some of the info you are looking for. As for users, you’ll have to link in sysusers (i think) through uid


select * from sysobjects 
where xtype='p'
and name not like 'dt_%'
4

Perhaps the ROUTINES view would be useful too, see the MSDN Library Reference for Information Schema Views. It might be more helpful since they aggregates system table access.