Re: Get all stored procedures
- From: "Cowboy (Gregory A. Beamer) - MVP" <NoSpamMgbworld@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 11 Jul 2005 07:25:03 -0700
I would further filter this to avoid system sprocs. The routine run to
populate Enterprise Manager is:
select o.name
, user_name(o.uid), o.crdate, xtype=convert(nchar(2), o.xtype)
, o.id, OBJECTPROPERTY(o.id, N'ExecIsStartup')
, OBJECTPROPERTY(o.id, N'ExecIsQuotedIdentOn')
, OBJECTPROPERTY(o.id, N'ExecIsAnsiNullsOn')
, OBJECTPROPERTY(o.id, N'IsMSShipped')
from dbo.sysobjects o
where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1
or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1
or OBJECTPROPERTY(o.id, N'IsReplProc') = 1)
and o.name not like N'#%%'
order by o.name
where OBJECTPROPERTY(o.id, N'IsMSShipped') is used to determine if this is
a user sproc or a Microsoft system sproc. To simply get names, you can use:
SELECT o.name
FROM dbo.sysobjects o
where (OBJECTPROPERTY(o.id, N'IsProcedure') = 1
or OBJECTPROPERTY(o.id, N'IsExtendedProc') = 1
or OBJECTPROPERTY(o.id, N'IsReplProc') = 1)
and o.name not like N'#%%'
and OBJECTPROPERTY(o.id, N'IsMSShipped') = 0 -- important part
order by o.name
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Steven Cheng[MSFT]" wrote:
> Hi Dave,
>
> For SQLSERVER, we can use the following SQL statement to query the SP
> objects of a certain database:
>
> select * from sysobjects where type='P'
>
> Also, I think there also exists the same object modal in SQLdmo.
>
> For oracle, I suggest you try checking the PL-SQL reference or have a
> search on the internet which may give you a quick awnser.
> Hope helps. Thanks,
>
> Steven Cheng
> Microsoft Online Support
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
> --------------------
> | Thread-Topic: Get all stored procedures
> | thread-index: AcWFtSAIUweRsUVuQ4a/lObVML4j1A==
> | X-WBNR-Posting-Host: 199.45.247.98
> | From: "=?Utf-8?B?RGF2aWQgVGhpZWxlbg==?=" <thielen@xxxxxxxxxxxxx>
> | References: <6430337F-0929-4A78-94C3-5A58CBE5A43F@xxxxxxxxxxxxx>
> <#UjDiDYhFHA.3316@xxxxxxxxxxxxxxxxxxxx>
> | Subject: Re: Get all stored procedures
> | Date: Sun, 10 Jul 2005 18:09:02 -0700
> | Lines: 47
> | Message-ID: <46378900-42B1-41F6-AC00-AC41ADB6C303@xxxxxxxxxxxxx>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.dotnet.framework.adonet
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.dotnet.framework.adonet:32388
> | X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
> |
> | Hello;
> |
> | Does anyone know the specific select for Sql Server and/or Oracle to get
> the
> | stored procedures?
> |
> | Any any idea how to get it from the OLE-DB connector
> |
> | --
> | thanks - dave
> |
> | ps - what is BHOM?
> |
> |
> |
> | "William (Bill) Vaughn" wrote:
> |
> | > Two of the data sources you mention have different interfaces and
> different
> | > ways to expose stored procedures--OLE DB is simply an interface to data
> | > sources.
> | > For SQL Server, you can use the sysobjects table to query for all
> objects in
> | > the database. For Oracle? BHOM.
> | >
> | > --
> | > ____________________________________
> | > William (Bill) Vaughn
> | > Author, Mentor, Consultant
> | > Microsoft MVP
> | > www.betav.com/blog/billva
> | > www.betav.com
> | > Please reply only to the newsgroup so that others can benefit.
> | > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> | > __________________________________
> | >
> | > "David Thielen" <thielen@xxxxxxxxxxxxx> wrote in message
> | > news:6430337F-0929-4A78-94C3-5A58CBE5A43F@xxxxxxxxxxxxxxxx
> | > > Hi;
> | > >
> | > > Is there a way under ADO .NET to get all stored procedures in a
> database.
> | > > I'd prefer a single solution but I can live with one that is specific
> to
> | > > each
> | > > driver: Sql Server, Oracle, and OLE-DB.
> | > >
> | > > --
> | > > thanks - dave
> | >
> | >
> | >
> |
>
>
.
- References:
- Get all stored procedures
- From: David Thielen
- Re: Get all stored procedures
- From: William \(Bill\) Vaughn
- Re: Get all stored procedures
- From: David Thielen
- Re: Get all stored procedures
- From: Steven Cheng[MSFT]
- Get all stored procedures
- Prev by Date: Re: Connection Timeout
- Next by Date: Re: Insert BLOB value.
- Previous by thread: Re: Get all stored procedures
- Next by thread: Re: Get all stored procedures
- Index(es):
Relevant Pages
|
