Paths to data files

Question

Post reply

Paths to data files

coenzyme77

SSCommitted

Points: 1669
More actions
December 11, 2007 at 9:54 am

#127233

Hi everyone,
I need to provide the directory paths for all my database .mdf, ndf, and .log files. to an IT staff person. I could right click each data base, select files and provide the path information. Is there an information_Schema, Catalog and/or MetaData View that will provide a table of paths, preferably within each instance, for both 2K and ’05 versions? Thanks for everyone for being there to help.
SQL 2K and ’05 standard
Ed

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

This works on 7.0, 2000, and 2005

Get Server Database File Information

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 2

Well, since it needs to work for 2005 as well as 2000 you can't use the DMVs or DMFs. This will do the trick however:

EXEC sp_MSForEachDB

'SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,

''?'' as DatabaseName,

[?]..sysfiles.name,

[?]..sysfiles.filename

From [?]..sysfiles'

You can tap into more metadata from the system tables as well if you need to using this method. Slap my hands for using the system tables, but it works!

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

coenzyme77 SSCommitted Points: 1669 More actions · Answer 3

Fantastic! You guys are the best. The results are exactly what is needed. Its there a way to place the results so it can be grabbed

by DTS or SSIS to send to a flat fole.

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 4

Something like this will place everything into a single result set. Just drop the temp table #Results when done.

CREATE TABLE #Results (SERVER VARCHAR(100), DatabaseName VARCHAR(100), NAME VARCHAR(100), filename VARCHAR(200))

EXEC sp_MSForEachDB

'INSERT INTO #Results(Server, DatabaseName, Name, filename)

SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,

''?'' as DatabaseName,

[?]..sysfiles.name,

[?]..sysfiles.filename

From [?]..sysfiles'

SELECT * FROM #Results ORDER BY SERVER, DatabaseName, NAME

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 5

I'd use two different versions of the script, one for each SQL Server version, especially as the 2000 one might not work in 2008. Just return the same values so your table can hold them, but use a proc or function to get the data in the best way for each version.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 6

Anyone ever hear of sp_HelpFile? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 7

Returns more info than what is asked for and the scope is the current db just as the other approach.

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 8

Yuh huh... you watch... the next thing the boys from ops will ask for is a summary of the files sizes 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 9

Better than the Analyst I had who wanted to open up a .ldf in notepad to do a search for a string value! That was one for sqldumbass.com let me tell you!

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 10

Heh... sorry... don't see the connection 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)