cannot drop table

_{Sunday, December 10, 2017 1:50 AM}

Hello all,

I am having an issue with dropping a table. When I am trying to drop a table, it's giving me an error:

" You cannot drop the table "Test123" , because table doesn't exists or you don't have permissions"

But when I am trying to create it, it's giving me an error message:

"There is already an object named 'TEST123' in the database."

I have tried checking in other database(s), but no use I cannot find the table. I also tried checking in the system tables.

So can any one helps me dropping and re-creating the table.

Thanks,

Sunny

_{Sunday, December 10, 2017 5:13 AM ✅Answered}

That sounds like Test123 exists, but is not a table (for example, it is a stored procedure).  Try running

select SCHEMA_NAME(o.schema_id) As SchemaName, o.name As ObjectName, o.type_desc As Type
From sys.objects o
Where o.name = 'Test123'

That should tell you what the type of Test123 is.

Tom

_{Sunday, December 10, 2017 7:23 AM ✅Answered}

Is that case sensitive server? Then run Tom's query

select SCHEMA_NAME(o.schema_id) As SchemaName, o.name As ObjectName, o.type_desc As Type
From sys.objects o
Where o.name = 'TEST123'

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, December 11, 2017 3:21 AM ✅Answered}

Hello Visakh,

It's "SQL_Latin1_General_CP1_CI_AS"

That suggests its case insensitive

Then it cant be reason fr your error

Can you show exact script you're trying to execute?

In any case you can use a script like below which will work always

IF OBJECT_ID('Test123') IS NOT NULL
DROP TABLE Test123

CREATE TABLE Test123
(
....
)

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Tuesday, December 12, 2017 3:15 AM ✅Answered}

Hi Sunny,

Then I think your issue related to your permissions.

You should know that when you want to Drop a table, there are permission limitations:

Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

You are not the admin. Right? So based on this, I have made some test. Create a user and grant him create table and alter schema permissions. And deny his CONTROL permission on the table. Scripts like:

GRANT CREATE TABLE TO [666666]
 
GRANT ALTER ON SCHEMA:: dbo TO [666666]
 
DENY CONTROL ON OBJECT ::dbo.Table2 TO [666666] 

After doing this, when EXECUTE AS USER = '666666' and doing drop and create. The same error like yours appears.

Thereby, I think you may need to ask the database admin for help. Grant the CONTROL permission on the table and see if it will resolve your issue.

Thanks,
Xi Jin.

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Sunday, December 10, 2017 2:27 AM}

Make sure you have drop table permission. Try executing following query

SELECT * FROM TEST123

If above query shows result then it could be possible you don't have drop permission. 

Gaurav Khanna | Microsoft MVP | Microsoft Community Contributor

_{Sunday, December 10, 2017 2:48 AM}

Hello Gaurav,

Thanks for the reply,when I am trying to execute SELECT * FROM TEST123, It's showing me invalid object. 

So when I am truing to create it, it shows me "

There is already an object named 'TEST123' in the database. It's not creating or letting me to drop the table.

Thanks,

Sunny

_{Sunday, December 10, 2017 7:21 AM}

Hello Tom,

Thanks for the reply, but when I ran the query... I am not getting no results."0 rows affected" .

Thanks

_{Sunday, December 10, 2017 2:00 PM}

Sounds like you're using a case sensitive collation

You can check it using query like

SELECT collation_name 
FROM sys.databases
WHERE name = '<your db name>'

If the collation has the pattern CS_ its case sensitive and for insensitive it would be CI_

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Sunday, December 10, 2017 8:06 PM}

Hello Visakh,

It's "SQL_Latin1_General_CP1_CI_AS"

_{Monday, December 11, 2017 3:03 AM}

Hi sunny,

First, could you please share us your entire DROP or CREATE table script if possible?

Then Tom's query is for current database.

select SCHEMA_NAME(o.schema_id) As SchemaName, o.name As ObjectName, o.type_desc As Type
From sys.objects o
Where o.name = 'Test123'

So maybe the 'Test123' exists in other databases. Please try following script, see if it returns any result.

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; if exists(select * from sys.objects where name=''Test123'') begin print ''exist in '' + db_name() end'

Thanks,
Xi Jin.

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Monday, December 11, 2017 3:34 AM}

Hello Jin,

Thanks for the reply. I have executed your given script, I am getting as command "Command(s) completed successfully." I am not getting any result.

Thanks,

Sunny

_{Monday, December 11, 2017 3:37 AM}

Hello Visakh,

I ma still getting the same :

"There is already an object named 'Test123' in the database."

Thanks,

Sunny

_{Tuesday, December 12, 2017 4:02 AM}

Hello Visakh,

I ma still getting the same :

"There is already an object named 'Test123' in the database."

Thanks,

Sunny

Can you show your full script?

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Tuesday, December 12, 2017 5:56 AM}

OK Jin,

Will ask my database admin.

Thanks,

Sunny

_{Tuesday, December 12, 2017 5:56 AM}

Hello Vaisakh,

Will send you my script tomorrow.

Thanks,

Sunny