if exists (select * from sysobjects
where id = object_id('dbo.sp_createdatabase') and sysstat & 0xf = 4)
drop procedure dbo.sp_createdatabase
GO
create procedure sp_createdatabase;1
(
@dbname varchar(30),
@size int,
@size_data int = null,
@size_log int = null,
@pwd sysname = null
)
as
/********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 18 Dec 1997 */
/* Description: This stored procedure can be used to create a database */
/* by just providing a name or optional size info. */
/* The pwdcompare function is undocumented & is used to */
/* password protect the SP. */
/********************************************************************************/
/* Resources : https://umachandar.com/resources.htm */
/********************************************************************************/
if pwdcompare(@pwd, '!1!J!,HXB!F&W5M<') = 0
begin
raiserror ('Only Authorized users can execute this sp. Please contact Admin group.',-1,-1)
return(1)
end
if suser_id() <> 1
begin
raiserror ('Only SA can execute this sp.',-1,-1)
return(1)
end
declare @db varchar(30), @now varchar(8), @retcode int,
@devdata varchar(30), @devlog varchar(30),
@devdatasize varchar(255), @devlogsize varchar(255)
if @size is null
begin
raiserror (15268,-1,-1,@dbname)
return(1)
end
if @size < 1
begin
raiserror (15262,-1,-1)
return(1)
end
if (@size <> @size_data + @size_log)
begin
raiserror ('Total size specified does not match the data & log file sizes.',-1,-1)
return(1)
end
if (@size_data is null and @size_log is null)
select @size_data = ceiling((3./5.)*@size), @size_log = floor((2./5.)*@size)
select @now = convert(varchar(2), datepart(mm, getdate()))
select @db = @dbname + convert(varchar, getdate(), 112)
select @devdata = @db + '_data', @devlog = @db + '_log',
@devdatasize = str(@size_data), @devlogsize = str(@size_log)
exec @retcode = sp_validname @dbname
if @retcode <> 0
return(1)
-- check for conflicting database name.
if db_id(@db) is not null
begin
raiserror ('Database name is already in use. Please use another name.',-1,-1)
return(1)
end
exec @retcode = sp_createdevice @devdata, @size_data
if @retcode <> 0
return(1)
exec sp_createdevice @devlog, @size_log
if @retcode <> 0
begin
exec sp_dropdevice @devdata, delfile
return(1)
end
exec ('create database ' + @db + ' on ' + @devdata + ' = ' + @devdatasize +
' log on ' + @devlog + ' = ' + @devlogsize)
if db_id(@db) is null
begin
exec sp_dropdevice @devdata, delfile
exec sp_dropdevice @devlog, delfile
return(1)
end
return(0)
GO
create procedure sp_createdatabase;2
(
@dbname varchar(30),
@size int,
@size_data int = null,
@size_log int = null
)
as
/********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 18 Dec 1997 */
/* Description: This SP can be used to run the creation of a database as*/
/* a task. These Sps allow even normal users to create dbs.*/
/* The databases are actually created by a task running as */
/* a user with sysadmin privileges. This SPs demonstrates */
/* several tricks like finding free space in a drive, file */
/* details etc. */
/********************************************************************************/
declare @retcode int,
@mesg varchar(255),
@mesg_aux varchar(255),
@exec_str varchar(255),
@file varchar(255),
@taskname varchar(100),
@sqlpath varchar(30),
@drive varchar(3),
@freespace float
if user_id() <> 1
begin
raiserror ('Only DBO can perform this action.',-1,-1)
return(1)
end
exec @retcode = sp_validname @dbname
if @retcode <> 0
return(1)
if @size is null
begin
raiserror (15268,-1,-1,@dbname)
return(1)
end
if @size < 1
begin
raiserror (15262,-1,-1)
return(1)
end
if (@size <> @size_data + @size_log)
begin
raiserror ('Total size specified does not match the data & log file sizes.',-1,-1)
return(1)
end
-- check for conflicting database name.
if db_id(@dbname) is not null
begin
select @mesg = 'Database name is already in use. Please use another name.'
raiserror (@mesg,-1,-1)
return(1)
end
-- check for conflicting logical device names.
if exists(select name from sysdevices where name = @dbname + '_data')
or
exists(select name from sysdevices where name = @dbname + '_log')
begin
select @mesg = 'Unable to create database.' + char(10) +
'The logical device names generated conflicts with existing devices.'
+ char(10) +'Contact System Administrator.'
raiserror (@mesg,-1,-1)
return(1)
end
-- check for conflicting physical device names.
select @drive = 'E:\'
select @sqlpath = @drive + 'sqldumps\'
create table #file_details (name varchar(30) null, size int null, creationdate int null,
creationtime int null, lastwrittendate int null, lastwrittentime int null,
lastaccesseddate int null, lastaccessedtime int null, attributes int null)
select @file = @sqlpath + @dbname + '_data.dat'
insert #file_details exec master..xp_getfiledetails @file
select @file = @sqlpath + @dbname + '_log.dat'
insert #file_details exec master..xp_getfiledetails @file
if exists(select name from #file_details)
begin
select @mesg = 'Unable to create database.' + char(10) +
'The physical device names generated conflicts with existing devices.'
+ char(10) + 'Contact System Administrator.'
raiserror (@mesg,-1,-1)
return(1)
end
-- check for available space.
/*
-- This (master..xp_availablemedia SP) didn't work for large drives.
create table #media_info (name varchar(255), low int, high int, type tinyint)
insert #media_info exec master..xp_availablemedia
if ((select floor(low/(1024*1024))-50-@size from #media_info where upper(name) = @drive) < 0)
begin
select @mesg = 'Not enough space on disk to accomodate the database. ' +
'Free space available on disk is: ' +
ltrim(str(floor(low/(1024*1024))-50)) + ' Megabytes.'
from #media_info
raiserror (@mesg,-1,-1)
return(1)
end
*/
create table #media_info (output varchar(255) null)
insert #media_info exec master..xp_cmdshell 'dir/w/-c e:\pagefile.sys'
select @freespace = floor((convert(float, rtrim(ltrim(substring(output, 1,
charindex('bytes free', output)- 1))))/(1024*1024))) - 50
from #media_info
where charindex('bytes free', output) > 0
if (@freespace - @size < 0)
begin
select @mesg = 'Not enough space on disk to accomodate the database. ' +
'Free space available on disk is: ' +
Convert(varchar, @freespace) + ' Megabytes.'
from #media_info
raiserror (@mesg,-1,-1)
return(1)
end
if @size >50
select @mesg_aux = char(10) + 'NOTE: The size specified is large, ' +
'creation of database will take few minutes.'
-- check the task also, if not present, then create.
select @taskname = 'Create Db - ' + SYSTEM_USER
create table #task_info (name varchar(100), id int, subsystem varchar(30),
server varchar(30) null, username varchar(30) null, dbname varchar(30), enabled tinyint)
insert #task_info exec @retcode = msdb..sp_helptask @taskname
if @retcode <> 0
return(1)
-- fire the task for creating the database.
select @exec_str = 'sp_createdatabase;3 ''' + @dbname + ''', ' +
ltrim(str(@size)) + ', ' + host_name() +
', ''' + @taskname + '''' +
case when (@size_data is null and @size_log is null)
then null
else ', ' + ltrim(str(@size_data)) + ', ' + ltrim(str(@size_log))
end
if exists(select name from #task_info where name = @taskname)
exec @retcode = msdb..sp_updatetask @taskname, @username = 'dbo', @command = @exec_str
else
exec @retcode = msdb..sp_addtask @taskname, 'TSQL', @username = 'dbo',
@databasename = 'master', @command = @exec_str, @enabled = 0
if @retcode <> 0
return(1)
exec @retcode = msdb..sp_runtask @taskname
if @retcode <> 0
return(1)
select @mesg = 'Creation of database started. You will receive an acknowledgement ' +
'after the process.' + @mesg_aux + char(10) + 'Please wait and relax.'
raiserror(@mesg, -1, -1)
return(0)
GO
create procedure sp_createdatabase;3
(
@dbname varchar(30),
@size int,
@machine varchar(30),
@taskname varchar(100),
@size_data int = null,
@size_log int = null
)
as
/********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 18 Dec 1997 */
/* Description: This SP is run from a SQLExecutive task. */
/********************************************************************************/
declare @retcode int,
@mesg varchar(255),
@exec_str varchar(255),
@vdevno int,
@size_in_blocks int,
@logical_data varchar(30),
@logical_log varchar(30),
@physical varchar(255),
@sqlpath varchar(30),
@drive varchar(3)
if (@size_data is null and @size_log is null)
select @size_data = ceiling((3./5.)*@size),
@size_log = floor((2./5.)*@size),
@machine = isnull(@machine, host_name())
exec @retcode = msdb..sp_updatetask @taskname, @username = 'guest', @command = ''
if @retcode <> 0
begin
select @mesg = 'Unable to update the ''''Create Database'''' task. ' +
'Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
/* Get next available device number for the data device. */
select @vdevno = null
select @vdevno = (min(low)/0x01000000)+1
from sysdevices d1
where low/0x01000000 between 0 and 254
and not exists
(select * from sysdevices d2
where d2.low/0x01000000 =
(d1.low/0x01000000)+1)
if @vdevno is null or @@error <> 0
begin
raiserror(15054,-1,-1)
select @mesg = 'No more available device numbers. Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
select @drive = 'E:\'
select @sqlpath = @drive + 'sqldumps\'
-- create the data device file
select @logical_data = case when datalength(@dbname) > 25 then substring(@dbname, 1, 25) + '_data'
else @dbname + '_data'
end,
@physical = @sqlpath + @dbname + '_data.dat',
@size_in_blocks = case when @size_data is null then ceiling((3./5.)*@size*512.)
else (@size_data*512.)
end
disk init
name = @logical_data,
physname = @physical,
size = @size_in_blocks,
vdevno = @vdevno
if @@error <> 0
begin
raiserror(15263,-1,-1,'data')
select @mesg = 'Could not create the ''''data'''' device. Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
/* Get next available device number for the log device. */
select @vdevno = null
select @vdevno = (min(low)/0x01000000)+1
from sysdevices d1
where low/0x01000000 between 0 and 254
and not exists
(select * from sysdevices d2
where d2.low/0x01000000 =
(d1.low/0x01000000)+1)
if @vdevno is null or @@error <> 0
begin
raiserror(15054,-1,-1)
exec sp_dropdevice @logical_data, delfile
select @mesg = 'No more available device numbers. Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
-- create the log device file
select @logical_log = case when datalength(@dbname) > 26 then substring(@dbname, 1, 26) + '_log'
else @dbname + '_log'
end,
@physical = @sqlpath + @dbname + '_log.dat',
@size_in_blocks = case when @size_log is null then floor((2./5.)*@size*512.)
else (@size_log*512.)
end
disk init
name = @logical_log,
physname = @physical,
size = @size_in_blocks,
vdevno = @vdevno
if @@error <> 0
begin
raiserror(15263,-1,-1,'log')
select @mesg = 'Could not create the ''''log'''' device. Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
select @exec_str = 'create database '+@dbname+' on '+@logical_data+
' = '+ltrim(str(@size_data))+' log on '+@logical_log+
' = '+ltrim(str(@size_log))
exec(@exec_str)
if @@error <> 0
begin
exec sp_dropdevice @logical_data, delfile
exec sp_dropdevice @logical_log, delfile
select @mesg = 'Unable to create the database. Contact System Administrator',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
raiserror (@mesg,-1,-1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(1)
end
-- Add all esi users to the database.
select @exec_str = 'use ' + @dbname + ' exec sp_addesiusers'
exec(@exec_str)
select @mesg = 'Database ' + @dbname + ' has been successfully created.',
@exec_str = 'master..xp_cmdshell ''net send ' + @machine + space(1)
raiserror (@mesg,-1,-1)
exec(@exec_str + '"' + @mesg + '"'', no_output')
return(0)
GO
This page was last updated on May 01, 2006 04:28 PM.