VerifyFiles.sql

-----------------------------------------------------------------------------------
-- create work table
-- cannot be a table variable due to the inner looping
CREATE TABLE #files
  ( PATH VARCHAR(256) )

  
USE [dbamaint]
GO

/****** Object:  Table [dbo].[DirPaths]    Script Date: 01/19/2009 15:51:30 ******/
SET ansi_nulls ON
GO

SET quoted_identifier ON
GO

SET ansi_padding ON
GO

CREATE TABLE [dbo].[DirPaths]
  (
     [PathID] [INT] IDENTITY(1, 1) NOT NULL,
     [Path]   [VARCHAR](256) NOT NULL,
     CONSTRAINT [PK_DirPaths] PRIMARY KEY CLUSTERED ( [PathID] ASC )
  )
ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Files]    Script Date: 01/19/2009 15:51:51 ******/
CREATE TABLE [dbo].[Files]
  (
     [PathID]   [INT] NOT NULL,
     [FilePath] [VARCHAR](256) NOT NULL
  )
ON [PRIMARY]
GO

SET ansi_padding OFF
GO

ALTER TABLE [dbo].[Files] WITH CHECK ADD CONSTRAINT [FK_Files_DirPaths] FOREIGN
KEY([PathID]) REFERENCES [dbo].[DirPaths] ([PathID])
GO

ALTER TABLE [dbo].[Files] CHECK CONSTRAINT [FK_Files_DirPaths]

-----------------------------------------------------------------------------------
DECLARE @DirRoots VARCHAR(64)
DECLARE @DirRoot VARCHAR(32)
DECLARE @Drive VARCHAR(1)
DECLARE @SubDirs VARCHAR(64)
DECLARE @SubDir VARCHAR(32)
DECLARE @DirPath VARCHAR(128)
DECLARE @File VARCHAR(128)
DECLARE @PathID INT
DECLARE @result INT

DECLARE @drives TABLE
  ( drive CHAR(1),
    free  VARCHAR(16)
  )

DECLARE @DBFiles TABLE 
	( ServerName varchar(24)
	, DatabaseName varchar(32)
	, LogicalName varchar(32)
	, FileName varchar(128)
	, LastUpdate datetime
	);

-- populate table with existing databases information
INSERT INTO @DBFiles
EXEC sp_MSForEachDB 
	'SELECT CONVERT(nvarchar(32), SERVERPROPERTY(''Servername'')) AS Server,
		''?'' as DatabaseName,
		[?]..sysfiles.name AS LogicalName, 
		[?]..sysfiles.filename AS FileName,
		GETDATE()
			From [?]..sysfiles'
  
SELECT @DirRoots = ':\mssql' + ',' + ':\mssql.1\mssql' + ',' + ':\MSSQL10.MSSQLSERVER\MSSQL'
SELECT @SubDirs = '\BAK\' + ',' + '\Data\' + ',' + '\LDF\' + ',' + '\'

INSERT INTO @drives
            (drive,
             free)
EXEC MASTER..Xp_fixeddrives

DECLARE drive CURSOR FOR
  SELECT drive
  FROM   @drives
WHERE Drive != 'C'

-- outer loop for each drive letter
OPEN drive

FETCH NEXT FROM drive INTO @Drive

WHILE @@FETCH_STATUS = 0
  BEGIN
      -- work goes here
      DECLARE dirroot CURSOR FOR
        SELECT *
        FROM   [dbamaint].[dbo].[Udf_split](@DirRoots, ',')

      -- 2nd loop for each directory root
      OPEN dirroot

      FETCH NEXT FROM dirroot INTO @DirRoot

      WHILE @@FETCH_STATUS = 0
        BEGIN
            -- work goes here
            DECLARE subdir CURSOR FOR
              SELECT *
              FROM   [dbamaint].[dbo].[Udf_split](@SubDirs, ',')

            -- 3rd loop for each each sub-directory defined
            OPEN subdir

            FETCH NEXT FROM subdir INTO @SubDir

            WHILE @@FETCH_STATUS = 0
              BEGIN
                  -- work goes here
                  PRINT '3rd loop ' + @Drive + @DirRoot + @SubDir
                  SELECT @DirPath = 'dir /B ' + @Drive + @DirRoot + @SubDir
                  --PRINT 'Dirpath exec: ' + @DirPath
                  EXEC @result = MASTER.dbo.Xp_cmdshell @DirPath, NO_OUTPUT

                  IF ( @result = 0 )
                    BEGIN
                        -- re-assign @DirPath
                        SELECT @DirPath = @Drive + @DirRoot + @SubDir

                        INSERT INTO dbamaint.dbo.dirpaths
                                    (PATH)
						VALUES      ( @DirPath )

                        -- directory exists, get the contents
                        SET @PathID = (SELECT pathid
                                       FROM   dbamaint.dbo.dirpaths
                                       WHERE  PATH = @DirPath)

                        PRINT 'Starting file loop for: ' + @DirPath
                        EXEC dbamaint.dbo.Dbm_listfiles @DirPath, '#Files', NULL, NULL, 0

                        -- files loop
                        DECLARE files CURSOR FOR
                          SELECT *
                          FROM   #Files

                        OPEN files

                        FETCH NEXT FROM files INTO @File

                        WHILE @@FETCH_STATUS = 0
                          BEGIN
                              IF @File != ''
                                INSERT INTO dbamaint.dbo.files
                                            (pathid,
                                             filepath)
                                VALUES      (@PathID,
                                             @DirPath + @File)
                              FETCH NEXT FROM files INTO @File
                          END

                        CLOSE files
                        DEALLOCATE files
                        
                        TRUNCATE TABLE #Files
                    END
                  ELSE
                    BEGIN
                        PRINT 'Directory path ' + @DirPath + ' does not exist.' + CHAR(10)
                    END

                  FETCH NEXT FROM subdir INTO @SubDir
              END -- 3rd loop close

            CLOSE subdir
            DEALLOCATE subdir

            FETCH NEXT FROM dirroot INTO @DirRoot
        END -- 2nd loop close
      CLOSE dirroot
      DEALLOCATE dirroot

      FETCH NEXT FROM drive INTO @Drive
  END -- outer loop close
CLOSE drive
DEALLOCATE drive

  
SELECT * FROM   dirpaths
SELECT * FROM   files

--SELECT FilePath
--	FROM DirPaths dp
--	JOIN files f ON dp.PathID = f.PathID
--WHERE FilePath NOT LIKE '%cer' -- ignore certificates
--ORDER BY 1


SELECT @@SERVERNAME AS Servername, DatabaseName, FilePath
	FROM @DBFiles dbf
	FULL JOIN files f ON dbf.FileName = f.FilePath
WHERE FilePath NOT LIKE '%cer' -- ignore certificates
--AND FilePath NOT LIKE '%.bak' -- ignore backup files
ORDER BY 1,2,3



-- cleanup
-----------------------------------------------------------------------------------
DROP TABLE #files
DROP TABLE files
DROP TABLE dirpaths