Sql-server – How to Delete A Database And Log Files With SQL 2008 Script

deletescriptingsql-server-2008

While working on making an automated Delete and Restore script for my backups I ran into a snag. I can not delete the database; however, the delete button works exactly how I need it to. Can anyone tell me the script for that delete button?

I have also tried another query to do this task, however it is not working like I would like. Here is the code:

> USE CENTRAL
ALTER DATABASE DATABASE REMOVE FILE DATABASE_1.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE_2.LDF
ALTER DATABASE DATABASE REMOVE FILE DATABASE.MDF
ALTER DATABASE DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DATABASE SET OFFLINE
EXEC sp_detach_db DATABASE, true;
DROP DATABASE DATABASE
GO

The error that I get when I run the code is:

Executed as user: NT AUTHORITY\SYSTEM. Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102).  The step failed.

I am not sure what I am doing wrong, can someone please tell me?

Best Answer

If you click on the 'Script' in the execute box that pops up from the delete button, it reveals that the script actually uses a DROP command:

USE [DATABASE]
GO
/****** Object:  Table [dbo].[TABLE]    Script Date: 3/6/2014 9:29:39 AM ******/
DROP TABLE [dbo].[TABLE]
GO

Related Solutions

Sql-server – BCP error near the keyword ‘as’

I believe that BCP does a two phase process. The first is done with a format only where the query isn't actually executed but it is parsed so that BCP can figure out the columns that will be returned and in the second phase it actually runs the query. You are failing on the first phase as the temp table doesn't exist so it can't be used for the format only option.

Basically the use of the temp table isn't going to work.

Sql-server – Create procedure in else block on SQL Server

I personally prefer not to do it this way myself as our company forces us to version everything we do - therefore we should know whether objects exist or not and produce a script which explicitly creates or alters objects.

That being said, if you need to do it this way then for each object type that must be executed within a batch by itself you will need to execute a separate command to alter/create them. For example:

IF OBJECT_ID(N'dbo.my_proc',N'P') IS NULL
EXECUTE ('CREATE PROCEDURE [dbo].[my_proc] AS SELECT 1');
EXECUTE ('GRANT EXEC ON [dbo].[my_proc] TO [some_user]');
ELSE
EXECUTE ('ALTER PROCEDURE [dbo].[my_proc] AS SELECT 2');
GO

EDIT

Based on your additional information, Max's solution will solve your problem. However, as he mentioned this is an extremely bad way of doing it as you spend more time ensuring you have quoted correctly rather than solving SQL errors.

A better way to do it, and what I was pointing to originally, would be to separate each component out into individual statements like so:

IF DB_ID(N'some_db') IS NOT NULL
BEGIN
PRINT 'EXISTS';
END
ELSE
BEGIN
IF OBJECT_ID(N'dbo.my_table',N'U') IS NULL
BEGIN
EXECUTE(
'CREATE TABLE [dbo].[my_table](
 [id] INT IDENTITY NOT NULL, 
 [name] NVARCHAR(8) NOT NULL,
 CONSTRAINT [pk_my_table] PRIMARY KEY([id]),
 CONSTRAINT [uk_my_table_name] UNIQUE([name])
 )');
END
IF OBJECT_ID(N'dbo.my_view',N'V')IS NULL
BEGIN
EXECUTE(
'CREATE VIEW [dbo].[my_view]
 AS
 SELECT [id],[name]
 FROM [dbo].[my_table]
 WHERE [name] LIKE ''A%'';'
);
END
IF OBJECT_ID(N'dbo.my_proc',N'P') IS NULL
BEGIN
EXECUTE(
'CREATE PROCEDURE [dbo].[my_proc](@id INT)
 AS
 SELECT [name]
 FROM [dbo].[my_view]
 WHERE [id] = @id;'
);
END
END
GO

Related Question