| Author |
Topic |
|
scootermcfly
Yak Posting Veteran
66 Posts |
 Posted - 2003-04-16 : 17:21:01
|
| I would like to get the default directory where the data files are stored and I can't seem to find anything in BOL about this. Is there an easy way to get this? Or should I just parse the path from the FileName field in the sysfiles table?Thanks,Scooter McFly |
|
|
kp_kumar
Starting Member
1 Post |
Posted - 2003-04-16 : 18:41:46
|
| see sp_MSget_setup_paths procedure , from master dbkris |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 18:43:47
|
| Well the database files do not have to be located in the default data directory, but here is how to find out where the default data directory exists:For the primary instance:[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup]"SQLDataRoot"For a named instance:[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Setup]"SQLDataRoot"I don't know why they are in different locations.To get the information using T-SQL, you'll want to call xp_regread (example is for primary instance):DECLARE @regvalue varchar(100)EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\MSSQLServer\Setup', @value_name='SQLDataRoot', @value=@regvalue OUTPUT, @output = 'no_output'PRINT @regvalueThe value will not include the data directory. So for example, if your default data directory is E:\MSSQL\Data, @regvalue will give you E:\MSSQL.HTH,Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-16 : 18:44:54
|
quote:
see sp_MSget_setup_paths procedure , from master db
Hmmm, that stored procedure doesn't give me any output. It just says that it ran successfully.[EDIT]Nevermind, I get values now. I was being stupid for a second[/EDIT]DECLARE @sql_path NVARCHAR(260)DECLARE @data_path NVARCHAR(260)EXEC master.dbo.sp_MSget_setup_paths @sql_path OUTPUT, @data_path OUTPUTPRINT @data_PathIt turns out that sp_MSget_setup_paths does pretty much the same as my code.  (I looked at the code in the stored proc)TaraEdited by - tduggan on 04/16/2003 18:54:31Edited by - tduggan on 04/16/2003 18:55:00 |
|
|
|
scootermcfly
Yak Posting Veteran
66 Posts |
Posted - 2003-04-17 : 09:09:01
|
| Thanks all, I think I might just read from the sysfiles table for right now. My problem is that I want to create a filegroup and file on a client's server and I am trying to create a script that will do this, but I need to have the path info to use in the ALTER DATABASE ADD FILE statement.So this is what I am using to find out the physical path to the default data file in the database where this will run:Select Reverse(substring(ltrim(reverse([FileName])),charindex('\',Ltrim(reverse([Filename]))),datalength(Ltrim(reverse([Filename]))))) AS DataFilePathFrom sysfiles Where groupid = 1Scooter McFly |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-17 : 12:12:17
|
| You shouldn't use system tables in any scripts especially installation scripts. MS says never to do this because the structure and name could change in the next version, however the stored procedures that do the same work will seldom change. Even though you're not thinking about the next version yet since it isn't even out, you shouldn't write scripts that might not port to the new version.Just a suggestion of course.Tara |
|
|
|
gvphubli
Yak Posting Veteran
54 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
SQL Data File Path