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