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
CREATE TABLE TestTable (ID INT)
CREATE PROCEDURE TestSP
SELECT 1 Col
The drop table will works and gives success message.
DROP TABLE TestDB.dbo.TestTable
The drop procedure will give following error.
DROP PROCEDURE TestDB.dbo.TestSP
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.
DROP PROCEDURE dbo.TestSP
Why does 3 part name works with dropping the table and not with dropping procedure?
Reference: Pinal Dave (http://blog.SQLAuthority.com)