Professional Web Applications Themes

find file size,last modified date thru SQL - Microsoft SQL / MS SQL Server

Can i find out the filename,filesize and last modified date using TSQL for all files under a given directory ? Thanks...

Sponsored Links
  1. #1

    Default find file size,last modified date thru SQL

    Can i find out the filename,filesize and last modified date using TSQL for
    all files under a given directory ?

    Thanks


    Sponsored Links
    FR Guest

  2. #2

    Default Re: find file size,last modified date thru SQL

    Well without going through the trouble of creating a File System Object you
    can do this:

    [url]http://www.mssqlserver.com/faq/general-dirlist.asp[/url]

    --

    Andrew J. Kelly
    SQL Server MVP


    "FR" <floydrevhotmail.com> wrote in message
    news:eRNXWM3PDHA.2636TK2MSFTNGP10.phx.gbl...
    > Can i find out the filename,filesize and last modified date using TSQL for
    > all files under a given directory ?
    >
    > Thanks
    >
    >

    Andrew J. Kelly Guest

  3. #3

    Default Re: find file size,last modified date thru SQL

    What about filesize and last modified date ?


    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:OqZ$bm3PDHA.1720TK2MSFTNGP11.phx.gbl...
    > Well without going through the trouble of creating a File System Object
    you
    > can do this:
    >
    > [url]http://www.mssqlserver.com/faq/general-dirlist.asp[/url]
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "FR" <floydrevhotmail.com> wrote in message
    > news:eRNXWM3PDHA.2636TK2MSFTNGP10.phx.gbl...
    > > Can i find out the filename,filesize and last modified date using TSQL
    for
    > > all files under a given directory ?
    > >
    > > Thanks
    > >
    > >
    >
    >

    FR Guest

  4. #4

    Default Re: find file size,last modified date thru SQL

    You can also take a look at

    [url]http://tinyurl.com/fp6d[/url]

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    SCT Software Solutions

    "FR" <floydrevhotmail.com> wrote in message news:eTDrNp3PDHA.2248TK2MSFTNGP11.phx.gbl...
    > What about filesize and last modified date ?
    >
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:OqZ$bm3PDHA.1720TK2MSFTNGP11.phx.gbl...
    > > Well without going through the trouble of creating a File System Object
    > you
    > > can do this:
    > >
    > > [url]http://www.mssqlserver.com/faq/general-dirlist.asp[/url]
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "FR" <floydrevhotmail.com> wrote in message
    > > news:eRNXWM3PDHA.2636TK2MSFTNGP10.phx.gbl...
    > > > Can i find out the filename,filesize and last modified date using TSQL
    > for
    > > > all files under a given directory ?
    > > >
    > > > Thanks
    > > >
    > > >
    > >
    > >
    >
    >
    Vinodk Guest

  5. #5

    Default Re: find file size,last modified date thru SQL

    I came across that article .It would be nice if I could figure out how to
    get rid of the verbose using
    exec master..xp_cmdshell 'DIR /A-D c:\WinNt'

    It would be nice to just get the datetime, the size and the name of the file
    into a table with those 3 columns. Can you let me know how ?

    Thanks

    "Vinodk" <vinodk_scthotmail.com> wrote in message
    news:eb#4BI4PDHA.3700tk2msftngp13.phx.gbl...
    You can also take a look at

    [url]http://tinyurl.com/fp6d[/url]

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    SCT Software Solutions

    "FR" <floydrevhotmail.com> wrote in message
    news:eTDrNp3PDHA.2248TK2MSFTNGP11.phx.gbl...
    > What about filesize and last modified date ?
    >
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:OqZ$bm3PDHA.1720TK2MSFTNGP11.phx.gbl...
    > > Well without going through the trouble of creating a File System Object
    > you
    > > can do this:
    > >
    > > [url]http://www.mssqlserver.com/faq/general-dirlist.asp[/url]
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "FR" <floydrevhotmail.com> wrote in message
    > > news:eRNXWM3PDHA.2636TK2MSFTNGP10.phx.gbl...
    > > > Can i find out the filename,filesize and last modified date using TSQL
    > for
    > > > all files under a given directory ?
    > > >
    > > > Thanks
    > > >
    > > >
    > >
    > >
    >
    >


    FR Guest

  6. #6

    Default Re: find file size,last modified date thru SQL

    Floyd,

    Here is a simple example of how to p the parts your looking for:

    CREATE PROCEDURE list_file_info

    AS

    SET NOCOUNT ON

    DECLARE SQL VARCHAR(500), FName VARCHAR(40), Error INT
    DECLARE Msg VARCHAR(100), Return INT
    DECLARE FileDate VARCHAR(20), FileSize VARCHAR(10)


    SET DATEFORMAT MDY

    IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
    DROP TABLE #DirList

    CREATE TABLE #dirlist (FName VARCHAR(1000))

    CREATE TABLE #Errors (Results VARCHAR(1000))

    -- Insert the results of the dir cmd into a table so we can scan it
    INSERT INTO #dirlist (FName)
    exec master..xp_cmdshell 'dir /OD C:\*.*'

    SET Error = ERROR
    IF Error <> 0
    BEGIN
    SET Msg = 'Error while getting the filenames with DIR '
    GOTO On_Error
    END

    -- Remove the garbage
    DELETE #dirlist WHERE
    SUBSTRING(FName,1,2) < '00' OR
    SUBSTRING(FName,1,2) > '99' OR
    FName IS NULL

    --SELECT * FROM #DirList

    -- Create a cursor and for each file name do the processing.
    -- The files will be processed in date order.
    DECLARE curDir CURSOR READ_ONLY LOCAL
    FOR
    SELECT SUBSTRING(FName,40,40) AS FName, SUBSTRING(FName,1,20) AS
    FileDate, SUBSTRING(FName,30,10) AS FileSize
    FROM #dirlist
    WHERE FName NOT LIKE '%<DIR>%'

    OPEN curDir

    FETCH NEXT FROM curDir INTO Fname,FileDate,FileSize
    WHILE (fetch_status = 0)
    BEGIN


    Print FName + ' ' + FileDate + ' ' + FileSize


    FETCH NEXT FROM curDir INTO Fname,FileDate,FileSize
    END

    CLOSE curDir
    DEALLOCATE curDir

    DROP TABLE #DirList
    DROP TABLE #Errors

    RETURN Error

    On_Error:
    BEGIN
    IF Error <> 0
    BEGIN
    SELECT Msg + '. Error # ' + CAST(Error AS VARCHAR(10))
    RAISERROR(Msg,12,1)
    RETURN Error
    END
    END
    GO


    --

    Andrew J. Kelly
    SQL Server MVP


    "FR" <floydrevhotmail.com> wrote in message
    news:OQXygl5PDHA.2244TK2MSFTNGP11.phx.gbl...
    > I came across that article .It would be nice if I could figure out how to
    > get rid of the verbose using
    > exec master..xp_cmdshell 'DIR /A-D c:\WinNt'
    >
    > It would be nice to just get the datetime, the size and the name of the
    file
    > into a table with those 3 columns. Can you let me know how ?
    >
    > Thanks
    >
    > "Vinodk" <vinodk_scthotmail.com> wrote in message
    > news:eb#4BI4PDHA.3700tk2msftngp13.phx.gbl...
    > You can also take a look at
    >
    > [url]http://tinyurl.com/fp6d[/url]
    >
    > --
    > HTH,
    > Vinod Kumar
    > MCSE, DBA, MCAD
    > SCT Software Solutions
    >
    > "FR" <floydrevhotmail.com> wrote in message
    > news:eTDrNp3PDHA.2248TK2MSFTNGP11.phx.gbl...
    > > What about filesize and last modified date ?
    > >
    > >
    > > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > > news:OqZ$bm3PDHA.1720TK2MSFTNGP11.phx.gbl...
    > > > Well without going through the trouble of creating a File System
    Object
    > > you
    > > > can do this:
    > > >
    > > > [url]http://www.mssqlserver.com/faq/general-dirlist.asp[/url]
    > > >
    > > > --
    > > >
    > > > Andrew J. Kelly
    > > > SQL Server MVP
    > > >
    > > >
    > > > "FR" <floydrevhotmail.com> wrote in message
    > > > news:eRNXWM3PDHA.2636TK2MSFTNGP10.phx.gbl...
    > > > > Can i find out the filename,filesize and last modified date using
    TSQL
    > > for
    > > > > all files under a given directory ?
    > > > >
    > > > > Thanks
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >
    >

    Andrew J. Kelly Guest

Similar Threads

  1. date record was last modified. Help!!!!
    By Joe Science in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: March 23rd, 07:52 PM
  2. RDS and Date Modified any better in MX 7?
    By momnotmom in forum Coldfusion Server Administration
    Replies: 0
    Last Post: March 10th, 11:02 PM
  3. Increased file size with date in footer
    By Asim_Rashid@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: June 17th, 02:20 PM
  4. change modified date?
    By Shaunn Johnson in forum PERL Beginners
    Replies: 1
    Last Post: January 29th, 01:18 AM
  5. How do you find out the size of a file
    By Chinku Simon in forum PERL Beginners
    Replies: 4
    Last Post: October 21st, 12:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139