SQL see if file exists

Posted on 2012-09-19
Last Modified: 2012-09-25
Hello EE,

i have a folder where it could contains multiple files...
something like:


How can I know if a file or more exists without giving the extension ?

I know there is exec master.dbo.xp_fileexist [@stringname]
but i need to put extension...

here in my example I would like to see that if I search with the name EE001 well it exists because there is a .doc and a .pdf

any idea ? I tried exec master.dbo.xp_fileexist "EE001.*"  but does not work
Question by:PhilippeRenaud
    LVL 12

    Expert Comment

    exec master.dbo.xp_fileexist 'C:\Documents and Settings\me\Desktop\EE001.doc'

    Should find EE001.doc on your desktop.

    The stored procedure does run under the sql user's account, so you may run into problems if you don't have the right security credentials on the network.

    Here is a post explaining a work-around that you can try if you need to.
    LVL 1

    Author Comment

    Yes but if I dont type at the end the .doc how can I search the file ?

    my question was lets say I dont know the extension is it possible to search with a .* or % for many
    LVL 12

    Expert Comment

    I'm sorry, I completely mis-read your question.

    Just leave off the extension:

    exec master.dbo.xp_fileexist 'C:\Documents and Settings\me\Desktop\EE001'
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    You need to mention typical 8.3 naming conversion

    exec master.dbo.xp_fileexist 'C:\Docume~1\me\Desktop\EE001.doc'

    try something on C:\

    exec master.dbo.xp_fileexist 'C:\EE001.doc'
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    also, xp_fileExist checks for that file on the server where you installed the sql server and wont check on your local machine. for searching files on the network drives, your sql server service account should have sufficeint permission to see the content of that network folder
    LVL 13

    Accepted Solution

    You can use a cmdshell rather than fileexist:
    exec master.dbo.xp_cmdshell 'dir c:\EE001.* /b'

    Open in new window

    LVL 12

    Expert Comment

    Everything I posted was a result of testing in my environment and my machine is a client.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now