EXEC sp_MSForEachDB @command into a csv file

• SirFalcon60

  SSC Eights!

  Points: 838

  More actions

  June 15, 2009 at 1:09 pm

  #131906

  The following statement will show you information about your instance and the file space used. how ever the sp_MSFForEachDB produces 2 rows for each DB withing the instance. Can some one show me how to take the following information and export it to a csv file so it can be put into a pivot tables to produce KPI.

  This information below will work in SQL Server 2005/2008

  DECLARE @command VARCHAR(5000);

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

  Select @@servername AS ServerName,

  (Select db_name() as DBNAME),

  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

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  June 15, 2009 at 1:41 pm

  #1009887

  Hello,

  Possibly write the output to a Table (in your DBA DB) and then extract it at your leisure.

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group