如何判断数据库是否存在
这篇文章是接上一篇《如何编写程序使得数据库服务器变化时代码不需要重要编译》而写的,本来该写在上一篇中,但是因为这一部分很重要,所以决定单独写出来了。
在创建数据库时,经常要判断服务器中是否存在某个数据库,然后再决定是不是要进行下一步操作。
以下是一些简单地示例,希望会有用。
1: --判断数据库是否存在
2: if exists(select * from master.dbo.sysdatabases where name = 'SkyBusiness')3: begin4: drop database SkyBusiness5: print 'SkyBusiness 已经存在,已被删除'6: end7: else8: begin9: create database SkyBusiness10: on primary11: (
12: name=SkyBusiness_mdf,
13: filename='c:\SkyBusiness.mdf',14: size=10mb,15: maxsize=50mb,
16: filegrowth=25%
17: )
18: log on19: (
20: name=SkyBusiness_ldf,
21: filename='c:\SkyBusiness.ldf',22: size=10mb,23: maxsize=50mb,
24: filegrowth=25%
25: )
26: print 'SkyBusiness数据库创建成功'27: end28:
29: --建聚集索引
30: create clustered index index_yexinwinners31: on tablename(column_name)32: with fillfactor = 1033:
34: --建非聚集索引
35: create nonclustered index index_yexinwinners36: on tablename(column_name)37: with fillfactor = 1038:
39: --建视图
40: create view view_name41: as42: select * from pubs.titles(sql语句,任意)43:
44: --建检查视图
45: create view view_name46: as47: select * from pubs.titles(sql语句,任意)48: with check option49:
50: --建加密视图
51: create view view_name52: with encryption53: as54: select * from pubs.titles(sql语句,任意)55:
56: --建表
57: create table tablename58: ( ---(字段名,字段类型 自己任意)
59: stuID int not null identity(1,1) primary key,60: stuName varchar(30) not null,61: stuAge char(3) not null62: )
63:
64: --添加check约束
65: alter table tablename66: add check(stuAge >0 and stuAge <100)67:
68: --添加外键
69: alter table tablename70: add foreign key (stuName)71: references Xtablename(stuName)72:
73: --添加唯一约束
74: alter table tablename75: add unique(stuID)76:
77: ---建事务
78: begin tranaction79: update Xtable set Money = Money + 10000 where ID = 180: update Xtable set Money = Money - 10000 where ID = 381: if(@@error <> 0)82: begin83: print '转帐失败'84: rollback transaction85: end86: else87: begin88: print '转帐成功'89: commit transaction90: end91:
92: --建游标
93: declare cursor_name Cursor94: for select * from northwind.product95:
96: --建更新游标
97: declare cursor_name Cursor98: for select * from northwind.product99: for update100:
101: --建随意移动游标
102: declare cursor_name Cursor scroll103: for select * from northwind.product104:
105: --使用游标
106: open cursor_name107:
108: --关闭游标
109: close cursor_name110:
111: --删除游标
112: deallocate cursor_name113:
114: --移动游标
115:
116: fetch next -- 下一条记录117: fetch last --最后一条记录118: fetch first --第一条记录119: fetch prior --上一条记录120: fetch ABSOLUTE n -- 绝对位置