script to find orphan records on single DB

Question

script to find orphan records on single DB

ricardo.cuevas

Old Hand

Points: 353
More actions
April 3, 2012 at 3:00 pm

#254263

Greetings. Long time lurker first time poster. BTW I love this site. Very professional and educational. I'm looking to find orphan records on a single DB. Not looking to find orphan USERS but data. I have a DB that the pointers are there but the actual files no longer exist.
Thanks in advance
RC

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

ricardo.cuevas (4/3/2012)
Greetings. Long time lurker first time poster. BTW I love this site. Very professional and educational. I'm looking to find orphan records on a single DB. Not looking to find orphan USERS but data. I have a DB that the pointers are there but the actual files no longer exist.
Thanks in advance
RC

might need some more details about the specifics.

usually, a foreign key would prevent the orphans in the first place, but if the FK's were dropped, or the relationship was not defined by a foreign key, you could get the data like that.

in general, for each table that you think has orphans, you simply query it agaisnt the parent:

SELECT *

FROM ChildTable

WHERE ParentID NOT IN

(SELECT ParentID

FROM ParentTable

WHERE ParentID IS NOT NULL)

if you give more specifics, we could help define it better.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Lowell (4/3/2012)
ricardo.cuevas (4/3/2012)
Greetings. Long time lurker first time poster. BTW I love this site. Very professional and educational. I'm looking to find orphan records on a single DB. Not looking to find orphan USERS but data. I have a DB that the pointers are there but the actual files no longer exist.
Thanks in advance
RC
might need some more details about the specifics.
usually, a foreign key would prevent the orphans in the first place, but if the FK's were dropped, or the relationship was not defined by a foreign key, you could get the data like that.
in general, for each table that you think has orphans, you simply query it agaisnt the parent:
SELECT *
FROM ChildTable
WHERE ParentID NOT IN
(SELECT ParentID
FROM ParentTable
WHERE ParentID IS NOT NULL)
if you give more specifics, we could help define it better.

I think the pointers may be to files stored elsewhere, not to data in the database.

Lowell SSC Guru Points: 323518 More actions · Answer 3

Lynn Pettis (4/3/2012)[hr

I think the pointers may be to files stored elsewhere, not to data in the database.

[/quote]

must be a long day; i'm still not reading the requirement right yet.....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ricardo.cuevas Old Hand Points: 353 More actions · Answer 4

Thanks for the speedy reply. My customer is making the request below.

I am looking for a way to get a list of ‘orphan pointers’. These are the pointer in the DB that point to the images but the images are not present (i.e. the physical TIF file is not there although the DB thinks it should be there

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok got it.

the files may have been deleted.

You could do it via xp_cmdShell, or with a nice CLR that Elliot Whitlow has posted out there:

http://nclsqlclrfile.codeplex.com/

then you can use a simple SQL statement to test it or not:

--MFFileExistsCheck

-- Parameters: @FilePath,@FileName

-- purpose: given a path and filename, check if the file exists

-- usage:

--returns 0 or 1 for false/true*/

SELECT dbo.MFFileExistsCheck('C:\Data','contents.txt')

--coming from a table?

SELECT dbo.MFFileExistsCheck(filepath,fielname) as ExistsOrNot,*

FROM YourTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ricardo.cuevas Old Hand Points: 353 More actions · Answer 6

Thank you again. I will give this a shot tomorrow on my TEST system and see if I get the desired results.

Did I mention this site kicks A$$

Thank you again.

Lowell SSC Guru Points: 323518 More actions · Answer 7

another way to do it:

use xp_cmdshell to get a list of all files in a directory, or all files including subdirectorys;

sticking that data into a table, you can join the original to the temp and find items that are not there as well:

create table #Files (

FName varchar(1000))

insert into #Files (FName)

--/b means brief mode..only file name.

exec master..xp_cmdshell 'dir c:\subdir\*.tif /b'

insert into #Files (FName)

--/s means include all sub directories

exec master..xp_cmdshell 'dir c:\masterdir\*.tif /s /b'

select * from #Files

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Another option, if you don't want to fire up CLR, is to use SSIS and a script component.

In a dataflow task, pull the table into the dataflow. Add in a column for your local use via a derived column with a default of 0. Direct that to a transformation script component, you can use a synchronous one. In that code, use the filesystem object to test for existance. If it's there, set your new column to 1, else, set to 0. Send that to a conditional split on your column. Anything with a 0, send to a logging table. Those will be your orphans.

The nice part about that is you could set it to a monthly job or somesuch and have it report when it gets positive rowcounts in the logging table.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

There is also the extended stored procedure xp_fileexist. Want to learn more, google or bing xp_fileexist.