How to detect if a Sproc exists ?

  • Thread starter Thread starter Stan Sainte-Rose
  • Start date Start date
S

Stan Sainte-Rose

Hi,

How can I detect if a stored procedure exists in my database with ado.net ?

Stan
 
Hi,

How can I detect if a stored procedure exists in my database with ado.net
Click to expand...
?

Query the system tables in Master (sqlserver 7) or use a query on
INFORMATION_SCHEMA.ROUTINES to see if the stored proc is there. (Sqlserver
2000).

There is also a way to determine this via OleDb, using the
OleDbConnection.GetOleDbSchemaTable() method with the proper parameters.

Frans
 
As Frans stated there is GetOleDbSchemaTable.
Example:
oleDbConnection1.Open();

DataTable schemaTable =
oleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures,

new object[] {null, null, "Sales by Year"});

oleDbConnection1.Close();

If "Sales by Year" stored procedure exists, schemaTable will have adjacent
record. Otherwise schemaTable will be empty.
 
Like Frans and Miha Mention, I'd use Information_Schema as the preferred
method, but you can use the ADO.NET version as well.

Just as an additional method you can use the good old fashioned
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_myProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
 
strSQL = "SELECT Count(*) FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[MySproc]') and xtype IN (N'FN', N'IF', N'TF')"

NumRecs = CInt(.ExecuteScalar(strSQL))
If NumRecs = 1 Then
'Sproc exists.
 
Back
Top