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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply