SQL Server equivalent of IF USED()...

[Neil Toulouse]

Hi there!

Is there an equivalent of IF USED() or IF FILE() in SQL Server?

Basically I need to check if there is an existence of a SQL temporary table, and if there is use it, or if not re-create it.

TIA
Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

Here's how I do it. The following is a function to which you can pass a table name, and which returns .T. if the table exists:

LPARAMETERS tcTablename
tcTablename = UPPER(tcTablename)
* Check to see if table already exists on server
lcCmD = "SELECT name from sysobjects WHERE UPPER(name) = '";
 + tcTablename + "' AND type = 'U'"
lnReply = SQLEXEC(THISFORM.ConnectionNumber,lcCmd)
IF RECCOUNT("SqlResult")>0
  RETURN .T.
ELSE
  RETURN .F.
ENDIF

However, I wrote that code for SQL Server 7.0. I've a feeling there might be an easier way in SQL Server 2000, but I can't offhand remember.

Let me know if this is any use.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Mike Lewis]

Neil,

I fond the "easier way". It's the Object_ID() function. Pass the table name. If it exists, it will return an integer, otherwise it will return NULL.

The drawback is that the function doesn't distinguish between tables, views or any other type of object. That's not a problem with the VFP code I posted earlier.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

Thanks Mike!

I will give it a bash and let you know how it goes!

Neil

I like work. It fascinates me. I can sit and look at it for hours...

 

[Mike Lewis]

Neil,

I just noticed that your original question refers to temporary tables. Are these tables whose names begin with # ? If so, keep in mind that they reside in the tempdb database, so you might have to switch to that database when running either of the above solutions.

There will also be an issue with the connection. If the name begins with a single #, the table will only be visible to the connection that creates it. I don't know if the code I gave you will work if run from another connection. Probably not.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[Neil Toulouse]

Hi Mike!

Thanks for the pointers. Yes, I was refering to the '#' tables so will adjust the code accordingly and let you know what occurs.

Neil

I like work. It fascinates me. I can sit and look at it for hours...