-- 创建历史数据库
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