Sql-server – Delete User View named the same as system view

sql serversql-server-2008

I am exporting a database to Azure and have found a user view created in a previous version of MSSQL (Currently running 2008) that is name INFORMATION_SCHEMA.TABLE_CONSTRAINTS which obviously should be impossible. I can find the objectid in sysobjects but can find no way to drop the view by anything other than it's name.

Trying to drop the view this error is generated which makes sense:

Msg 3701, Level 11, State 5, Line 1 Cannot drop the view
'INFORMATION_SCHEMA.TABLE_CONSTRAINTS', because it does not exist or
you do not have permission.

But the object in sysobjects and it's corresponding definition are definitely user based, not what one should expect.

Any thoughts?

Pics:
Results of Drop View
Query Results

Best Answer

Making a guess with no information but is the name in sys.objects INFORMATION_SCHEMA.TABLE_CONSTRAINTS?

Try running this:

SELECT object_schema_name(object_id) AS SchemaName, name 
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'

I'm guessing you get something like this:

SchemaName          Name
------------------  ------------------------------------
INFORMATION_SCHEMA  TABLE_CONSTRAINTS
dbo                 INFORMATION_SCHEMA.TABLE_CONSTRAINTS

First confirm that the second entry is actually a view then run the following:

DROP VIEW [dbo].[INFORMATION_SCHEMA.TABLE_CONSTRAINTS]

Please run the following query and post a screenshot of the results in your question.

SELECT object_id, schema_id, object_schema_name(object_id) AS SchemaName, name, type
FROM sys.all_objects
WHERE name like '%TABLE_CONSTRAINTS'
Related Question