Wednesday, 1 January 2014

3 Simple ways to check if a table exists in DB

Many times we ought to search if a table exist in DB. At those times we typically search the object explorer and do a manual search. This is OK if we have like 1-50 tables; but for huge databases it is best to run a query and find the result.Below are 3 queries which use different methods to find the same thing

1.  Using Object_ID approach


SELECT CASE WHEN OBJECT_ID (N'books', N'U') IS NOT NULL THEN  'Yes' ELSE  'No' END AS [DoesTableExists?]

2.  Using Information_Schema approach

SELECT CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='books') THEN 'yes'  ELSE 'no'  END AS [DoesTableExists?];

3.  Using Sys.tables approach

select case when exists(select 1 from sys.tables where name like 'books' )

then 'yes' else 'no' end as [DoestableExists?]

No comments:

Post a Comment