SQL Server Performance Forum – Threads Archive
deleting data
I need to delete a row from a table and that table has a lot of dependencies for that column. I want to write a query that will find all the dependencies and delete the data for my given input. Any help would be appreciated. Thanks, StarWarsBigBangHi Please try with Cascade Delete option. Thanks
Rao
1st step would be to find all the foreign keys and tables which are referred in foreign key constraints.
2>Fire delete statement on all those child tables and
3>Finally fire delete on primary master table.
thanks for you response…. a) I am aware of the ‘Cascade Delete’ option. Unfortunately we are not allowed to use it.
b) Deleting from query analyser will definitely have to be the way as described by Ranjit Jain…..what I am looking forward to is writing a query for the same. Any help….. Thanks, StarWarsBigBang
try this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14594 Simple way is to see how the system does this…. SP_Fkeys will give u all the related tables and referred column names. If you know the system procedure to get the required information, what u should do next is use SP_HelpText system stored procedure to get the code behind the system storedprocedure. For eg. SP_helptext sp_fkeys this statement will give the code behind sp_fkeys… see how it fetches the information… make your own stored procedure or query from this… it may takes more time … but you will learn a lot…
Madhu
You can also query system provided views i.e.
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
Look in BOL for more..
This may give you some ideas
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=14149 Madhivanan Failing to plan is Planning to fail
]]>