A WordPress Site

Main Menu

Share innovation with the world

June, 2017

now browsing by month

 

Dropping and recreating databases in Microsoft SQL Server

If you are unable to drop and create database in SQL server, this code will close all active connections to the database and then drop it WHILE EXISTS(select NULL from sys.databases where name=‘yourDBname’) BEGIN DECLARE @SQL varchar(max) SELECT @SQL = COALESCE(@SQL,”) + ‘Kill ‘ + Convert(varchar, SPId) + ‘;’ FROM MASTER..SysProcesses WHERE DBId = DB_ID(N ‘yourDBname’) AND SPId <> @@SPId EXEC(@SQL) DROP DATABASE ‘yourDBname’ END GO CREATE DATABASE yourDBname GO /*********************************************************************************                                                         OR *********************************************************************************/ DECLARERead More