Hi!
I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statement.
When you Google for the answer, you get so many different answers. Is there an official/backward & forward compatible way of doing it?
Here are two possible ways of doing it. Which 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'".$table_name."', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;
MySQL provides a nice SHOW TABLES LIKE '%tablename%'; statement. I am looking for something similar.
Thanks !
AVADHESH PATEL
06-May-2013