Hi,
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 so.name as Tablename from syscolumns sc
left join sysobjects so on sc.id = so.id where so.xtype = 'U' and sc.name ='OrgID'
----------------------------------------------------------------
The above query gives me list of all the tables in the current database which contains OrgID field in it as under,
TableName
----------
M_Employee
M_Project
M_Customer
M_Supplier
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,
Atul