Get all stored procedures

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
__________________________________
 
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
 
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==?=" <[email protected]>
| References: <[email protected]>
<#[email protected]>
| Subject: Re: Get all stored procedures
| Date: Sun, 10 Jul 2005 18:09:02 -0700
| Lines: 47
| Message-ID: <[email protected]>
| 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.
| > __________________________________
| >
| > | > > 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
| >
| >
| >
|
 
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!
***************************
 
David:

The issue here is one of schema and objects. There are two ways to get at
objects with SQL Server: DMO or NS (SMO in SQL 2005), aka the object way OR
querying system tables. AFAIK, you can only get Oracle sprocs from system
tables. As such, there is no silver bullet that conquers both.

For SQL Server, if you want only user sprocs (drop all Microsoft created
sprocs from the list), you can do the following:

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

This is just a list of names. I do not have access to an Oracle box right
now, but the tables you want to look at are the system tables:

http://www.techonthenet.com/oracle/sys_tables/index.php

If I remember correctly, you will want to query ALL_OBJECTS.

A word of warning here, for both SQL Server and Oracle. By default, system
tables are not an open book. This is good, for security reasons. You will
need an account with a higher set of priveleges to get at this data. This is
not bad if you are making an admin tool, but be wary of using this type of
connection to set up a connection that does other work. It could be the hole
hackers need to get at your database.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
thanks - dave


Cowboy (Gregory A. Beamer) - MVP said:
David:

The issue here is one of schema and objects. There are two ways to get at
objects with SQL Server: DMO or NS (SMO in SQL 2005), aka the object way OR
querying system tables. AFAIK, you can only get Oracle sprocs from system
tables. As such, there is no silver bullet that conquers both.

For SQL Server, if you want only user sprocs (drop all Microsoft created
sprocs from the list), you can do the following:

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

This is just a list of names. I do not have access to an Oracle box right
now, but the tables you want to look at are the system tables:

http://www.techonthenet.com/oracle/sys_tables/index.php

If I remember correctly, you will want to query ALL_OBJECTS.

A word of warning here, for both SQL Server and Oracle. By default, system
tables are not an open book. This is good, for security reasons. You will
need an account with a higher set of priveleges to get at this data. This is
not bad if you are making an admin tool, but be wary of using this type of
connection to set up a connection that does other work. It could be the hole
hackers need to get at your database.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
Click to expand...
 
Google "BHOM Vaughn"

--
____________________________________
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.
__________________________________
 
¤ 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.

The .NET OLEDB Provider will cover those databases that support an OLEDB provider (although the
level of functionality may vary). Below is an example that uses Access:

Dim DatabaseConnection As New System.Data.OleDb.OleDbConnection
Dim SchemaTable As DataTable

DatabaseConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\db1.mdb"

DatabaseConnection.Open()

'Procedures
SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _
New Object() {Nothing, Nothing, Nothing})

'Views
'SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Views, _
' New Object() {Nothing, Nothing, Nothing})

Dim RowCount As Int32
For RowCount = 0 To SchemaTable.Rows.Count - 1
Console.WriteLine(SchemaTable.Rows(RowCount)!PROCEDURE_NAME.ToString)
Next RowCount

'list of info pertaining to Procedures
DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top