-- Stored Procedure: dbo.SpaceUsedByFileGroupInsert
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SpaceUsedByFileGroupInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SpaceUsedByFileGroupInsert]
GO



CREATE PROCEDURE [dbo].[SpaceUsedByFileGroupInsert] 
 @Server varchar(128)
 , @Database varchar(128)
 , @FileGroup varchar(128)
 , @FileName varchar(128)
 , @File int
 , @Path varchar(128)
 , @IsPrimaryFile bit
 , @Size int
 , @SizeInKB float
 , @SpaceAvailableInMB int
 , @FileGrowth int
 , @FileGrowthType int
 , @MaximumSize int
            
AS
/*******************************************************************************************************
* admin.dbo.SpaceUsedByFileGroupInsert 
* Creator: Bill WUnder      
* Date:    5-22-2002      
*
* Description:   Add a row to the SpaceUsedHistory table
* Notes:         DMO DBFILE properties
*
* Usage:
      EXECUTE admin.dbo.SpaceUsedByFileGroupInsert 
  , admin
  , 1
  , 'test'
  , 'c:\temp\test.txt'
  , 1
  , 1
  , 1
  , 1
  , 1
  , 1
  , '%'
  , 1
*
*
* Modifications:   
*          Developer Name     Date     Brief Description
*          ------------------ -------- ------------------------------------------------------------
*                    
********************************************************************************************************/

---------------------------------------------
-- declare variables
---------------------------------------------

---------------------------------------------
-- create temp tables
---------------------------------------------

---------------------------------------------
-- set session variables
---------------------------------------------
SET NOCOUNT ON


---------------------------------------------
-- body of stored procedure
---------------------------------------------

if object_id('dbo.SpaceUsedHistory','U') is null
 create table dbo.SpaceUsedHistory
  ([Server] varchar(128)
  , [Database] varchar(128)
  , [FileGroup] varchar(128)
  , [FileName] varchar(128)
  , [File] int
  , [Path] varchar(128)
  , [IsPrimaryFile] bit
  , [Size] int
  , [SizeInKB] float
  , [SpaceAvailableInMB] int
  , [FileGrowth] int
  , [FileGrowthInKB] float
  , [FileGrowthType] int
  , [MaximumSize] int
  , [RecCreatedDt] datetime
  constraint dft_SpaceUsedHistory__RecCreatedDt default getdate()
  , [RecCreatedUser] varchar(128)
  constraint dft_SpaceUsedHistory__RecCreatedUser default suser_sname() 
  , constraint pk_SpaceUsedHistory__RecCreatedDt__Server__Database__FileGroup__FileName
  primary key ([RecCreatedDt], [Server], [Database], [FileGroup], [FileName]))

insert dbo.SpaceUsedHistory ([Server]
  , [Database]
  , [FileGroup]
  , [FileName]
  , [File]
  , [Path]
  , [IsPrimaryFile]
  , [Size]
  , [SizeInKB]
  , [SpaceAvailableInMB]
  , [FileGrowth]
  , [FileGrowthInKB]
  , [FileGrowthType]
  , [MaximumSize])
values
 (@Server
 , @Database
 , @FileGroup
 , @FileName
 , @File
 , @Path
 , @IsPrimaryFile
 , @Size
 , @SizeInKB
 , @SpaceAvailableInMB
 , case when @FileGrowthType = 0 then null else @FileGrowth end
 , case when @FileGrowthType = 0 then @FileGrowth else null end
 , @FileGrowthType 
 , @MaximumSize)




RETURN


GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO