MSSQL语句创建DB

CREATE procedure proc_Create_DB

@createDBName varchar(200),@sqlUid varchar(100),@sqlPwd varchar(100),@c_companyCode char(12),@dbAdr varchar(50),@dbSize char(10),@D_syscode varchar(50),@db_url varchar(500),@db_name varchar(50)

 

as

Begin

--创建数据库

execute ('IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'''+@createDBName+''')'+

'DROP DATABASE ['+@createDBName+']'+

'; '+

'CREATE DATABASE ['+@createDBName+']'+ 

'; '+

'exec sp_addlogin '''+@sqlUid+''','''+@sqlPwd+''','''+@createDBName+'''')

;

 

--还原数据库并移动到数据库指定的盘符

execute('RESTORE DATABASE ['+@createDBName+']'+' FROM DISK='''+@db_url+''''+

' with '+

' move '''+@db_name+''' to ''C:/Program Files/Microsoft SQL Server/MSSQL/Data/'+@createDBName+'.mdf'','+

' move '''+@db_name+'_log'' to ''C:/Program Files/Microsoft SQL Server/MSSQL/Data/'+@createDBName+'_log.ldf'','+

' replace')

;

 

--修改数据库逻辑文件名与新建的数据名同名:

execute('Alter DataBase '+@createDBName+

' MODIFY FILE(NAME='+@db_name+',NEWNAME='''+@createDBName+''')'+

' Alter DataBASE '+@createDBName+

' MODIFY FILE(NAME='''+@db_name+'_log'',NEWNAME='''+@createDBName+'_log'')')

;

 

--添加用户

--给用户添加数据库中

--把用户添加在角色中 

 

execute ('use '+@createDBName+'; exec sp_grantdbaccess '''+@sqlUid+''''+

'; '+

'EXEC sp_addrolemember ''db_owner'', '''+@sqlUid+''''+

'; ')

;

 

insert into [com-edudb2009]..k_dbInfo(c_companyCode,D_syscode,D_dbName,D_dbUserName,D_dbpwd,D_dbAdr,D_dbSize) values (@c_companyCode,@D_syscode,@createDBName,@sqlUid,@sqlPwd,@dbAdr,@dbSize)

 

End

GO