listing file locations
-
Hi everyone.
Need to get a list of all the database file locations on a SQL 2000 Sp3 sever.
Tried , sp_helpdb , sp_helpfile ( gave current db ) , sp_helpdevice , and sp_databases. But did not seem to get correct results.
Seems I need a mix between sp_databases and sp_helpdevice to list all the database files.
Any thoughts appreciated.
Cheers
-
El,
Try this:
sp_msforeachdb 'Use [?] Select * From sysfiles'
HTH,
Ravinder
-
use the sp_helpfile system stored procedure.
sp_msforeachdb 'Use [?] exec sp_helpfile'
-
run following:
--------------------------------------------
CREATE TABLE #Files (
[DatabaseNM] sysname NOT NULL,
[name] [nchar] (128) NOT NULL ,
[fileid] [smallint] NOT NULL ,
[filename] [nchar] (260) NOT NULL ,
[filegroup] [nvarchar] (128) NULL ,
[nvarchar] (18) NULL ,
[maxsize] [nvarchar] (18) NULL ,
[growth] [nvarchar] (18) NULL ,
[usage] [varchar] (9) NOT NULL
)
EXEC master..sp_MSForeachdb 'USE ?
INSERT #Files
SELECT ''?'', name,
fileid,
filename,
filegroup_name(groupid),
CONVERT(nvarchar(15), size * 8) + N'' KB'',
CASE maxsize WHEN -1 THEN N''Unlimited''
else
CONVERT(nvarchar(15), maxsize * 8) + N'' KB'' end,
CASE status & 0x100000 WHEN 0x100000 THEN
CONVERT(nvarchar(3), growth) + N''%''
else
CONVERT(nvarchar(15), growth * 8) + N'' KB'' end,
CASE status & 0x40 WHEN 0x40 THEN ''log only'' else ''data only'' end
FROM sysfiles
ORDER BY fileid'
SELECT *
FROM #Files
ORDER BY DatabaseNM
DROP TABLE #Files
-----------------------------------
-
Hi, Will this not do ?
use master
select * from sysaltfiles
--Kishore
-
from BOL: "Under special circumstances, contains rows corresponding to the files in a database."
anyone know what these *special* circumstances may be?
Dan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply