SQL Server – Can’t Create Table with Same Name After Deletion

sql serversql-server-2012

I started to use SQL Server recently and I think that its simple to resolve

I did:

1 – Create table table_nome

2 – Drop table table_nome

3 – I'm trying to create a table with the same name, and stay with error in the table_name, written: "There is already an object named 'table_nome' in the database."

How can I fix it and create a table?

  • if have some errors in the writing, I'm sorry, I'm from brazil and dont know English very well –

I'm using SQL server 2012.

Best Answer

you can do something like this, it will create and drop a table called t1, schema dbo

use tempdb
go

--------------------------------------------------------------------------------
-- creates the table
--------------------------------------------------------------------------------

if exists (
              select * 
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          ) 
          begin

              drop table dbo.t1
               print 'table dropped'
          end 
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end 
go 

--------------------------------------------------------------------------------
-- drops the table
--------------------------------------------------------------------------------

if exists (
              select * 
              from sys.tables t
              inner join sys.schemas s
                      on t.schema_id = s.schema_id
              where t.name = 't1'
                and s.name ='dbo'
          ) 
          begin

              drop table dbo.t1
               print 'table dropped'
          end 
else
          begin

               create table dbo.t1 (i int not null primary key clustered)
               print 'table created'
          end 
go
Related Question