How to check if a table exists and return a value (0/1) if it doesn't without using a stored procedure?

I've only been able to see this work with a stored procedure and I can't figure out the syntax. I basically have some external system calling a SQL query and I want to verify the table exists first using code similar to this:

if (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND  TABLE_NAME = 'OrderUpdates'))
  BEGIN
    --return 1 or 0 if it exists or doesn't exist
  END

I can't get the return statement to work properly.

2012-04-05 21:52
by Alex Kwitny

return is only for stored procedures - Elroy Flynn 2012-04-05 21:54

Do you mean return or select - Tim Lehner 2012-04-05 22:04

I meant that I wanted a result set of one row with one value in one column. Not sure the correct verbiage to explain that - Alex Kwitny 2012-04-06 17:39

 SELECT CAST(COUNT(*) AS BIT) -- Should be unique to give 0 or 1 as result
 FROM INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'OrderUpdates'
    -- Not sure if you want views or not:
    AND TABLE_TYPE = 'BASE TABLE'

2012-04-05 21:55
by Tim Lehner

This is exactly what I needed - Alex Kwitny 2012-04-06 15:42

I don't understand quite what you are getting at - RETURN is only valid in a stored procedure or function.

If you want the same thing in a set (because the caller is expecting a rowset), then something like this can work:

SELECT 1 AS DoesItExist
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'OrderUpdates'
UNION ALL
SELECT 0
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'OrderUpdates')

2012-04-05 21:55
by Cade Roux

I put the return in a comment because I wasn't sure the correct word to use. I wanted it to return a value - Alex Kwitny 2012-04-06 15:43

@Alex K Return can return a table from a table-valued function, but generally can only return a single value. A procedure which selects various things will return result sets as well as a return value (and you also have the option of output parameters), but the result sets are not really return values. It sounded like you didn't want to use a stored procedure, so I wasn't sure what you were returning anything to. I figured that meant you could only have SQL which produced result sets which the client could then be configured to interpret (i.e. one value in a row) - Cade Roux 2012-04-06 19:46

Yes exactly, a single result set is what I was going for. I think this works to - Alex Kwitny 2012-04-06 20:53

SQL Server has a table named 'tables', which contains the list of tables in your database. So following code should help:

CREATE PROCEDURE DoesTableExist(@table VARCHAR(50), @exists BIT OUTPUT)
AS
BEGIN
IF EXISTS(SELECT * FROM sys.tables WHERE name = @table)
    SET @exists = 1
ELSE
    SET @exists = 0

RETURN @exists
END

2012-04-05 22:06
by Mitesh Budhabhatti

You could do something like this:

USE SomeDatabase;
IF OBJECT_ID('YourTable', 'U') IS NOT NULL 
BEGIN
    SELECT 1
END ELSE BEGIN
    SELECT 0
END

If you're looking for a type other than a User Table, you can change the 'U' in OBJECT_ID to the correct value as determined by the types listed in sys.objects

2012-04-05 22:08
by bhamby

This could easily give false positives because of objects other than tables (or views) - Tim Lehner 2012-04-05 22:11

@TimLehner You caught me in between edits. :-) See the revised answer - bhamby 2012-04-05 22:15

I've been there before - Tim Lehner 2012-04-05 22:15