Regarding table Order

Greetings,
I have an application that need to get all the user
defined child tables first before their parents.
I wrote a query, given in this newsgroup only, as below

SELECT o.name
FROM sysobjects o
WHERE o.type='U'
ORDER BY case WHEN exists ( SELECT *
FROM sysforeignkeys f
WHERE o.id = f.fkeyid )
THEN 1
ELSE 0
end, o.name
go

When i try to truncate the first table of the list, it still tells me
that tha table is being referenced by foreign key in another table. My
main job is to truncate all the user defined tables before loading data
into them.
Is there something wrong in the query? Or if someone can tell me a
better approach.

Any help will be appreciated.

TIA

Hi,

You can do one of these:

a)
1. Drop all the FK-s
2. Truncate the tables (in any order)
3. Re-create the FK-s

b)
1. Delete the tables (in a particular order, so FK-s won't be
violated).

Of course, DELETE is slower than TRUNCATE, but if there are many FK-s
and not too much data, I preffer not to drop the FK-s.

Razvan

Like said in the BOL:

"You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY
constraint; instead, use DELETE statement without a WHERE clause."

Therefore the only way is to delete the FK and do the truncate and
recreate the FK, or to use an insert statement instead.

HTH, jens Suessmeyer.

thank for the help. i hope i can get back to u ppl if any more
questions.
thanx again

>> Is there something wrong in the query? Or if someone can tell me a better
 approach. <<

The correct terms are "referenced" and "referencing" tables; "child"
and "parent" are terms from IDMS, IMS and other network DBMS systems.

It is always a dangerous thing to do queries and execture statements on
the schema information tables.  It says that you do not know what you
are doing until run time.

Why not put DRI actions for ON DELETE CASCADE on the referencing tables
and let the system do the work properly so you do not have to do this
kind of manual housecleaning?

Why do you allow users to define tables on the fly in a production
database?  That means you have no data model and users control the
schema, so you do not even know the names of your entities.

TRUNCATE is both proprietrary and dangerous (see what it does with
logging, its limitations, etc.)

The kludge to fix the bad design is to create DELETE FROM statements on
the fly.  The real answer is to get a workable schema and to get rid of
this code.

> Why do you allow users to define tables on the fly in a  production
> database?  That means you have no data model and users control the
> schema, so you do not even know the names of your entities.

Think Knowledge Management, where you define your knowledge model on the fly
and build the table structure to support it; better that then creating
1,000's of tables containing generic stuff.

> TRUNCATE is both proprietrary and dangerous (see what it does with
> logging, its limitations, etc.)

What does it do with logging? Why is it dangerous?

What rubbish.

TRUNCATE TABLE is logged, SQL Server logs extent deallocations so is fully
recoverable if in the middle of the truncate a problem occurs.

Yes there are limitations, specifically and for good reason, not being able
to use it when you have foriegn key constraints - you should know that.

Say you have a 10GB table that you want to clear down and empty - would you
really issue a DELETE so that ALL the rows and index pages are logged
causing a 10GB+ log file, it would also take an exceedingly long time to
run. Its one of the first things you learn as a DBA, TRUNCATE is better than
DELETE when clearing down a table.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1134739926.655709.309060@z14g2000cwz.googlegroups.com... 
>
> The correct terms are "referenced" and "referencing" tables; "child"
> and "parent" are terms from IDMS, IMS and other network DBMS systems.
>
> It is always a dangerous thing to do queries and execture statements on
> the schema information tables.  It says that you do not know what you
> are doing until run time.
>
> Why not put DRI actions for ON DELETE CASCADE on the referencing tables
> and let the system do the work properly so you do not have to do this
> kind of manual housecleaning?
>
> Why do you allow users to define tables on the fly in a production
> database?  That means you have no data model and users control the
> schema, so you do not even know the names of your entities.
>
> TRUNCATE is both proprietrary and dangerous (see what it does with
> logging, its limitations, etc.)
>
> The kludge to fix the bad design is to create DELETE FROM statements on
> the fly.  The real answer is to get a workable schema and to get rid of
> this code.
>