check if table exists sql server

Question

Possible Duplicate:
SQL Server: Check if table exists

I'm working with Java and MS SQL Server 2008 and I want to create a table only after checking if it not already exists in the database.

 public void addTestTable(){
  jdbcTemplate.execute(
            "create table [mydatabase].[dbo].[test] (ID integer not null identity, CREATEDBY varchar(50), CREATEDAT datetime, TITLE varchar(50), NRQUEST int, FORGROUP int, primary key(id))"

            );

}

This is my createTable function, I need another boolean function for checking if the table already exists, but I don't know how to write the sql statement for it. Could anyone please help me?

http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists — Pramod Kumar, Jul 03 '12 at 11:08

score 2 · Answer 1 · answered Jul 03 '12 at 11:15

2

IF OBJECT_ID('tablename','U') is not null
-- table exists

or

SELECT *
   FROM sys.tables
   WHERE name = 'mytable'
   AND schema_id = SCHEMA_ID('myschema')

answered Jul 03 '12 at 11:15

Nikson Kanti Paul

3,394
1
35
51

score 0 · Answer 2 · edited Jul 16 '12 at 02:25

0

IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'testSchema' 
                 AND  TABLE_NAME = 'testTable')
BEGIN
    --create table
END

edited Jul 16 '12 at 02:25

ig0774

39,669
3
55
57

answered Jul 03 '12 at 11:23

manurajhada

5,284
3
24
43

1

is `!` a valid operator for T-SQL ? – YS. Jul 03 '12 at 12:22

score 0 · Answer 3 · edited Jul 04 '12 at 05:24

0


if not exists(select 1 from sys.tables where name ='test' and schema_id = SCHEMA_ID('dbo'))
begin
  create table [dbo].[test] (ID integer not null identity, CREATEDBY varchar(50), 
  CREATEDAT datetime, TITLE varchar(50), NRQUEST int, FORGROUP int, primary key(id)) 
  print 'table created'
end
go

edited Jul 04 '12 at 05:24

Nikson Kanti Paul

3,394
1
35
51

answered Jul 03 '12 at 11:44

sganesh

79
6

check if table exists sql server

3 Answers3