Posted By

hairajeshk on 08/04/10


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite


Delete all dependent rows in all the other tables

 / Published in: SQL

delete all the dependent rows in all the other tables (like cascade option)

  1. Normally, IF you try AND DELETE a record FROM a TABLE that IS constrained BY a FOREIGN KEY, you’ll get an error message. This procedure checks FOR any FOREIGN KEYS FOR the TABLE, deletes any child records, then deletes the intended record.
  2. It REFERENCES the system TABLES sysforeignkeys, sysobjects AND syscolumns. Sysforeignkeys does what it says ON the tin – it’s a list of ALL FOREIGN KEYS IN the DATABASE. It doesn’t contain actual TABLE AND FIELD names, instead it contains links TO the sysobjects (TABLES, stored procedures, views etc) AND syscolumns (FIELDS).
  4. The procedure works LIKE this – IF we want TO DELETE a record FROM TABLE X, we look IN the sysforeignkeys TABLE FOR ALL REFERENCES WHERE TABLE X IS the parent TABLE. It may be involved IN several such FK’s. ALL we do IS recursively go through these FK’s, deleting the child TABLE records that are linked TO the record we want TO DELETE.
  6. DELETE ALL records FROM TABLE X WHERE field1 equals '234'
  8. DELETE FROM X WHERE field1 = '234'- TABLE Y IS linked TO X through the Y_ID FIELD, so
  10. DELETE FROM Y WHERE Y_ID IN (SELECT Y_ID FROM X WHERE field1 = '234')- TABLE Z IS linked TO Y through the Z_ref FIELD
  14. (SELECT Y_ID FROM X WHERE field1 = '234'))
  16. AS you can see FROM the above example, IF one of the child TABLES IS also involved IN a FK constraint, we also need TO DELETE the relating rows IN it’s child TABLES.
  18. Here’s the code FOR the procedure:
  20. CREATE Procedure spDeleteRows
  21. /*
  22. Recursive row delete procedure.
  24. It deletes all rows in the table specified that conform to the criteria selected,
  25. while also deleting any child/grandchild records and so on. This is designed to do the
  26. same sort of thing as Access's cascade delete function. It first reads the sysforeignkeys
  27. table to find any child tables, then deletes the soon-to-be orphan records from them using
  28. recursive calls to this procedure. Once all child records are gone, the rows are deleted
  29. from the selected table. It is designed at this time to be run at the command line. It could
  30. also be used in code, but the printed output will not be available.
  31. */
  32. (
  33. @cTableName varchar(50), /* name of the table where rows are to be deleted */
  34. @cCriteria nvarchar(1000), /* criteria used to delete the rows required */
  35. @iRowsAffected int OUTPUT /* number of records affected by the delete */
  36. )
  37. AS
  38. SET nocount ON
  39. declare @cTab varchar(255), /* name of the child table */
  40. @cCol varchar(255), /* name of the linking field on the child table */
  41. @cRefTab varchar(255), /* name of the parent table */
  42. @cRefCol varchar(255), /* name of the linking field in the parent table */
  43. @cFKName varchar(255), /* name of the foreign key */
  44. @cSQL nvarchar(1000), /* query string passed to the sp_ExecuteSQL procedure */
  45. @cChildCriteria nvarchar(1000), /* criteria to be used to delete
  46.   records from the child table */
  47. @iChildRows int /* number of rows deleted from the child table */
  49. /* declare the cursor containing the foreign key constraint information */
  51. SELECT AS Tab,
  52. AS Col,
  53. AS RefTab,
  54. AS RefCol,
  55. AS FKName
  56. FROM dbo.sysforeignkeys FK
  57. INNER JOIN dbo.syscolumns SC1 ON FK.fkeyid =
  58. AND FK.fkey = SC1.colid
  59. INNER JOIN dbo.syscolumns SC2 ON FK.rkeyid =
  60. AND FK.rkey = SC2.colid
  61. INNER JOIN dbo.sysobjects SO1 ON FK.fkeyid =
  62. INNER JOIN dbo.sysobjects SO2 ON FK.rkeyid =
  63. INNER JOIN dbo.sysobjects FO ON FK.constid =
  64. WHERE SO2.Name = @cTableName
  66. OPEN cFKey
  67. FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
  69. BEGIN
  70. /* build the criteria to delete rows from the child table. As it uses the
  71.   criteria passed to this procedure, it gets progressively larger with
  72.   recursive calls */
  73. SET @cChildCriteria = @cCol + ' in (SELECT [' + @cRefCol + '] FROM [' +
  74. @cRefTab +'] WHERE ' + @cCriteria + ')'
  75. print 'Deleting records from table ' + @cTab
  76. /* call this procedure to delete the child rows */
  77. EXEC spDeleteRows @cTab, @cChildCriteria, @iChildRows OUTPUT
  78. FETCH NEXT FROM cFKey INTO @cTab, @cCol, @cRefTab, @cRefCol, @cFKName
  79. END
  80. Close cFKey
  81. DeAllocate cFKey
  82. /* finally delete the rows from this table and display the rows affected */
  83. SET @cSQL = 'DELETE FROM [' + @cTableName + '] WHERE ' + @cCriteria
  84. print @cSQL
  85. EXEC sp_ExecuteSQL @cSQL
  86. print 'Deleted ' + CONVERT(varchar, @@ROWCOUNT) + ' records from table ' + @cTableName

Report this snippet  


RSS Icon Subscribe to comments
Posted By: shefu on October 4, 2011

is there any way to find the total rows affected by a delete statement (including all the rows deleted on cascade in the children table referring those in parent table )

Posted By: vijay9mca on August 10, 2016

I'm getting error at line 30 30/1 PLS-00103: Encountered the symbol "CURSOR" when expecting one of the following: := . ( @ % ; not null range default character

You need to login to post a comment.