November 12, 2008 at 5:03 am
Hi,
If i run this query i am getting physical_name and all the file details for particular database.
SELECT * FROM [sys].[database_files]
I want to know all the database physical path (.mdf and .ldf path) for a particular database server. Is it possible in sql server 2005.
Appreciate for your help.
---
November 12, 2008 at 5:24 am
try this code to get the file paths for each db on a server.
DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]
INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'
SELECT
*
FROM
#DBFiles
November 12, 2008 at 5:31 am
steveb (11/12/2008)
try this code to get the file paths for each db on a server.
DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]
INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'
SELECT
*
FROM
#DBFiles
its really amazing steve.
very gud
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 12, 2008 at 5:58 am
or simply:
SELECT * FROM master.sys.master_files
if you want that matched up to the database name
select c.name,a.name as logicalname,a.physical_name from
master.sys.master_files a
inner join master.sys.sysdatabases c on a.database_id=c.dbid
order by dbid
---------------------------------------------------------------------
November 13, 2008 at 12:28 am
Thanks You Very Much GUYZZZ...
Great Work...! :w00t:
November 13, 2008 at 12:35 am
Please use following command :
select db_name(dbid),filename from sys.sysaltfiles
order by 1
November 13, 2008 at 3:50 am
steveb (11/12/2008)
try this code to get the file paths for each db on a server.
DROP TABLE #DBFiles
CREATE TABLE #DBFiles
(
[DBName] [nvarchar](100),
[Name] [nvarchar](100),
[Filename] [nvarchar](100)
)
ON [PRIMARY]
INSERT INTO
#DBFiles
EXEC sp_MSforeachdb 'Select ''?'' as DatabaseName, [?]..sysfiles.name,
[?]..sysfiles.filename From [?]..sysfiles'
SELECT
*
FROM
#DBFiles
Hi steveb,
I am not getting "sp_MSforeachdb" in Sql Server online help. Where shall i get the information for the same.
---
November 13, 2008 at 3:59 am
It's an undocumented stored procedure and does not appear in books on-line, am not sure why as i find it useful.
try this link for help
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy