CREATE DATABASE in default path

I have simple code

: CREATE DATABASE [asst]  
: ON (NAME = 'asst_dat', FILENAME = 'C:\data' , SIZE = 62, FILEGROWTH = 10%) 
: LOG ON (NAME = 'asst_log', FILENAME = 'C:\data' , SIZE = 146, FILEGROWTH = 10%)

How can I change the FILENAME parameter to create DB in the default installation path like C:\Program Files\Microsoft SQL 开发者_C百科Server\MSSQL10_50.ATASSIST\MSSQL. So why I need it: from version to version, from instance to instance of SQL Server this locations differs

First create the database and then alter file properties as needed.

CREATE DATABASE [DBName]
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName' , SIZE = 512MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
GO

ALTER DATABASE [DBName] MODIFY FILE
( NAME = N'DBName_log' , SIZE = 256MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

You can use the following variables, that respectively contain the root directory of your install and the data directory :

SELECT @@basedir, @@datadir;

Thanks, überjesus, I've simplified your code a little bit

DECLARE @rows varchar(MAX),
        @script nvarchar(MAX);
SET @rows = (SELECT physical_name AS current_file_location 
FROM sys.master_files 
where name = 'master');
SET @rows = Replace(@rows, 'master.mdf', '')
SELECT @rows;
set @script = 'CREATE DATABASE [assist1]
ON (NAME = ''asst_dat'', FILENAME = ''' + @rows  + 'assist1.mdf'' , SIZE = 62, FILEGROWTH = 10%)    
LOG ON (NAME = ''asst_log'', FILENAME = ''' + @rows + 'assist1_log.ldf'' , SIZE = 146, FILEGROWTH = 10%);'
exec(@script);

Thanks for a great idea!

try this

You can create a database without specifying file details, like:

CREATE DATABASE DatabaseName;

Assuming you are using SQL Server 2005 or 2008 this should do it. Although it's probably a bit longer than you expected :-) You could put the main part of the code into a function and call that every time you need to create a database.

declare @instance_name nvarchar(200), 
        @system_instance_name nvarchar(200), 
        @registry_key nvarchar(512), 
        @path_data nvarchar(260), 
        @path_log nvarchar(260), 
        @value_name nvarchar(20),
        @script nvarchar(4000);

set @instance_name = coalesce(convert(nvarchar(20), serverproperty('InstanceName')), 'MSSQLSERVER');

exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer';

/* determine default location for data files */
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultData', @path_data output;
if @path_data is null
begin
    /* this is only executed if we are using the default instance */
    set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path_data output;
    set @path_data = @path_data + '\Data';
end;

/* determine default location for log files */
exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'DefaultLog', @path_log output;
if @path_log is null
begin
    /* this is only executed if we are using the default instance */
    set @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
    exec master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', @registry_key, N'SQLDataRoot', @path_log output;
    set @path_log = @path_log + '\Data';
end;

set @script = 'CREATE DATABASE [asst] 
    ON (NAME = ''asst_dat'', FILENAME = ''' + @path_data  + '\yourfile.mdf'' , SIZE = 62, FILEGROWTH = 10%)
    LOG ON (NAME = ''asst_log'', FILENAME = ''' + @path_log + '\yourfile.ldf'' , SIZE = 146, FILEGROWTH = 10%);'

exec(@script);

You can not use variables in the CREATE DABASE statement. That's why you have to create a variable that holds the command and execute that as a script.