Script of Creating Database and Tables at Runtime Using Procedure

Script of Creating Database and Tables at Runtime Using Procedure

This was a Requirement from one of my  friend who asked me to create a Procedure that will create Database and tables at Runtime.So, below Procedure will take DBName as Input Parameter and Create Database with the same name, I am creating one table inside Procedure you can add multiple according to your requirement.

--Script of Creating Database and Tables at Runtime Using Procedure 
--Tested  in SQL 2008 R2
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[Proc_CreateDbObject] 
@DbName nvarchar(100) 
AS
BEGIN
DECLARE @cmd varchar(8000)
DECLARE @CreateDB varchar(100)
DECLARE @ErrMsg varchar(150)

-- SET NOCOUNT ON added to prevent returning no. of records
SET NOCOUNT ON;

IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @DbName)
BEGIN
SET @CreateDB = N'CREATE DATABASE ' + @DBName
EXEC(@CreateDB)
END
ELSE
BEGIN
SET @ErrMsg = N'Database ' + @DBName + ' already exists. '
RAISERROR(@ErrMsg, 16, 1)
RETURN -1
END

BEGIN TRAN

SET @cmd = N'CREATE TABLE [' + @DBName + N'].' + N'[dbo].[Employee](
[empid] [int] IDENTITY(1,1)  NOT NULL,
[empname] [varchar](50) NULL,
[address] [varchar](50) NULL,
[email] [varchar](50) NULL,
[phonenumber] [varchar](50) NULL)
'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while creating datbase objects.', 16, 1)
SET @CreateDB = 'DROP DATABASE ' + @DBName
EXEC(@CreateDB)
RETURN -1
END
ELSE
BEGIN
COMMIT TRAN
PRINT N'Created successfully'
PRINT N''
RETURN 0
END
end

--Execute Procedure
exec [Proc_CreateDbObject] 'test_DB'

output: Created successfully