While dropping a table present in a different database, you can use the fully qualified Table Name ("DatabaseName.SchemaName.TableName" format) and drop the table as shown below.

DROP TABLE AdventureWorks.HumanResources.Department
GO

While the above statement is valid and will execute successfully, the following statements will throw an error.

DROP VIEW AdventureWorks.HumanResources.vEmployee
GO

DROP FUNCTION AdventureWorks.dbo.ufnGetContactInformation
GO

DROP PROCEDURE AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo
GO

In the above scenarios, you need to change the database context using "USE <<Database>>" clause and run drop statements without the DatabaseName prefix.

Published under: SQL Server Tips ·  ·  ·  ·