Mapped to the drive - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Mapped to the drive

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-23 : 03:17:34

Hi,

Could anyone pass on the query to identify the .mdf, .ldf and .ndf mapped to the drive irrespective of the databases in the server.

I have the query which displays me the files that is mapped to the given database id.

SELECT db.name AS 'db_name',
(select 'file_type' = CASE WHEN alt.groupid = 1 THEN 'data'
WHEN alt.groupid = 0 THEN 'log'
END) as 'file_type',
alt.name AS 'Name', alt.filename as 'filename',
(alt.size * 8 / 1024) 'file size(MB)' /*File size, in 8-kilobyte (KB) pages.*/
FROM master..sysdatabases db JOIN master..sysaltfiles alt
ON db.dbid = alt.dbid
WHERE db.dbid = '21'
ORDER BY db.name

Thanks in advance.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-12-23 : 16:36:44

-- not sure what you are looking.
-- Is this what you are looking for?

SELECT db.name AS 'db_name'
,(SELECT 'file_type' = CASE WHEN alt.groupid = 1 THEN 'data'
WHEN alt.groupid = 0 THEN 'log'
END) AS 'file_type'
,alt.name AS 'Name'
,alt.filename AS 'filename'
,(alt.size * 8 / 1024) 'FILE size(MB)' /*FILE size, IN 8-kilobyte (KB) pages.*/
FROM master..sysdatabases db JOIN master..sysaltfiles alt
ON db.dbid = alt.dbid
ORDER BY db.name

------------------------
I think, therefore I am - Rene Descartes

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-24 : 00:45:11

Yeh, thanks its working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources