SQL SERVER – Explain Error:166 : does not allow specifying the database name as a prefix to the object name – Puzzle to Win SQL Server Interview Questions and Answers Book

I was recently reading excellent Just Learned Tip regarding 3 part naming Cannot be used when dropping Views,Functions or Procedures. This is quite a well known tip however, every developer and DBA learns at sometime in their career with ‘hm…’ moment. To illustrate this further here is a simple case scenario.

Setup environment

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable (ID INT)
GO
CREATE PROCEDURE TestSP
AS
SELECT 1 Col
GO

Drop Table

The drop table will works and gives success message.

DROP TABLE TestDB.dbo.TestTable
GO

Drop Procedure

The drop procedure will give following error.

DROP PROCEDURE TestDB.dbo.TestSP
GO

Msg 166, Level 15, State 1, Line 1
‘DROP PROCEDURE’ does not allow specifying the database name as a prefix to the object name.

Workaround

You can drop the procedure using following commands.

USE TestDB
GO
DROP PROCEDURE dbo.TestSP
GO

Puzzle

Why does 3 part name works with dropping the table and not with dropping procedure?

Give Away

Two winners gets SQL Server Interview Questions and Answers book [Amazon|Flipkart|Kindle]. Leave your answer in the comment area. Winner will be announced before holiday season!

Reference: Pinal Dave (https://darkslategrey-bat-805937.hostingersite.com)