 Re: List all Databases
Derrick,
>I would like to produce a list of all Database/the Location of the
>Database/Logfiles. I assume that there is a 'system' table that contains
>this inof, but what is it
Below is a system stored procedure you can add to the master database
that I think will give you what you want. Yes, it uses undocumented
features. For this type of tool I do not think it matters.
Roy
CREATE PROCEDURE sp__dba_useful_info
@DB varchar(15) = '%'
AS
CREATE TABLE #files
([DB Name] char(15),
[Db/Log] char(4),
MB decimal(15,2),
[Pages (8k)] int,
[File Name] varchar(25),
[File Location] varchar(50),
Maxsize char(18),
Growth char(18))
exec sp_msforeachdb "insert #files
SELECT 'DB Name' = '?',
'Db/Log' = (case status & 0x40 when 0x40 then 'Log' else 'Data'
end),
'MB' = convert(dec(15,2),convert(dec(
15,2),size * 8)/1024),
'Pages (8k)' = size,
'File Name' = substring(name,1,25),
'File Location' = substring(filename,1,50),
'Maxsize' = (case maxsize when -1 then N'Unlimited'
else
convert(nvarchar(15), maxsize * 8) + N' KB'
end),
'Growth' = (case status & 0x100000 when 0x100000 then
convert(nvarchar(3), growth) + N'%'
else
convert(nvarchar(15), growth * 8) + N' KB' end)
FROM ?..sysfiles
ORDER by fileid"
SELECT *
FROM #files
WHERE [DB Name] LIKE @DB
drop table #files
Report this post to a moderator
|
10-06-02 03:15 AM
