Check availability of a table

Question

i m creating a table using this query Select * into newtable from oldtable This is query wen u want to create an empty newtable with a structure of old table now  uptill this it works fine  but now i want to check if newtable already exists so i wrote if not exists ( Select * into newtable from oldtable) it gives syntax error why?

PHANI NADIGADDA · Answer

Strange  this is how I tried and it worked successfully the way I wanted. IF OBJECT_ID ( 'USERS1'  'U' ) IS NULL SELECT * INTO USERS1 FROM USERS I expected it should not give any error because there is no fault in this to get an error and I do not have an idea about this type of error. I recommend to try again and I am sure you will get it with out any errors. ALL THE BEST.

sheetu sachdeva · Answer

I am doing it like this if OBJECT_ID('table_name''U') IS NULL Select * into table_name from old_table  This thing is giving me the following error: Invalid SQL statement; expected 'DELETE' 'INSERT' 'PROCEDURE' 'SELECT' or 'UPDATE'.

PHANI NADIGADDA · Answer

Second parameter is XTYPE. Execute followed statement and you will have an idea what are other types we have SELECT * FROM sysObject order by xtype ALL THE BEST

sheetu sachdeva · Answer

Thanx for reply But can u please tell me wat are these arguments to object_id wen u write object_id('Table_Name''U') if Table_name is the name of the table then wat is this 'U'

PHANI NADIGADDA · Answer

You can try this way too IF OBJECT_ID ( 'TABLE_NAME'  'U' ) IS NOT NULL PRINT 'Available' ELSE PRINT 'Unavailable' ALL THE BEST