Click here to Skip to main content
15,072,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to create a stored procedure in which,
I want to prevent the user from deleting a Master record if its ID exist in any other table in the database.
I have taken example of OrgID which is PK in Organization table.

---- Query ---------------------------------------------------
Select as Tablename from syscolumns sc
left join sysobjects so on =  where so.xtype = 'U' and ='OrgID'


The above query gives me list of all the tables in the current database which contains OrgID field in it as under,


Now what i want is to enhance the above query so that it gives me total record count that comes after counting up the records in front of
each table. This number if greater than zero will determine that OrgID record exist somewhere.

|TableName     | count     |
| M_Employee   |    8      |
| M_Project    |    0      |
| M_Customer   |   12      |
| M_Supplier   |    0      |
|                  20      |

20 will be the returned value by the stored procedure.

Thanks in advance,
Updated 22-May-12 9:42am

1 solution

If you want that parent record isn't deleted if it has child records, then you must use ON DELETE NO ACTION definition on the foreign key. See CREATE TABLE[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900