Can't create a table with the same name I deleted before

Question

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.

Answer 1

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 

Answer 2

If I had to guess you didn't actually drop the table. It is very likely it was created under an unintended schema. First thing I would do is run this:

SELECT object_schema_name(object_id), name
FROM sys.objects
WHERE name = 'table_name'

I'm betting you will find an object with that name under a schema you don't expect. There are two solutions here.

• Delete the object you found making sure to explicitly mention the schema name.

  DELETE TABLE schemaname.tablename

• Create your new object and explicitly mention the schema.

  CREATE TABLE schemaname.tablename .....

Answer 3

Disconnect SQL Server Management Studio from the database and re-connect helped in my case.^

Note, there is also a "Intelli"-Sense cache which seems to get out of sync easily. To refresh that cache:

Press CTRL+SHIFT+R Go to Edit >> IntelliSense >> Refresh Local Cache

Answer 4

I am adding to these answers because my keyword search, 'Why does SQLSvr still believe my dropped tabled exists?', brought me here for what I thought was the same reason. Someone else may also arrive here if same scenario exists.

Using Kennith Fisher's snippet my 'table_name' was not in sys.objects.

SELECT object_schema_name(object_id), name
FROM sys.objects
WHERE name = 'TitleMasters'

I looked closely at my create table's error messages. Turns out I had accidentally created a primary key in a different table with the same name. Kennith's snippet verified this.

 SELECT object_schema_name(object_id), name
    FROM sys.objects
    WHERE name = 'PK_TitleMasters'

I renamed the Primary Key in other table correctly. Then of course my Create Table worked.

So, in the scenario table does not exists in sys.objects be sure its not complaining about a key, constraint, etc. So check whatever object comes up as 'already exists', then delete/rename, whatever is appropriate. This will unblock your new table creation.

Adam S.

Answer 5

Quite simple:

Try this first before reading what the other guys say.

1) Give your table a temporary name. E.g. If your deleted table was called "tblStudent", name your new table "tblStudent1"
2) Make sure that you actually deleted tblStudent
3) Close SQL Server Management Studio and then restart it and reconnect to your database.
4) Rename your "tblStudent1" to "tblStudent"

Answer 6

I would suspect that you have, or had in this case, a pending change on the table you previously deleted.

If you make sure that the table you deleted didn't have another tab open, such as the design change, that you were trying to do previously before you deleted the table.

Answer 7

drop and create table using schema sql helps, first generate CREATE table script, then drop table by sql, and modify CREATE script, then execute CREATE script.

If you using ssms, restart ssms works.