Purge

Hi Everyone.

i have a big database and for each table i have Active field (N/Y) and i wanna delete all record the active N. how i can do that without delete from each tables and looking to details tables before. our database have a complex relations.

please any solution.

thanks alot.

There is no simple way. You will have to scan through the entire database and execute 'delete

' for each desired table. Since you're doing data scrubbing, it might be simpler to use DTS/SSIS to extract desired data (i.e. Y rows) from every table into new table(s). Recreate constraints as needed after that. Else you will have to track down all all referenced/referencing tables and delete accordingly.

This little old script should help finding the ref tables.

Code Snippet

if object_id('usp_findreferences','p') is not null
drop proc usp_findreferences
go

create procedure usp_findreferences
/**************************************************************************-*
*/
/* Purpose: A quick & dirty way to find ref. objects for a[ll] table[s] */
/* Author: OJ Ngo */
/* Date: 02/28/2002 */
/**************************************************************************-*
*/
@.tbname sysname=null
as
set nocount on

Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end

print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go

|||

A some help can be get from "ON DELETE {CASCADE | NO ACTION |SET DEFAULT | SET NULL} " , the option of referential constraints.But for that you have to review the relationshisps of your db. You can be helped by "Database Diagrams" in Management Studio.