June 15, 2009 at 9:11 am
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
June 15, 2009 at 9:14 am
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.
June 15, 2009 at 9:17 am
Above post is not correct.. Apologies..
still working on it..
June 15, 2009 at 9:36 am
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 🙁
June 15, 2009 at 10:54 am
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
June 15, 2009 at 11:03 am
Great!! you got the trick 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply