Stored Procedure

  • Thread starter Thread starter vips
  • Start date Start date
V

vips

Hello Everyone,

I have found a way to query the SQL database for the text
within the
Stored Procedure.
Execute the following query:

select routine_definition from
information_schema.routines

Is there a better way to do it?

But i need to the same with all the database like ORACLE,
FOXPRO and
MICROSOFT ACCESS.

Can someone please tell me if there is an equivalent way
of doing it.

cheers,

Vips
 
Hi,

vips said:
Hello Everyone,

I have found a way to query the SQL database for the text
within the
Stored Procedure.
Execute the following query:

select routine_definition from
information_schema.routines

Is there a better way to do it?

But i need to the same with all the database like ORACLE,
FOXPRO and
MICROSOFT ACCESS.
Click to expand...

For Oracle you can query the sys.all_procedures object.
The other two I don't know.
 
¤ Hello Everyone,
¤
¤ I have found a way to query the SQL database for the text
¤ within the
¤ Stored Procedure.
¤ Execute the following query:
¤
¤ select routine_definition from
¤ information_schema.routines
¤
¤ Is there a better way to do it?
¤
¤ But i need to the same with all the database like ORACLE,
¤ FOXPRO and
¤ MICROSOFT ACCESS.
¤
¤ Can someone please tell me if there is an equivalent way
¤ of doing it.
¤

The below code will work for an Access and Oracle database. It may work with the FoxPro OLEDB driver
as well (if supported) but I don't have a FoxPro database with SPs to test this:

Sub ListTableSchema()

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"

'Oracle OLEDB connection string
'DatabaseConnection.ConnectionString = "Provider=MSDAORA;" & _
' "Data Source=DataSourceNameGoesHere;" & _
' "USER ID=xxx;PASSWORD=xxx;"

DatabaseConnection.Open()

SchemaTable =
DatabaseConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Procedures, _
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

'display all the sp schema in a DataGrid
DataGrid1.DataSource = SchemaTable

DatabaseConnection.Close()

End Sub



Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top