Deleting rows
-
Hi,
I am trying to delete all the rows from the table which have many foriegn key constraints and I need to write the script to delete the rows from 50 tables with each table having many constraints.I am using DELETE.But I need to drop the constrints before deleting the table.Is their any syntax by which I can delete the rows in table without droping the constraint.
I appreciate any help.
Thanks,
Sree
Thanks,
SR -
Why can't you just delete the tables in the right order?
Would be simpler and easy to reuse if needed.
-
Hi,
Remi thanks for responding.
According to scenario I can't delete all the tables.
Thanks,
Sree
Thanks,
SR -
Hi,
write you how I do it usually, because I often need to delete the data in some tables.
Open isqlw and type 'delete from <tablename>'. Then mark the tablename and press if the shortcut exists ALT + F1 to apply sp_help on that table. Alternatively use sp_help '<tablename>' to see all foreign keys and the referenced tables in Query Analyzer.
Then mark and copy each tablename shown in the report window and paste it before the first 'delete from <tablename>' and add 'delete from' in front of the tablename. This way you step by step produce a list of 'delete from' (or 'truncate table') statements with the right dependency sequence. Note, that dependend tables can also have dependencies, so that you have to repeat sp_help on the referenced tables as well.
Output example:
DELETE FROM table2
DELETE FROM table3
DELETE FROM table1....
It takes you some minutes, and it's manual work, but it works very fast, especially when you need to do that with several databases. You will get some routine on that work...
-
H!!
The query listed below'll be able to delete all foreign key constraints in your database .After deleting the foreign keys you can take out the script of your databse from previous day's back up and recreate all foreign key's which you had deleted
/******************************************************************
set nocount on
-- declare variables
declare @FKeyCount as smallint
declare @Counter as smallint
declare @FKeyName as varchar(256)
declare @TableName as varchar(768)
declare @fk_drop table
(DBName varchar(256),
TblName varchar(256),
FKName varchar(256),
Num smallint identity(1,1),
primary key(DBName,TblName,FKName))
-- identify user table foreign keys to drop
insert into
@fk_drop
select
f.constraint_catalog,
f.table_name,
f.constraint_name
from
information_schema.table_constraints f
where
f.constraint_type = 'foreign key' and
objectproperty(object_id(f.table_name), N'IsUserTable') = 1
-- set while loop process variables
set @FKeyCount = (select count(1) from @fk_drop)
set @Counter = 1
-- drop foreign keys
while @Counter <= @FKeyCount
begin
set @FKeyName = (select FKName from @fk_drop where Num = @Counter)
set @TableName = (select DBName + '.dbo.' + TblName from @fk_drop where Num = @Counter)
exec ('alter table ' + @TableName + ' drop constraint ' + @FKeyName )
set @Counter = @Counter + 1
continue
end
set nocount off
regards,
Vinod S.R(DBA)
HTC Global services (India)Pvt Ltd., Chennai(09840856202)
-
I meant, just generate the 50 deletes statement and execute them in the right order.
-
Check out ApexSQL Script. It should be able to do just what you need. There's a fully functional trial at http://www.apexsql.com/sql_tools_script.htm . Give it a try, it might save you a few hours 🙂
-
Try the following: ALTER TABLE yourtable NOCHECK CONSTRAINT ALL. This should disable all constraints on a given table and allow you to delete. Much easier than having to list the constraints. After you delete from the table you can set the table back with ALTER TABLE yourtable CHECK CONSTRAINT ALL.
-
deleting table perhaps if this is only contains few records is ok but if this contains millions of record i dont think is easy so try TRUNCATE TABLE, but you cannot truncate table if this has constraints so drop first the constraints then truncate the table then add again the constraint.
mel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply