ASP check if table exists

[jaya_rath]

Check table's existance

--------------------------------------------------------------------------------

How can I check if a table exists in an MS SQL database using asp.plz help me . advance thanx


<% Option Explicit %>
<% Response.Buffer=True %>
<!--#include File ="Include/Connection.asp" -->
<%
Dim bugId,found_by,module,submodule,details,found_date


objCon.Execute "Create table bugfound (bugId int IDENTITY(100,1) PRIMARY KEY ,found_by nvarchar(50),module nvarchar(50),"&_
"submodule nvarchar(50),details nvarchar(250),found_date Datetime)"

ObjCon.Close
Set ObjCon= Nothing %>

this code i hv written.

[sashi]

Hi Jaya,

be more specific my fren.. how do you plan to check for the table existence on MSSQL?? are you planning to use stored prodecures or your planning to do it programatically??

there are few ways to do it..

method 1
SELECT * FROM INFORMATION_SCHEMA.TABLES

You can then loop through the resulting rows and search for the given table names or just add a WHERE condition.

method 2
Here's an easy way to check if a temp table exists, before trying to create it (ie. for reusable scripts)

*** MyTempTable = YourTableName ***

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

method 3 - using vb/asp

'--------------------------Start Code----------------------

Public Function TableExists(ByVal strDB As String, strTable As String) As

Boolean

On Error GoTo Hell

'Create a Catalog object

Dim oCat As ADOX.Catalog

Set oCat = New ADOX.Catalog

oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &

strDB

'Create a table object

Dim oTbl As ADOX.Table

Set oTbl = New ADOX.Table

Set oTbl = oCat.Tables(strTable)

'Return Success

TableExists = True

Exit_For:

'Clean up

Set oCat = Nothing

Set oTbl = Nothing

Exit Function

Hell:

GoTo Exit_For

End Function

'--------------------------End Code----------------------

ADO Method

'--------------------------Start Code----------------------

Public Function TableExistsADO(ByVal strDB As String, strTable As String) As

Boolean

On Error GoTo Hell

'Create a Catalog object

Dim RS As Recordset

Set RS = New Recordset

RS.Open "SELECT * FROM " & strTable & " WHERE 1=0", _

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strDB

'Return Success

TableExistsII = True

Exit_For:

'Clean up

If RS.State = adStateOpen Then RS.Close

Set RS = Nothing

Exit Function

Hell:

GoTo Exit_For

End Function

'--------------------------End Code----------------------