Getting dtabase information using sp_MSForEachDb

Question

Post reply

Getting dtabase information using sp_MSForEachDb

SirFalcon60

SSC Eights!

Points: 838
More actions
June 15, 2009 at 9:11 am

#211061

Okasy I have been racking my brain to understand why this will not use the 'use' cammond in the first select statement. the inner statement works fine when the DB name is phyiscal added. Would someone please eyeball this and tell me where I'm making my mistake in geeting the entire command to work.
Regards
DECLARE @command VARCHAR(5000);
SELECT @command = 'Use [' + '?' + ']
Select @@servername AS ServerName,
CAST(sysfiles.size /128.0 AS int) AS FileSizeMB,
sysfiles.name AS LogicalFileName,
sysfiles.filename AS PhysicalFileName,
CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , 'status' )) AS Status,
CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , 'Recovery' )) AS RecoveryMode,
convert(decimal(12,2),round(sysfiles.size/128.000,2)) as FileSizeMB,
convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB,
convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB,
CONVERT ( VARCHAR(10), GETDATE(),111) AS Date
from dbo.sysfiles
EXEC sp_MSForEachDB @command

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

DECLARE @command VARCHAR(5000);

SELECT @command = 'Use [' + '?' + ']'

Select @@servername AS ServerName,

CAST(sysfiles.size /128.0 AS int) AS FileSizeMB,

sysfiles.name AS LogicalFileName,

sysfiles.filename AS PhysicalFileName,

CONVERT (sysname, DATABASEPROPERTYEX( '?' , 'status' )) AS Status,

CONVERT (sysname, DATABASEPROPERTYEX( '?' , 'Recovery' )) AS RecoveryMode,

convert(decimal(12,2),round(sysfiles.size/128.000,2)) as FileSizeMB,

convert(decimal(12,2),round(fileproperty(sysfiles.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB,

convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB,

CONVERT ( VARCHAR(10), GETDATE(),111) AS Date

from dbo.sysfiles

EXEC sp_MSForEachDB @command

I've made the changes in bold and its running fine.

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 2

Above post is not correct.. Apologies..

still working on it..

Pradeep Singh

ps. One Orange Chip Points: 29422 More actions · Answer 3

DECLARE @command VARCHAR(5000);

SELECT @command = 'Use ['+ '?'+ ']

Select @@servername AS ServerName,

CAST(sysfiles.size /128.0 AS int) AS FileSizeMB,

sysfiles.name AS LogicalFileName,

sysfiles.filename AS PhysicalFileName,

CONVERT (sysname, DATABASEPROPERTYEX( ''[?]'' , ''status'' )) AS Status,

CONVERT (sysname, DATABASEPROPERTYEX( ''[?]'' , ''Recovery'' )) AS RecoveryMode,

convert(decimal(12,2),round(sysfiles.size/128.000,2)) as FileSizeMB,

convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB,

convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB,

CONVERT ( VARCHAR(10), GETDATE(),111) AS Date

from dbo.sysfiles'

--print @command

EXEC sp_MSForEachDB @command

This is working fine except in Status and RecoveryMode columns where getting NULL values 🙁

Pradeep Singh

SirFalcon60 SSC Eights! Points: 838 More actions · Answer 4

Got It

DECLARE @command VARCHAR(5000);

SELECT @command = 'Use ['+ '?'+ ']

Select @@servername AS ServerName,

CAST(sysfiles.size /128.0 AS int) AS FileSizeMB,

sysfiles.name AS LogicalFileName,

sysfiles.filename AS PhysicalFileName,

CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , ''status'' )) AS Status,

CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , ''Recovery'' )) AS RecoveryMode,

convert(decimal(12,2),round(sysfiles.size/128.000,2)) as FileSizeMB,

convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB,

convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB,

CONVERT ( VARCHAR(10), GETDATE(),111) AS Date

from dbo.sysfiles'

--print '?'

EXEC sp_MSForEachDB @command

I toke the '[]' of the '?' and it works fine. Next step is to make the output into a *.csv file so I can use it in a Pivot table for KPI's

ps. One Orange Chip Points: 29422 More actions · Answer 5

ps.

One Orange Chip

Points: 29422

June 15, 2009 at 11:03 am

#1009794

Great!! you got the trick 🙂

Pradeep Singh