Asked  7 Months ago    Answers:  5   Viewed   66 times

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements.

When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible way of doing it?

Here are two possible ways of doing it. Which one among the two is the standard/best way of doing it?

First way:

IF EXISTS (SELECT 1 
           FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

Second way:

IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

MySQL provides the simple

SHOW TABLES LIKE '%tablename%'; 

statement. I am looking for something similar.

 Answers

62

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END
Tuesday, June 1, 2021
 
ojrac
answered 7 Months ago
64

A simple Group by with nested queries should suffice:

Select State, coalesce(max_created,max_updated) from (
  Select State, min(foo) as min_foo, max(foo) as max_foo, 
    max(DateCreated) as max_created,
    max(DateUpdated) as max_updated
  From Data
  Group by State)
 Where min_foo = max_foo
Monday, August 23, 2021
 
Grzegorz
answered 4 Months ago
75

If you look at the documentation for CONVERT, you'll see that format 102 is the ANSI format, or yyyy.mm.dd. So passing a date like 2012.05.08 should work.

It looks like 2012.05.08 9:41 and 2012.05.08 9:41AM will work too, but that's outside the documented format.

Wednesday, September 1, 2021
 
anubhava
answered 3 Months ago
98

Yes, it does exist:

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TableName'
Sunday, September 19, 2021
 
Hans-Peter Störr
answered 3 Months ago
30

Yes you can. But the first method is always encouraged!

Thursday, October 28, 2021
 
Justin
answered 1 Month ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share