If I say, How I can create a database dynamically? you most probably tell me “easy man”!!! , just put CREATE DATABASE script in dynamic query string with database name as a parameter and execute it, whenever you want. Absolutely correct!!!;
But If I say I have number of servers and don’t want to go and manually alter the script with it’s appropriate file-path location. Then, above idea will fail; because, we put file-path (log & data files) as a plain text in the query string.
To serve this purpose, SQL Server 2012 onward, very interesting parameters named “InstanceDefaultDataPath” & “InstanceDefaultLogPath” added in SERVERPROPERTY function.
This gives you default location, configured for data and log files respectively. Try below code ….
SELECT SERVERPROPERTY('InstanceDefaultDataPath') AS [Data File Path] , SERVERPROPERTY('InstanceDefaultLogPath') AS [Log File Path]