SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.folder_contents_get    Script Date: 6/27/2005 9:57:31 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[folder_contents_get]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[folder_contents_get]
GO


CREATE PROCEDURE folder_contents_get

--Name:  		folder_contents_get
--
--Purpose:		Keep folder information stored, including addition and deletion dates of files.
--					Run as a scheduled job daily.  Keeps folder contents updated for SQL Server jobs.
--
--Notes:		Must use "" around folder names with multiple words.  See Example_1.
--
--Type:			Utility
--
--Format:		EXEC sa_ProcessInformation @folder_name
--
--Example_1:	EXEC folder_contents_get 'C:\"Documents and Settings"\dleggett' SELECT * FROM folder_contents
--
--Author:			Date:		Type:		Description:
--Derrick Leggett	01/13/2003	Created		Created to help server inventory project.
--

@folder_name VARCHAR(256)

AS

DECLARE 
	@rundate DATETIME,
	@dir NVARCHAR(2000)

SELECT @rundate = GETDATE()

--Create temp table to hold contents of dir.
CREATE TABLE #contents(folder_file VARCHAR(256))

--Format dir string properly.
SELECT @dir = 'DIR ' + @folder_name + ' /B /A:-D'

-- Get the contents.  This will error if they didn't pass a valid directory name.
INSERT #contents(folder_file)
	EXEC master..xp_cmdshell @dir

IF @@ERROR <> 0
	BEGIN
		RAISERROR('Invalid directory name.',16,1)
	END

--Insert new records into the folder_contents table.
INSERT folder_contents(
	folder_name, 
	folder_file,
	date_added,
	date_deleted)

	SELECT
		@folder_name,
		c.folder_file,
		@rundate,
		NULL
	FROM 
		#contents c
		LEFT OUTER JOIN (
			SELECT
				folder_name,
				folder_file,
				MAX(ISNULL(date_deleted,'12/31/2100')) date_deleted
			FROM folder_contents 
			GROUP BY
				folder_name,
				folder_file) fc ON c.folder_file = fc.folder_file 
				AND fc.folder_name = @folder_name
	WHERE
		(fc.folder_file IS NULL
			OR fc.date_deleted < '12/31/2100')
		AND c.folder_file IS NOT NULL

--Update the deleted files.
UPDATE fc
SET date_deleted = @rundate
FROM
	folder_contents fc
	LEFT OUTER JOIN #contents c ON fc.folder_file = c.folder_file
		AND fc.folder_name = @folder_name
WHERE
	fc.date_deleted IS NULL
	AND c.folder_file IS NULL

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO