Query to see whether a table exists?

Results 1 to 5 of 5

Thread: Query to see whether a table exists?

  1. #1
    Join Date
    Dec 1969
    Posts
    1,912

    Default Query to see whether a table exists?

    I need a SQL query that will check to see whether a table exists. If it exists, I want to DROP it. Anybody know how to write this query?<BR><BR>Currently, I just use on error resume next and DROP the table. If it does not exist, an error is generated and the page keeps processing. I am not pleased with this solution, though.<BR><BR>Thanks,<BR>Dave

  2. #2
    Ben Jones Guest

    Default RE: Query to see whether a table exists?

    In order to drop the table you are going to have to be connected as its creator via the DSN. Assuming this isn&#039t a security problem, you can just run a simple SQL command to drop the table:<BR><BR>&#060;%<BR><BR>set Conn = server.createObject("ADODB.connection")<BR>Conn.Op en "[DSN]", "[username]", "[password]"<BR><BR>sSQL = "if exists (select * from sysobjects where id = object_id(&#039dbo.your_table_name&#039) AND sysstat & 0xf = 3)" & chr(10) & "drop table dbo.your_table_name&#039" & chr(10) & "GO"<BR><BR>set RS = Conn.Execute(sSQL)<BR><BR>Conn.Close<BR>set RS = nothing<BR>set Conn = nothing<BR><BR>&#037;&#062;<BR><BR>Note that I have assumed that your table is owned by the system administrator (sa account, listed there as DBO, or database owner). You will have to change the name of the table obviously.

  3. #3
    Join Date
    Dec 1969
    Posts
    1,019

    Default RE: Query to see whether a table exists?

    What if you want to check to see if the table exists then if it does then run through a bunch of asp code. For what I need to do I need to just check to see if it exists and if it does then continue through the rest of the asp code, else print something like "the table doesnt exist" to the screen.

  4. #4
    Join Date
    Dec 1969
    Posts
    9

    Default RE: Query to see whether a table exists?

    Wouldn&#039;t it be convenient to have something like the MySQL extension "IF EXISTS" ?<BR><BR>So you could do things like<BR><BR>IF NOT dbo.Exists_function(&#039;objectname&#039;)<BR> CREATE TABLE objectname ......<BR><BR>Maybe,<BR>CREATE FUNCTION dbo.Found (@TN nvarchar(100))<BR>AS<BR> RETURN EXISTS (SELECT * FROM sysobjects WHERE id = object_id(@TN)<BR>


  5. #5
    Join Date
    Dec 1969
    Posts
    10,852

    Default Is there a reason....

    You replied to a nine year old thread? Did you have a question in there?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •