2

I have lookup table, whose records are being referenced in other tables. For example, a countries lookup table, and a user table referc the country id from countries table.

I want to know, how i can get count of references and all the rows in other tables, which have a record with a country id.

If a reference of a row is found anywhere else, i need to show an alert to user that this record is being referened, and cannot be deleted, and list down all the referenced rows in a grid.

I have seen a similar topic at

SQL Server: how to know if any row is referencing the row to delete

But the answer didn't helped at all, and gave an error.

Community
  • 1
  • 1
suleman
  • 167
  • 1
  • 1
  • 8

5 Answers5

1

If you give reference as a foreign key, then sqlserver easily help you by Viewing dependensy and you just select query to get all table 

select * from countrytable where countryid in
(select countryid from usertable )

or 

select * from countrytable  c
inner join usertable u on c.countryid = u.countryid
Deepshikha
  • 9,896
  • 2
  • 21
  • 21
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
0
DECLARE @TableName sysname = 'YourTable'

DECLARE @Command VARCHAR(MAX) 

SELECT @Command = isnull(@Command + ' UNION ALL ', '') + 
       'Select count(*) ,''' + SCHEMA_NAME(obj.schema_id)+'.'+OBJECT_NAME(fkc.parent_object_id) + ''' '+
       'From ' + SCHEMA_NAME(obj.schema_id)+'.'+OBJECT_NAME(fkc.parent_object_id) + ' '+
       'Where ' + col.name+ ' IS NOT NULL' 
from sys.foreign_key_columns fkc
INNER JOIN sys.columns col on fkc.parent_object_id = col.object_id and fkc.parent_column_id = col.column_id
INNER JOIN sys.objects obj ON obj.object_id = col.object_id
where object_name(referenced_object_id) = @TableName

execute (@Command)
mehdi lotfi
  • 11,194
  • 18
  • 82
  • 128
0

You can use merge like 

 Merge countrytable  as c
using usertable as t on t.cid = c.cid
when not matched then delete
Azar
  • 1,852
  • 15
  • 17
0

There is also one more efficient way 

 ON DELETE NO ACTION

If a row is referenced it will throw an error that it is refernced by other table which is your exact requirement.

Azar
  • 1,852
  • 15
  • 17
  • I'm afraid that you missed a point in question that my requirement was to know in advance if there are any references to current rows, I do not want to handle the SqlException - but show an alert if the row is being referenced in some other tables. – suleman Jun 25 '14 at 13:05
0

You can find the references using this

SELECT  *
FROM    Countries C
WHERE   EXISTS (SELECT 1 FROM Users U WHERE U.CountryID = C.CountryID)
Jesuraja
  • 3,774
  • 4
  • 24
  • 48