How to detect if a Sproc exists ?

[Stan Sainte-Rose]

Hi,

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

Stan

 

[Frans Bouma]

Hi,

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

?

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

 

[Miha Markic]

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.

 

[William Ryan]

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)

 

[Joe Fallon]

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.