Please Reply ! 1

[Saama5]

I want to use in Sql script which search the default Sql server path for database files. Is there any default function?

 

[tlbroadbent]

The default data path is stored in the registry not in a SQL server table. In SQL 2000 it is the DefaultData value in the following key. Look for a similar key in earlier versions.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Sqlsa]

Hi McDonald,
This may not be the correct way of doing it. But you can try it.

If we believe that wherever the data and log files are stored for MASTER database in SQL Server as DEFAULT PATH for databases, then you can use this.

SYSDATABASES has filenames, database names etc..

--gives the database name,data filename for all databases
select name,filename from sysdatabases

--database name, filepath for master database
select name,filename from sysdatabases where name='master'

--(1)
--this is to get the path of master data file
select reverse(substring(reverse(filename),charindex('\',reverse(filename),1),len(filename)))
from sysdatabases where name='master'

to get the path from filename, u can make good logic to extract the required path.

--there you go
--i got one more idea to extract it simply
--because we know the datafile name for MASTER
--(2)
select replace(filename,'MASTER.MDF','') from sysdatabases where name='master'


Statements (1) and (2) will give the same result.

But I am not sure whether is correct to extract the DEFAULT PATH from sysdatabases.

Need to look more in BOL...

Hope this helps.

Sreenivas
avnsr@hotmail.com

 

[Sqlsa]

Hi McDonald,
Sorry ..a small correction.

Use Master.dbo.systemdatabases instead of systemdatabases

Hope it works,

Sreenivas
avnsr@hotmail.com
--------------------