Getting ghostly errors in SQL Server 2008

Question

It seems that I'm getting a conflict when trying to make a relationship because of a table I had in my database and then deleted. The table does not show up in the object explorer but the error says "an exception occured, the new name is already in use as a OBJECT name and would cause a duplicate." Where does one delete all references to the object, not just the table name.

Answer 1

You probably have another object in the database with that name. Run

SELECT *
 from sys.objects
 where name = 'YourName'

and see what pops up. ("Objects" includes tables, views, procedure, functions, defaults, and a whole lot of other obscure stuff.)

Answer 2

I was getting the same error every time I ran a script that required deleting a table then renaming another table to the same name immediately after, and came across this recommendation in another forum:

Leave the schema out of the new table name when renaming:

sp_RENAME '[schema.OldTableName]' , '[NewTableName]'

I cannot explain why it works, but it sure saved my code.

(Credit to: Rhomeroo on https://social.msdn.microsoft.com/forums/sqlserver/en-US/654678f2-313f-4dad-8bd2-1741b01561f3/find-object-sprename-issue )