html

When working with automatically generated database names (like those created by POCO or ASP.NET), you often encounter names containing special characters such as hyphens. These can cause syntax errors when executing DROP DATABASE commands through sqlcmd.

-- This fails because of the hyphen
sqlcmd -S .\\SQLEXPRESS -q "drop database aspnet-ORData-20120910180110"

-- Error:
-- Msg 102, Level 15, State 1, Server MY-PC\\SQLEXPRESS, Line 1
-- Incorrect syntax near '-'.

To drop databases with special characters, you need to properly escape the database name using square brackets:

-- Correct syntax
sqlcmd -S .\\SQLEXPRESS -Q "DROP DATABASE [aspnet-ORData-20120910180110]"

Key differences from the failing command:

• Use -Q instead of -q (executes query and exits)
• Enclose database name in square brackets []
• Use proper case for SQL keywords (though not strictly required)

For deleting multiple databases matching a pattern (common in development environments), you can generate dynamic SQL:

sqlcmd -S .\\SQLEXPRESS -Q "SELECT 'DROP DATABASE [' + name + '];' FROM sys.databases WHERE name LIKE 'aspnet-%'"

This outputs the required DROP statements which you can then execute. For automation:

-- Generate and execute in one step
sqlcmd -S .\\SQLEXPRESS -Q "DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = @sql + 'DROP DATABASE [' + name + '];' FROM sys.databases 
WHERE name LIKE 'aspnet-%';
EXEC sp_executesql @sql;"

For more control, use sqlcmd's interactive mode:

sqlcmd -S .\\SQLEXPRESS
1> DROP DATABASE [aspnet-ORData-20120910180110]
2> GO

Before deleting databases:

• Ensure no active connections exist to the database
• Consider taking backups if the data might be needed
• For production databases, verify you're connecting to the correct server instance

To check for active connections:

sqlcmd -S .\\SQLEXPRESS -Q "SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('aspnet-ORData-20120910180110')"

To set database to single_user mode before dropping:

sqlcmd -S .\\SQLEXPRESS -Q "ALTER DATABASE [aspnet-ORData-20120910180110] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [aspnet-ORData-20120910180110];"

When working with automatically generated database names (common in frameworks like Entity Framework or ASP.NET), you often encounter names containing hyphens. These special characters require special handling in SQL commands.

-- This will fail:
DROP DATABASE aspnet-ORData-20120910180110

-- Error: Msg 102, Level 15, State 1
-- Incorrect syntax near '-'

To delete databases with special characters in their names, you need to properly escape the identifiers using square brackets:

sqlcmd -S .\\SQLEXPRESS -Q "DROP DATABASE [aspnet-ORData-20120910180110]"

To delete multiple databases matching a pattern, you can generate and execute dynamic SQL:

sqlcmd -S .\\SQLEXPRESS -Q "SELECT 'DROP DATABASE [' + name + ']' FROM sys.databases WHERE name LIKE 'aspnet-%'"

If you encounter "database in use" errors, first set the database to SINGLE_USER mode:

sqlcmd -S .\\SQLEXPRESS -Q "ALTER DATABASE [aspnet-ORData-20120910180110] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE [aspnet-ORData-20120910180110]"

For production environments, consider creating a PowerShell script:

$databases = sqlcmd -S .\\SQLEXPRESS -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name LIKE 'aspnet-%'" -h -1
$databases | ForEach-Object {
    sqlcmd -S .\\SQLEXPRESS -Q "ALTER DATABASE [$_] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$_]"
}

Always back up important data before performing mass deletions. Consider adding additional filters to your queries to avoid accidentally dropping the wrong databases.