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 (http://blog.SQLAuthority.com)

About these ads

19 thoughts on “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

  1. 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

    Like

  2. 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

    Like

  3. 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 ]

    Like

  4. Table creation syntax:
    CREATE TABLE
    [ 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.

    Like

  5. 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.

    Like

  6. 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.

    Like

  7. 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.

    Like

  8. 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.

    Like

  9. 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.

    Like

  10. 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.

    reference: http://blog.sqlauthority.com

    Like

  11. 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.

    Like

  12. 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.

    Like

  13. 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!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s