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.
CREATE DATABASE TestDB GO USE TestDB GO CREATE TABLE TestTable (ID INT) GO CREATE PROCEDURE TestSP AS SELECT 1 Col GO
The drop table will works and gives success message.
DROP TABLE TestDB.dbo.TestTable GO
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.
You can drop the procedure using following commands.
USE TestDB GO DROP PROCEDURE dbo.TestSP GO
Why does 3 part name works with dropping the table and not with dropping procedure?
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://blog.sqlauthority.com)