Help: Need sp to empty a table that is FK to others

Help: Need sp to empty a table that is FK to others

Post by Jon Sack » Fri, 25 Apr 1997 04:00:00



The SPs below won't do the delete, but they're 90% of the way there.
They will generate scripts which you can cut and paste to drop and recreate
the foreign keys.  It would be an easy modification to have them execute
the scripts rather than return them and also do the truncate table.  Note:
these are cut and paste jobs from system SPs on 6.0.  I don't know if they
need to be updated for 6.5.

For example, executing:

sp_ScriptDropFK Customer

gives this output:

Scripts                                                                    

----------------------------------------------------------------------------
----------------------------------------------
ALTER TABLE Payment DROP CONSTRAINT fkPayment_Customer                    

ALTER TABLE RegAdmin DROP CONSTRAINT fkRegAdminCustomer                    

*******************************************                                

ALTER TABLE Payment ADD CONSTRAINT fkPayment_Customer
        FOREIGN KEY (NCN)
        REFERENCES Customer (NCN)                                                

ALTER TABLE RegAdmin ADD CONSTRAINT fkRegAdminCustomer
        FOREIGN KEY (NCN)
        REFERENCES Customer (NCN)                                                



Quote:> Anyone know of (or written) a stored procedure to clear a table that has
> references by other tables?

begin 600 Scriptdr.sql
<uuencoded_portion_removed>

`
end

begin 600 Scriptfk.sql
<uuencoded_portion_removed>
+="<I#0I'3PT*#0I4
`
end

 
 
 

Help: Need sp to empty a table that is FK to others

Post by Mike Wilke » Sat, 26 Apr 1997 04:00:00


Jon,

I've included the procedure I built as a way of saying thanks for your
response. It runs in TEST mode if you supply a 1 as the second argument...
        sp_clear_table committee, 1

Thanks for yours,

Mike
begin 600 cleartbl.txt
<uuencoded_portion_removed>
`
end

 
 
 

1. table export in OPAL creates an empty file - help needed

I have a weird problem with Paradox for Windows 4.5:

When I ExportAsciiVar a table in Object PAL, the resulting file's size is 0
bytes. Manually exporting the same table works fine, as long as my form has
only been in the design mode in the current Paradox session. The table is not
open in the OPAL code and it is not used on the form's data model either.
The same ExportAsciiVar works fine on another computer (using the same user
name = same file access rights).

So I need suggestions on which Paradox or DOS configuration options could
affect exporting. I'm working on a network, could the multi-user settings
cause this behaviour ? Or SHARE ? I can easily duplicate the functionality
of ExportAsciiVar in OPAL, but I'd hate to re-invent the wheel.

Thanks in advance,
Teemu M?tt?nen.

PS. An additional twist: sometimes the resulting file also has the read-only
    file attribute set :-)
--
Teemu M?tt?nen  Department of Computer Science, University of Helsinki, Finland

2. No Performance Montior Objects for SQL Server

3. Add records to an empty table - AddNewX.cpp example fails with an empty table

4. Getting/Setting the default DATETIME format in DB2

5. Create Empty Table to Return from SP?

6. Refill Data Combo

7. Need SP help - Return rows 126-150 from sp

8. US-MO-PROGRAMMER ANALYST / ORACLE

9. SP and ADO with temporary table (help needed)

10. I am custom Traverse97 Programmer, seeking others.

11. Need advice on replacing tables with fk

12. FK Refernce help needed pls.

13. Newbie: Help on Referential Integrity via FK when creating a table