How to check Table existance in SQL Server?

Question

-- I AM TRYING TO WRITE THIS CODE BUT IT GIVES ERROR PLZ HELP ME !
Create procedure pro
as
begin
if exists(drop table emp)
create table emp
(
eID int,
)
end
---Error is --
Msg 156, Level 15, State 1, Procedure pro, Line 4
Incorrect syntax near the keyword 'drop'.

Msg 102, Level 15, State 1, Procedure pro, Line 4
Incorrect syntax near ')'.

Answer 1

Create procedure pro

as

begin

IF (EXISTS (SELECT *

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'Emp'))



BEGIN

drop table emp

END



create table emp

(

eID int

)

end

Answer 2

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.emp') AND type in (N'U'))

Drop Table dbo.emp



Why don't you just right click on the table in SSMS and select Script Table as Drop To? SSMS will give you the exact script to drop the table. That's where I got my command

Answer 3

if your are using sql server 2008, you can query the "[sys].[objects]" view for your table.



IF EXISTS(SELECT * FROM [sys].[objects] WHERE name like 'Your Table name' AND TYPE='U')

BEGIN



create table [Your Table name]

(

eID int,

)



END

Answer 4

if exists(select 1 from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'emp')

drop table emp

create table emp

(

eID int

)

Answer 5

Read this to the end, please ( http://stackoverflow.com/questions/19139565/how-to-check-existence-of-a-table-from-a-different-sql-db )

Answer 6

challenging step. do a search in google. it can assist!