创建数据库(脚本实现)

-- 创建历史数据库
if object_id('[dbo].[spr_create_his_db]') is not null
   drop procedure [dbo].[spr_create_his_db]
go

create proc [dbo].[spr_create_his_db]
(  
   @hisdbname varchar(30) -- 要创建的历史数据库名称
)
as
set nocount on

-- 当前数据库名
declare @strCurDBName varchar(30)

select @strCurDBName = name from master..sysDataBases 
where dbid = (select dbid from master..sysProcesses where spid = @@spid)

--print @strCurDBName

-- 如果VM2011为数据库名称
--if db_id('VM2011') = (select dbid from master..sysprocesses where spid = @@spid)
--begin
--    print 'VM2011 is Current DB.'
--end

declare @strFindCase varchar(30)
declare @strCurDBMdf varchar(260)
declare @strCurDBLdf varchar(260)

-- 数据存储文件
set @strFindCase = '%\' + @strCurDBName + '%.mdf'
select @strCurDBMdf = filename from sysfiles
where filename like @strFindCase

-- 日志文件
set @strFindCase = '%\' + @strCurDBName + '%.ldf'
select @strCurDBLdf = filename from sysfiles
where filename like @strFindCase

-- 生成要创建数据库的文件路径
declare @strCreateDBMdf varchar(260)
declare @strCreateDBLdf varchar(260)
set @strCreateDBMdf = left(@strCurDBMdf, len(@strCurDBMdf) - charindex('\', reverse(@strCurDBMdf)) + 1) + @hisdbname + '.mdf'
set @strCreateDBLdf = left(@strCurDBLdf, len(@strCurDBLdf) - charindex('\', reverse(@strCurDBLdf)) + 1) + @hisdbname + '_Log.ldf'

-- 创建数据库
declare @strCreateSql varchar(500)
set @strCreateSql = 
'create database ' + @hisdbname + '
on
(
name = ' + @hisdbname + ',
filename = ''' + @strCreateDBMdf + ''',
size = 100MB,
filegrowth = 100MB
)
log on
(
name = ' + @hisdbname + '_log,
filename = ''' + @strCreateDBLdf + ''',
size = 3MB,
filegrowth = 10%
)'
--print @strCreateSql

exec (@strCreateSql)

return
set nocount off