使用动态SQL创建数据库

/*其实我也搞不懂为什么要用SQL来创建,明明SQL
Server有图形化创建数据库多省事啊!*/
USE master;

CREATE PROC sp_job_LoadPorc
@command     nvarchar(4000),         –异步调用的存储过程或者要执行的
Transact-SQL 语句
@DatabaseName sysname=NULL,       
–在那个数据库中执行作业步骤,默认在当前数据库中
@jobdelay      int=5,               
–当前时间后的多少秒钟执行,该值大于等于5
@jobid        uniqueidentifier OUTPUT –定义的作业编号
AS
–作业名称,作业的执行时间
DECLARE @jobname sysname,@time int
SELECT @jobname=N’临时作业’
        +N’_’+LEFT(HOST_NAME(),40)
        +N’_’+CONVERT(char(19),GETDATE(),120)
        +N’_’+CAST(NEWID() as varchar(36)),
    @jobdelay=CASE
        WHEN ISNULL(@jobdelay,0)<5 THEN 5
        ELSE @jobdelay+1 END,
    @time=REPLACE(CONVERT(char(8),
        DATEADD(Second,@jobdelay,GETDATE()),
        108),’:’,”)

USE tempdb

​DECLARE @sqlstr nvarchar(max)
/*定义一个变量*/
DECLARE @database_name nvarchar(20) = ‘MyDB’;
/*这里输入需要创建的数据库名*/
DECLARE @file_name_d nvarchar(200) = CONVERT(sysname,
SERVERPROPERTY(‘InstanceDefaultDataPath’))
/*这里是利用SQL
Server的serverproperty这个函数来获取当前实例的默认数据文件位置和日志文件位置*/
DECLARE @file_name_l nvarchar(200) = CONVERT(sysname,
SERVERPROPERTY(‘InstanceDefaultLogPath’))
/*微软官方参数介绍

IF DB_ID(‘MyDB’) IS NOT NULL
    BEGIN
        PRINT ‘Database ‘ +@database_name+’ already exists’; 
–判断MyDB是否存在,存在的话就干掉,我也不知道为什么要这么写
        DROP DATABASE MyDB; –还是手动删除吧,不使用脚本删除
    END

–数据库名
IF DB_ID(@DatabaseName) IS NULL
    SET @DatabaseName=DB_NAME()

IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=’20秒一次的作业’)
    EXEC msdb.dbo.sp_delete_job @job_name=’20秒一次的作业’

ELSE
    
    BEGIN
        SET @sqlstr= ‘CREATE DATABASE’+’ ‘+@database_name+’ ‘
        SET @sqlstr =  @sqlstr +’ON’
        SET @sqlstr =  @sqlstr +'(‘    
        SET @sqlstr =  @sqlstr +’NAME = ‘+’ ‘+@database_name+’_dat,’
        SET @sqlstr =  @sqlstr +’FILENAME =
‘+””+@file_name_d+”+@database_name+’.mdf”,’
        SET @sqlstr =  @sqlstr +’SIZE = 10,’
        SET @sqlstr =  @sqlstr +’MAXSIZE = 50,’
        SET @sqlstr =  @sqlstr +’FILEGROWTH = 5′
        SET @sqlstr =  @sqlstr +’)’
        SET @sqlstr =  @sqlstr +’LOG ON’
        SET @sqlstr =  @sqlstr +'(   NAME = ‘+’
‘+@database_name+’_log,’
        SET @sqlstr =  @sqlstr +’FILENAME =
‘+””+@file_name_l+”+@database_name+’.ldf”,’
        SET @sqlstr =  @sqlstr +’SIZE = 5MB,’
        SET @sqlstr =  @sqlstr +’MAXSIZE = 25MB,’
        SET @sqlstr =  @sqlstr +’FILEGROWTH = 5MB’
        SET @sqlstr =  @sqlstr +’);’
        Print (@sqlstr) —
如果不想直接执行,使用使用print参数先把命令打印出来
        –exec (@sqlstr);   — 执行直接上面生成的动态SQL
        –Print ‘Datbaase ‘+@database_name +’ has been created using
default data and log location in the server configuration!!’
        /*打印已经创建成功的数据库名字*/
        –Print ‘Data file location =
‘+@file_name_d+@database_name+’.mdf’;
        /*打印创建的数据库的数据文件路径*/
        –Print ‘Log file location =
‘+@file_name_l+@database_name+’.ldf’;
        /*打印创建的数据库的日志文件路径*/
    END

–检查是否存在同名作业,存在则删除
IF EXISTS(SELECT * FROM msdb.dbo.sysjobs WHERE name=@jobname)
    EXEC msdb..sp_delete_job @job_name=@jobname

–定义创建作业
DECLARE @jobid uniqueidentifier
EXEC msdb.dbo.sp_add_job
    @job_name = N’20秒一次的作业’,
    @job_id = @jobid OUTPUT

–定义作业
EXEC msdb.dbo.sp_add_job
    @job_name = @jobname,
    @delete_level =3,           –作业执行后自动删除
    @job_id = @jobid OUTPUT

–定义作业步骤
DECLARE @sql nvarchar(400),@dbname sysname
SELECT @dbname=DB_NAME(),
    @sql=N’
        DECLARE @dt datetime
        SET @dt=DATEADD(Minute,1,GETDATE())
        WHILE @dt>GETDATE()
        BEGIN
            IF EXISTS(SELECT * FROM sysobjects WHERE
name=”tb_log”)            
                INSERT tb_log VALUES(CONVERT(char(8),GETDATE(),108))
            ELSE
                SELECT dt=CONVERT(char(8),GETDATE(),108) INTO tb_log
            WAITFOR DELAY ”00:00:20”
        END’
EXEC msdb.dbo.sp_add_jobstep
    @job_id = @jobid,
    @step_name = N’作业步骤名称’,
    @subsystem = ‘TSQL’,
    @database_name=@dbname,
    @command = @sql

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图