USE MASTER

 

IF (SELECT COUNT(*) FROM sysdatabases WHERE name = '<DatabaseName, Varchar, MyDatabase>') = 1
BEGIN
	PRINT 'There is already a database installed with the name <DatabaseName, Varchar, MyDatabase>'
END
ELSE
BEGIN
	DECLARE @StartInstall BIT
	DECLARE @MdfPath NVARCHAR(255)
	DECLARE @LdfPath NVARCHAR(255)
	DECLARE @MdfName NVARCHAR(255)
	DECLARE @LdfName NVARCHAR(255)
	SET @StartInstall = 0
	SET @MdfPath = '<Path_Mdf, varchar, D:\DB\>'
	SET @LdfPath = '<Path_Ldf, varchar, D:\DB\>'
	SET @MdfName = '<Path_Mdf, varchar, D:\DB\><DatabaseName, Varchar, MyDatabase>.mdf'
	SET @LdfName = '<Path_Ldf, varchar, D:\DB\><DatabaseName, Varchar, MyDatabase>.ldf>'
	

	-- Check if path for MDF file exists
	CREATE TABLE #xp_fileexist_output ([FILE_EXISTS] int not null, [FILE_IS_DIRECTORY] int	not null, [PARENT_DIRECTORY_EXISTS] int not null)
	INSERT INTO #xp_fileexist_output EXEC master.dbo.xp_fileexist @MdfPath
	IF exists ( SELECT * FROM #xp_fileexist_output WHERE FILE_IS_DIRECTORY = 0 )
	BEGIN
		print 'Directory for MDF file does not exist.'
		SET @StartInstall = 0
	END
	ELSE
	BEGIN
		DELETE FROM #xp_fileexist_output
		SET @StartInstall = 1
		-- Check if path for LDF file exists
		INSERT INTO #xp_fileexist_output EXEC master.dbo.xp_fileexist @LdfPath
		IF exists ( SELECT * FROM #xp_fileexist_output WHERE FILE_IS_DIRECTORY = 0 )
		BEGIN
			print 'Directory for LDF file does not exist.'
			SET @StartInstall = 0
		END
		ELSE
		BEGIN
			DELETE FROM #xp_fileexist_output
			-- Check if no other mdf file with the same name exists
			INSERT INTO #xp_fileexist_output EXEC master.dbo.xp_fileexist @MdfName
			IF exists ( SELECT * FROM #xp_fileexist_output WHERE FILE_EXISTS = 1 )
			BEGIN
				print 'There is already a MDF file with the name: ' + @MdfName
				SET @StartInstall = 0
			END
			ELSE
			BEGIN
				DELETE FROM #xp_fileexist_output

				-- Check if no other mdf file with the same name exists
				INSERT INTO #xp_fileexist_output EXEC master.dbo.xp_fileexist @ldfName
				IF exists ( SELECT * FROM #xp_fileexist_output WHERE FILE_EXISTS = 1 )
				BEGIN
					PRINT 'There is already an LDF file with the name: ' + @LdfName
					SET @StartInstall = 0
				END
				ELSE
					SET @StartInstall = 1
				END
		END
	END

	DELETE FROM #xp_fileexist_output


	IF @StartInstall = 1
	BEGIN
		print 'Start install'

		DROP TABLE #xp_fileexist_output

		CREATE DATABASE <DatabaseName, Varchar, MyDatabase>  ON (NAME = N'<DatabaseName, Varchar, MyDatabase>', 
			FILENAME = N'<Path_Mdf, varchar, D:\DB\><DatabaseName, Varchar, MyDatabase>.mdf' , 
			SIZE = 24, 
			FILEGROWTH = 10%) 
			LOG ON (NAME = N'<DatabaseName, Varchar, MyDatabase>_log', 
			FILENAME = N'<Path_Ldf, varchar, D:\DB\><DatabaseName, Varchar, MyDatabase>.ldf' , 
			SIZE = 10, 
			FILEGROWTH = 10%)
		 COLLATE Latin1_General_CI_AS

		IF CONVERT(INT, SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR), 1, 1)) >= 9
		BEGIN
			PRINT 'Set compatibility to SQL 2000'
			EXEC dbo.sp_dbcmptlevel @dbname=N'<DatabaseName, Varchar, MyDatabase>', @new_cmptlevel=80
			
		END

		exec sp_dboption N'<DatabaseName, Varchar, MyDatabase>', N'autoclose', N'false'

           /* 2MB of CREATE tables, CREATE sp etc. 
           ............................
           */
	END -- End install
END -- DB exist check