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://blog.sqlauthority.com)
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:
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.
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.
Hi,
Table:
created table going to Transaction Log of database.
Proc
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.
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:
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)
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!