Question

怎么判断sql数据库是否存在，存在删除

Answer 1

判断数据库，如果存在则删除：

IF (EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE dbid=db_ID('dbname')))

DROP DATABASE dbname

如果提示：删除数据库时提示数据库正在被使用，无法删除(Cannot drop database databasename because it is currently in use)，使用：

IF (EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE dbid=db_ID('dbname')))

BEGIN

USE master

ALTER DATABASE dbname

SET single_user

WITH ROLLBACK IMMEDIATE

DROP DATABASE dbname

END

扩展资料

判断储存过程,如果存在则删除

IF (EXISTS(SELECT * FROM sysobjects WHERE name='procedurename' AND type='P'))

DROP PROCEDURE procedurename

判断触发器,如果存在则删除

IF (EXISTS(SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[triggername]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1))

DROP TRIGGER triggername

判断用户函数是否存在,如果存在则删除

此处type有两种: 'TF'- Table-Value Function 表值函数 'FN'- Scalar-Value Function 标量值函数

IF (EXISTS(SELECT * FROM sysobjects WHERE id=object_id(N'[dbo].[userfunction]') AND (type='FN' OR type='TF')))

DROP FUNCTION userfunction

Answer 2

如何判断数据库是否存在 执行下列的SQL，获得一张表，根据表的行数来判断。
select * from master..sysdatabases where name=N'所查询的数据库名'
if exists(select * from master.dbo.sysdatabases where name = 'yexinwinners')
begin
drop database yexinwinners
print 'yexinwinners己存在，己被删除'
end
else
begin
create database yexinwinners
on primary
(
name = yexinwinners_mdf,
filename = 'c:\yexinwinners.mdf',
size = 10mb,
maxsize = 50mb,
filegrowth = 25%
)
log on
(
name = yexinwinners_ldf,
filename = 'c:\yexinwinners.ldf',
size = 10mb,
maxsize = 50mb,
filegrowth = 25%
)
print 'yexinwinners 数据库创建成功'

end