Technical Article

Create Database using dynamic SQL

,

Create database using dynamic SQL

Script creates a database,picks up the default locations for data and log file placements from server configuration, Give it a go!!

Useful in scenario's where your DBA doesn't like you and doesn't tell you that he doesn't like you!! So give him this script and ask him to trust you for creating the database, ill soon post something to create tables dynamically, Lets test your DBA's

USE master;

​DECLARE @sqlstr nvarchar(max)
DECLARE @database_name nvarchar(20) = 'MyDB';
DECLARE @file_name_d nvarchar(200) = CONVERT(sysname, SERVERPROPERTY('InstanceDefaultDataPath'))
DECLARE @file_name_l nvarchar(200) = CONVERT(sysname, SERVERPROPERTY('InstanceDefaultLogPath'))

​

IF DB_ID('MyDB') IS NOT NULL
    BEGIN
        PRINT 'Database ' +@database_name+' already exists, Please drop manually, i do not like like dropping it using automated scripts'; 
        --DROP DATABASE SQLBazaar; -- Optional drop if exists, i do not like dropping objects using automation scripts
    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 first if you want to see the output
        EXEC (@sqlstr);
        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

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating