/*============================================================================
  File:     4a_Baselines_FileData_Setup.sql

  Summary:  This script contains the statements to
			create a table to hold file data, and the
			statement to use in the scheduled job.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Erin Stellato, SQLskills.com

  (c) 2015, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [BaselineData];
GO

/*
	Create table
*/

IF NOT EXISTS ( SELECT  1
            FROM    [sys].[tables]
            WHERE   [name] = N'SQLskills_FileInfo' ) 

CREATE TABLE [dbo].[SQLskills_FileInfo]
    (
      [DatabaseName] SYSNAME NOT NULL ,
      [FileID] INT NOT NULL ,
      [Type] TINYINT NOT NULL ,
      [DriveLetter] NVARCHAR(1) NULL ,
      [LogicalFileName] SYSNAME NOT NULL ,
      [PhysicalFileName] NVARCHAR(260) NOT NULL ,
      [SizeMB] DECIMAL(38, 2) NULL ,
      [SpaceUsedMB] DECIMAL(38, 2) NULL ,
      [FreeSpaceMB] DECIMAL(38, 2) NULL ,
      [MaxSize] DECIMAL(38, 2) NULL ,
      [IsPercentGrowth] BIT NULL ,
      [Growth] DECIMAL(38, 2) NULL ,
      [CaptureDate] DATETIME NOT NULL
    )
ON  [PRIMARY];
GO

CREATE CLUSTERED INDEX [CI_SQLskills_FileInfo] ON [dbo].[SQLskills_FileInfo] ([CaptureDate]);


/*
	script to put in scheduled job
*/
USE [BaselineData];
GO
SET NOCOUNT ON;

DECLARE @sqlstring NVARCHAR(MAX);
DECLARE @DBName NVARCHAR(257);

DECLARE DBCursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
    SELECT  QUOTENAME([name])
    FROM    [sys].[databases]
    WHERE   [state] = 0
    ORDER BY [name];

BEGIN
    OPEN DBCursor;
    FETCH NEXT FROM DBCursor INTO @DBName;
    WHILE @@FETCH_STATUS <> -1 
        BEGIN
            SET @sqlstring = N'USE ' + @DBName + '
      ; INSERT [BaselineData].[dbo].[SQLskills_FileInfo] (
      [DatabaseName],
      [FileID],
      [Type],
      [DriveLetter],
      [LogicalFileName],
      [PhysicalFileName],
      [SizeMB],
      [SpaceUsedMB],
      [FreeSpaceMB],
      [MaxSize],
      [IsPercentGrowth],
      [Growth],
      [CaptureDate]
      )
      SELECT ''' + @DBName
                + ''' 
      ,[file_id],
       [type],
      substring([physical_name],1,1),
      [name],
      [physical_name],
      CAST([size] as DECIMAL(38,0))/128., 
      CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128., 
      (CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.),
      [max_size],
      [is_percent_growth],
      [growth],
      GETDATE()
      FROM ' + @DBName + '.[sys].[database_files];'
            EXEC (@sqlstring)
            FETCH NEXT FROM DBCursor INTO @DBName;
        END

    CLOSE DBCursor;
    DEALLOCATE DBCursor;
END

