Post reply

SQL to compare filepaths stored in a field to to physical filepath?

  • June 30, 2005 at 4:26 am

    #186440

    I have a database that records the physical location of a hi-res file associated with each record in it. I then have some hi-res files whose database record no longer exists. I know I can script a query to compare the two and report the orphaned physical files, but I don't know where to start. Suggestions?

  • June 30, 2005 at 6:20 am

    #569730

    Are all the files in the same directory?

  • June 30, 2005 at 6:52 am

    #569747

    Yes they are.

  • June 30, 2005 at 6:56 am

    #569751

    This is something I did for someone who wanted to find the unused dbs in its mssql/data directory.

    You should be able to adapt this script yourslef :

    --list the database data files found in the directory that are not listed in master :

    Create table #temp

    ( output varchar (2000) null

    )

    GO

    insert into #temp

    exec master.dbo.xp_cmdshell 'DIR "E:\Program Files\Microsoft SQL Server\MSSQL\Data"'

    Select dtDirectory.FileName FROM

    (SELECT SUBSTRING(output, 40, LEN(OUTPUT) - 38) as FileName from #temp where RIGHT(output, 4) = '.MDF') dtDirectory

    LEFT OUTER JOIN

    (Select RIGHT(FileName, charindex('\', Reverse(FileName), 1) - 1) as FileName from master.dbo.SysDatabases) dtMaster

    on dtDirectory.FileName = dtMaster.FileName

    where dtMaster.FileName is null

    DROP TABLE #temp

  • June 30, 2005 at 7:07 am

    #569755

    Thanks, I'll give it a shot.

  • July 1, 2005 at 2:15 am

    #570251

    Hi,

    You could also try using the extended stored procedure xp_FileExists. See http://www.sqlservercentral.com/scripts/contributions/424.asp

    Cheers,

    Sameer

  • July 1, 2005 at 8:21 am

    #570337

    xp_fileexists will not be a set based solution... this could run 10 times slower than the xp_cmdshell version.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply