动用动态SQL创立数据库

/*实则笔者也搞不懂为啥要用SQL来创制,明明SQL
Server有图形化创设数据库多方便啊!*/
USE master;

​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’))
/*微软官方参数介绍https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-2017\*/

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

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

相关文章