SQL Server 如何写存储过程以获取特定目录下各个文件磁盘大小

CREATE PROCEDURE   [dbo].[GetListOfFileWithSize] 

(

    @Dir    VARCHAR(1000)

)

AS

---------------------------------------------------------------------------------------------

-- Variable decleration

---------------------------------------------------------------------------------------------

    declare @curdir nvarchar(400)

    declare @line varchar(400)

    declare @Command varchar(400)

    declare @counter int



    DECLARE @1MB    DECIMAL

    SET     @1MB = 1024 * 1024



    DECLARE @1KB    DECIMAL

    SET     @1KB = 1024



---------------------------------------------------------------------------------------------

-- Temp tables creation

---------------------------------------------------------------------------------------------

CREATE TABLE #dirs (DIRID int identity(1,1), directory varchar(400))

CREATE TABLE #tempoutput (line varchar(400))

CREATE TABLE output (Directory varchar(400), FilePath VARCHAR(400), SizeInMB DECIMAL(13,2), SizeInKB DECIMAL(13,2))



CREATE TABLE #tempFilePaths (Files VARCHAR(500))

CREATE TABLE #tempFileInformation (FilePath VARCHAR(500), FileSize VARCHAR(100))



---------------------------------------------------------------------------------------------

-- Call xp_cmdshell

---------------------------------------------------------------------------------------------   



     SET @Command = 'dir "'+ @Dir +'" /S/O/B/A:D'

     INSERT INTO #dirs exec xp_cmdshell @Command

     INSERT INTO #dirs SELECT @Dir

     SET @counter = (select count(*) from #dirs)



---------------------------------------------------------------------------------------------

-- Process the return data

---------------------------------------------------------------------------------------------     



        WHILE @Counter <> 0

          BEGIN

            DECLARE @filesize INT

            SET @curdir = (SELECT directory FROM #dirs WHERE DIRID = @counter)

            SET @Command = 'dir "' + @curdir +'"'

            ------------------------------------------------------------------------------------------

                -- Clear the table

                DELETE FROM #tempFilePaths





                INSERT INTO #tempFilePaths

                EXEC MASTER..XP_CMDSHELL @Command



                --delete all directories

                DELETE #tempFilePaths WHERE Files LIKE '%<dir>%'



                --delete all informational messages

                DELETE #tempFilePaths WHERE Files LIKE ' %'



                --delete the null values

                DELETE #tempFilePaths WHERE Files IS NULL



                --get rid of dateinfo

                UPDATE #tempFilePaths SET files =RIGHT(files,(LEN(files)-20))



                --get rid of leading spaces

                UPDATE #tempFilePaths SET files =LTRIM(files)



                --split data into size and filename

                ----------------------------------------------------------

                -- Clear the table

                DELETE FROM #tempFileInformation;



                -- Store the FileName & Size

                INSERT INTO #tempFileInformation

                SELECT 

                        RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FilePath,

                        LEFT(files,PATINDEX('% %',files)) AS FileSize

                FROM    #tempFilePaths



                --------------------------------

                --  Remove the commas

                UPDATE  #tempFileInformation

                SET     FileSize = REPLACE(FileSize, ',','')









                --------------------------------------------------------------

                -- Store the results in the output table

                --------------------------------------------------------------



                INSERT INTO output--(FilePath, SizeInMB, SizeInKB)

                SELECT 

                        @curdir,

                        FilePath,

                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1MB AS DECIMAL(13,2)),

                        CAST(CAST(FileSize AS DECIMAL(13,2))/ @1KB AS DECIMAL(13,2))

                FROM    #tempFileInformation



            --------------------------------------------------------------------------------------------





            Set @counter = @counter -1

           END





    DELETE FROM OUTPUT WHERE Directory is null      

----------------------------------------------

-- DROP temp tables

----------------------------------------------          

DROP TABLE #Tempoutput 

DROP TABLE #dirs 

DROP TABLE #tempFilePaths 

DROP TABLE #tempFileInformation 

--DROP TABLE #tempfinal 



SELECT  * FROM  OutPut

DROP TABLE output