Cannot drop schema because it is being reference by object

Question

I've looked through dozens of posts regarding this error and they all have the same solution, drop all the objects referenced by the schema - but the object the error gives me does not exist.

So I try to run the following syntax

DROP SCHEMA [Application]

And the error it gives is

Cannot drop schema 'Application' because it is being referenced by object 'ApplicationRequestTable'.

So I think "ok no problem, I'll remove that object, just like the past couple of views it pointed out", so I type

SELECT * FROM sys.objects WHERE name = N'ApplicationRequestTable'

And nothing is returned from the above - am I doing something wrong because it looks like my schema is referencing an object that does not exit?!? I further verify by typing

SELECT o.name, s.name FROM sys.objects o JOIN sys.schemas s on o.schema_id = s.schema_id ORDER by s.name

And there is not a single object under the Application schema... Any pointers would be greatly appreciated as I cannot see anything at all referencing this schema now?

Version: SQL Server 2014 SP1 (build 12.0.4100)

Solution

Not all objects are in sys.objects

At least...

• sequences (sys.sequences)
• user defined table types (sys,table_types)
• service broker objects (sys.service_queues)

Full list

SELECT OBJECT_NAME(ac.object_id) FROM sys.all_columns ac WHERE name = 'schema_id'

Comments

[Gavin Burke]

I found the table in the table_types (it is a user defined table after all) SELECT * FROM sys.table_types WHERE name = N'ApplicationRequestTable' That ApplicationRequestTable DOES NOT appear in sys.all_columns though so you've got that bit wrong ... SELECT OBJECT_NAME(ac.object_id) FROM sys.all_columns ac ORDER BY OBJECT_NAME(ac.object_id) returns 14786 rows and ApplicationRequestTable is not one of them, the table Application.ApplicationRequestTable also doesn't exist in Object Explorer.

[ZygD]

Explain why I'm wrong even though you've found your object in the schema you want to remove. Application.ApplicationRequestTable is not a table. It is a table type, It appears under Programmability..Types node (not Tables node) and won't have any entries in sys.columns. I mentioned sys.all_columns so you could find schema dependent objects: not look up metadata about your object.

[Gavin Burke]

Ah ok I see now. Your wording says "Full list" so I therefore assumed my ApplicationRequestTable should be in a so-called "Full" list - you meant the list of object types (if that's correct wording, i.e. table_types)? So selecting from one of those 24 in my list would return an answer for my ApplicationRequestTable

[Alexandre M]

Full list above doesn't work for me. Trying to drop a schema, SQL refuses with that same message on the title. And the query you posted returns an empty result set....

[ZygD]

@AlexandreM then you have another object that depends on the schema. Look at sys.objects, schema_id column to start. Or ask a new question