|
Subject:
|
If table exists in database
|
|
Posted By:
|
meghana
|
Post Date:
|
2/21/2004 8:47:47 PM
|
I have a function in VB to check if the given table exists in SQL Server 7 Database
1.Using ADOX catalog as follows:
Dim CatlogObj New ADOX.CatalogADOX.Catalog
For i = 0 To CatObj.Tables.Count - 1
'check if name present in the
If UCase(CatlogObj.Tables(i).Name) = UCase(TableName) Then
TableExists = True
Exit Function
End If
I found that using this function for database of 24,000 tables,the method returns false though the physical table is present.Table is getting created and after 45-60 secs the code checks if table exists.
This happens only when running 2 jobs in parallel and both are creating different tables in DB
Is it because somehow the catalog is not getting refreshed view of database or something like that.
I have another approach to do the same as follows:
2.Querying "sysobjects" table
SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER'
and then if recodset has >0 records return true;
But I am not sure if "sysobjects" will be refreshed with that entry when function call is made.
Any suggestions are welcome
Thanks
Meghana
|
|
Reply By:
|
Lalit_Pratihari
|
Reply Date:
|
9/29/2004 10:39:25 AM
|
Hi,
Yes you can use the second approach. Just add to the query:
SELECT COUNT (NAME) AS CNT FROM SYSOBJECTS OBJ WHERE OBJ.NAME='CUSTOMER' AND XTYPE = 'U'
Hope this helps,
Lalit
Life Means More...
|
