SQL Server: Extract SQL Server Instance Path Dynamically

Script to check the SQL Server Instance Path

Problem:

For database creation we specify the default path including the drive name e.g. (C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA).

If SQL Server Instance is installed on the different drive or path e.g. (d:,e:) in that case database creating script may fail.

**Solution: Following script will be helpful if we need to extract the **instance path and create the database dynamically.

USE [master]
 
declare @InstancePath as varchar(1000)
declare @fileName as varchar(1000)
select  @InstancePath= filename from sysfiles
select  @fileName = SUBSTRING(@InstancePath,0,LEN(@InstancePath)-CHARINDEX('\',REVERSE(@InstancePath))+1)
declare @stringQuiery as nvarchar(4000)
 
Set @stringQuiery = 'CREATE DATABASE  [APPDB] ON  PRIMARY
(NAME = N''APPDB'', FILENAME = N''' + @fileName + + '\<DatabaseName>.mdf' + ''' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
(NAME = N''APPDB_log'', FILENAME = N''' + @fileName + '\<DatabaseName>_log.ldf' + ''' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)'
 
EXEC    sp_executeSQL @stringQuiery