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


Drop Table

The drop table will works and gives success message.

DROP TABLE TestDB.dbo.TestTable

Drop Procedure

The drop procedure will give following error.


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.



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://blog.sqlauthority.com)

SQL Scripts, SQL Server
Previous Post
SQL SERVER – A Quick Look at Performance – A Quick Look at Configuration
Next Post
SQLAuthority News – Online Session Practical Tricks and Tips to Speed up Database Queries Today

Related Posts

19 Comments. Leave new

  • Hi Pinal,

    Because DROP TABLE provides three naming convention as per below syntax

    DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
    table_name [ ,…n ] [ ; ]

    AND, regarding DROP PROC; it does provide two naming convention as per below syntax

    DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,…n ]

    Kindly let me know in case you are agree with me or not.

    Best Regards,
    Mohit Sharma

  • As per syntax of DROP PROCEDURE

    DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,…n ]

    It does not take name of the database, it drops the procedure from current database

  • hi everybody…
    I believe that’s because
    Tables, Views, Constraints are ‘directly schema-scoped objects’ that been stored under sys.objects ONLY so they inherits the schema by default… on other meaning.. this is why you can drop a table using dbName.SchemaName.ObjectsName

    On other hand.. Stored Procedures are information is stored in sys.SQL_Modules and there for it’s Generally ‘schema-scoped objects’ and you can’t use ‘server Name’ nether ‘Database Name’ to call them, only [Schema].[spName].

    Mahmoud Sayed

  • This Is because Naming convention of drop procedure

    DROP TABLE provides three naming convention as per below syntax

    DROP TABLE [ database_name . [ schema_name ] . | schema_name . ]
    table_name [ ,…n ] [ ; ]

    AND, regarding DROP PROC; it does provide two naming convention as per below syntax

    DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,…n ]

  • Table creation syntax:
    [ database_name.[ owner ] . | owner. ] table_name
    ( {
    | column_name AS computed_column_expression
    | } [ ,…n ]

    SP creation syntax:
    CREATE PROCEDURE [ owner. ] procedure_name [ ; number ] …….

    Needless to say, whether it is a table or procedure must belongs to a database. We can use database prefix in table creation/drop optionally in case of not use ‘Use DBName’. By default, table will be created under ‘master’ database. So, I think, why table creation/drop has the syntax to mention database name doesn’t has any favor more than procedure creation/drop syntax.

    The point to hover is the second prefix ([dbo]). dbo(Database Owner) is a user who has the privilege to create the table/proc. Typically, dbo is the built-in user who has all the privileges of sysadmin role. Therefore, when a new user is created under the sysadmin role, it inherits the privileges assigned to dbo. When user is not assigned in procedure creation syntax, it goes with [dbo].

    So, we’ve something to do with owner of table/procedure not with the database name as it should be there.

  • I guess, if you create any table that information will be stored in master database, but if you create any database specific objects like procedure/view/function/etc., it will be stored only with in the database which can not access globally and its scope of these SQL objects are limited with in the database.

  • Hii,
    Tables and Stored procedure both are part of a Database.The reason for the given query is that Storeprocedures are subroutines which are dynimacly stored in database’s datadictionary. so there is no need to identify datbase part while droping the Stored Procedure in other hand tables are not like this.

  • I am not sure, but it seems to me it is somehow related to security and cross- database objects.

  • Selvam Krishnan
    December 14, 2011 5:40 pm



    created table going to Transaction Log of database.


    it wont be.

  • Procedures are bound to a schema where as tables for example are bound to a database.

    Multiple databases can share a single schema so using :

    Drop Procedure MyDB.MySchema.MyStoredProcedure

    Would not make sense as the procedure may exist across multiple databases.

  • The Stored procedures when created are tied to a database, the creation process is tied to a paricular database hence when dropping the procedure a two part convention is followed. As per the DROP PROC it is with respect to schema_name.procedure.

  • Hi Pinal,
    in the syntax for drop proc
    DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,…n ]

    the argument for schema_name clearly states that
    schema_name Is the name of the schema to which the procedure belongs. A server name or database name cannot be specified.

  • David N Nguyen
    December 15, 2011 4:12 am

    SQL syntax (BOL) allows dropping a table by specifying 3 part database.schema.table, therefore it works successfully with DROP TABLE databasename.schemaname.tablename.

    but when it comes to dropping stored procedure. SQL syntax allows only schema.stored_procedure. Therefore, when you use USE Database, it points to a specific database, next T-SQL DROP PROCEDURE schema.stored_procedure_name works sucessfully.


  • sathyanarayanansrinivasan
    December 15, 2011 2:39 pm

    reason is that in sql sproc we can have operations across dbs and linked servers thats the reason its not allowed. not sure if this answer is correct :)

  • puzzle ans:-
    my guess is that it allows store proc is not bound to a table (kind of more decoupling)

  • Kevin Scharnhorst
    December 16, 2011 8:51 pm

    I am not sure if it is this simple, but when creating objects like tables and views links are maintained under the master database so that globally they could be accessed from externally linked servers. procedure/view/function can only live under the database schema directly so when creating/deleting/altering any of these objects it can only be done while connected to that database specifically so the naming path is implied that you could only make changes while connected to that database.

  • Does it have something to do with the INFORMATION_SCHEMA?

    i.e. That you can access all of the information about a table through this but cannot access information about the procedures. INFORMATION_SCHEMA is for ANSI compliance and I imagine that under the hood, sql is probably trying to be ansi compliant in the way it performs this operations so perhaps there are ANSI rules preventing this or maybe the use of stored procedures within the INFORMATION_SCHEMA is something that just has not been added yet?

    I am guessing here but I am also intrigued to know the enaswer.

  • Good threa — I get the part about 1st needing t “USE myDB” before dropping a proc or fctn, but what about trying to run a “DROP dbo.SomeProcedure” from within a stored procedure? Here the problem is that you cannot switch context (database) mid-procedure. My immediate workaround will be to put my “drop_object” stored procedure in each database (and calling it from a batch/command-line process). Any comments welcome on how to overcome this final hurdle!


Leave a Reply

Exit mobile version