2

I try to execute this command:

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]') AND type in (N'P', N'PC'))
   DROP PROCEDURE dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]
GO

But I'm getting this 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

What I'm trying to do is to delete all objects in a database, so that the database is empty. I'm using Microsoft SQL Server 2014 Management Studio.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Hakan Kiyar
  • 1,199
  • 6
  • 16
  • 26

2 Answers2

2

As the error clearly says - you cannot use the database name as a prefix in a DROP PROCEDURE.

You need to make sure you're connected to the correct database and then you need to execute

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]') AND type in (N'P', N'PC'))
   DROP PROCEDURE [PROC_CHECK_TRACE_SETTINGS]
GO
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • You actually **can** use the database, but he put it in wrong order. The correct order is: 1. database, 2. schema, 3. procedure name. – SQL Police Jul 05 '15 at 13:31
0

You need to change the order of database name and schema.

This is wrong:

DROP PROCEDURE dbo.njams_test_sql_2.[PROC_CHECK_TRACE_SETTINGS]

This is correct:

DROP PROCEDURE njams_test_sql_2.dbo.[PROC_CHECK_TRACE_SETTINGS]

Or, you can just leave them away. Then the current database and the default schema (most likely dbo) are used:

DROP PROCEDURE [PROC_CHECK_TRACE_SETTINGS]
SQL Police
  • 4,127
  • 1
  • 25
  • 54