Hi All,
How do you plan to check for the table existence on MSSQL?? are you planning to use stored procedures 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----------------------