orphan recods

[NickName]

Hi,

Say, a database has all the FK constraints created properly, would sql
server 2000 save this kind of meta data into sysforeignkeys table? Or
it depends on ...?

I'm trying to find all the orphen records in a given database via meta
data.

TIA.

[Simon Hayes]

Re: orphan recods


"NickName" <dadada@rock.co m> wrote in message
news:1109186512 .635327.144720@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> Hi,
>
> Say, a database has all the FK constraints created properly, would sql
> server 2000 save this kind of meta data into sysforeignkeys table? Or
> it depends on ...?
>
> I'm trying to find all the orphen records in a given database via meta
> data.
>
> TIA.
>[/color]

sysforeignkeys contains the table and column IDs for all foreign keys (see
sysreferences too), but it doesn't contain any user data, only metadata. I'm
not really sure what you want to do, but I guess you may want to build your
queries dynamically? If so, you can use the OBJECT_NAME() and COL_NAME()
functions to get the table and column names:

select
object_name(fke yid) as 'ReferencingTab le',
col_name(fkeyid , fkey) as 'ReferencingCol umn',
object_name(rke yid) as 'ReferencedTabl e',
col_name(rkeyid , rkey)'Reference dColumn'
from
sysforeignkeys

Simon

[--CELKO--]

Re: orphan recods

>> I'm trying to find all the orphen records[sic] in a given database
via meta data. <<

Wouldn't be more informative to just use a query with an NOT EXISTS()
predicate? And then add the DRI actions that were missing the first
time.

I assumethat you do not plan on messing up the schema so often you need
a general tool for cleaning up.

[NickName]

Re: orphan recods

Simon,

That is very similar to what I was doing except that I did not know
about OBJECT_NAME() and COL_NAME() functions.

Thanks.

Don

[NickName]

Re: orphan recods

Joe,

While I appreciate you acclaimed knowledge of sql, I have to say you
seem to have this wicked sense/urge of driling a needle into other's
palm :)

No, no messing around of schema, and again, when one inherits a huge
database he/she never really has sufficient knowledge of it, hence, the
need for a general tool to tackle it.

Don

[Erland Sommarskog]

Re: orphan recods

NickName (dadada@rock.co m) writes:[color=blue]
> Say, a database has all the FK constraints created properly, would sql
> server 2000 save this kind of meta data into sysforeignkeys table? Or
> it depends on ...?
>
> I'm trying to find all the orphen records in a given database via meta
> data.[/color]

Hm, if foreign keys are set up properly, there should not really be
orphans to be found through meta-data. ...if it wasn't for the fact
that you can disable a foreign key, and when you re-enable it the
default is not check its validity. Whether you have any such foreign
keys, you can find out by adding this column to Simon's query:

objectproperty( constid, 'CnstIsNotTrust ed')

DBCC CHECKCONSTRAINT S is the easiest way to find out if you have any
violations. However, even if there are not, this will not make constraints
trusted.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[NickName]

Re: orphan recods

Erland,

I ran DBCC CHECKCONSTRAINT S
with all constraints option
against several databases, at least one of them I know that has orphen
records (for I created one for testing), and yet, DBCC ... returned
"(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"
across board, how come?

Thanks.

Don

[Erland Sommarskog]

Re: orphan recods

NickName (dadada@rock.co m) writes:[color=blue]
> I ran DBCC CHECKCONSTRAINT S
> with all constraints option
> against several databases, at least one of them I know that has orphen
> records (for I created one for testing), and yet, DBCC ... returned
> "(1 row(s) affected)
>
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> "
> across board, how come?[/color]

I don't know exactly how you define orphan rows. Then again, if you were
able to create a row that violated the foreign-key constraint, then the
constraint is obviously not enabled, and Books Online says that DBCC
CHECKCONSTRATIN S only checks enabled constraints. The repro below demon-
straints.

CREATE TABLE main1 (a1 int NOT NULL,
CONSTRAINT pk_main1 PRIMARY KEY(a1))
go
CREATE TABLE main2 (a2 int NOT NULL,
CONSTRAINT pk_main2 PRIMARY KEY(a2))
go
CREATE TABLE child (a1 int NOT NULL,
a2 int NOT NULL,
CONSTRAINT pk_child PRIMARY KEY (a2, a1),
CONSTRAINT fk_child1 FOREIGN KEY(a1)
REFERENCES main1 (a1),
CONSTRAINT fk_child2 FOREIGN KEY(a2)
REFERENCES main2 (a2))
go
INSERT main1 (a1) VALUES (1)
INSERT main2 (a2) VALUES (2)
INSERT child (a1, a2) VALUES (1, 2)
go
ALTER TABLE child NOCHECK CONSTRAINT fk_child1
ALTER TABLE child NOCHECK CONSTRAINT fk_child2
go
INSERT child (a1, a2) VALUES (11, 2)
INSERT child (a1, a2) VALUES (1, 12)
go
ALTER TABLE child WITH NOCHECK CHECK CONSTRAINT fk_child1
go
DBCC CHECKCONSTRAINT S ('child')
go
DROP TABLE child, main1, main2

You can use the objectproperty( ) function to find disabled constraint. The
repro also shows hows to enable the constraints, but better is to say
WITH CHECK CHECK. The you will check the constraint when you enabled it,
and you will get an error if there are orphans. No report of the orphans,
though. The good thing with WITH CHECK is that the constraint will be
trusted by the optimizer and can thus be used in query plans.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp