June 18, 2004 at 4:38 pm
I'm intending to use Cascading deletes in a SQL 2000 project. However, I don't really want to allow users to just blindly delete a record if it's connected to dozens or possibly hundreds of others through FK's.
I was wondering if anyone had ever come across a script that, given a table name and a record ID, would pull from the system tables info on all FK related tables and check for related records, returning a count of each.
That way, I could query that function to check just how big an impact there'd be and let the user know.
Any ideas?
Thanks
June 20, 2004 at 10:48 am
Hai,
I wrote One procedure for this.
The Required Input Parameters are
TableName
ColumnValue
OutPutParamters are
IsReferenseExists Returns
1- for depency Records Exists
0- for no depency Records Exists
Create
procedure GEN_SP_CHECK_REFERENCE_EXISTS
(@tabValue
varchar(2000),
@colValue
varchar(2000),
@RefExists
int OUTPUT ,
@RefExistsTabs
varchar(4000) OUTPUT
)
AS
BEGIN
Set
@RefExistsTabs=''
Set
@RefExists=0
declare
@Colname varchar(200),@Tabname varchar(200)
declare
@lstrSql nvarchar(2000)
Declare
lcur Cursor local for
Select
syscolumns.Name,object_name(fkeyid) as FkeyTableName
from
sysreferences ,syscolumns
where
sysreferences.fkeyid=syscolumns.id and
fkey1=syscolumns.colid
and object_name
(rkeyid)=@tabValue
open
lcur
fetch next from
lcur into @Colname,@Tabname
while
@@fetch_status=0
begin
set
@lstrSql= 'Select '+@Colname+ ' from '+@Tabname+ ' where '+@Colname+ ' ='''+@colValue+''''
--print @lstrSql
exec
sp_executesql @lstrSql
if
@@rowcount>0
begin
if
len(@RefExistsTabs)>0
set
@RefExistsTabs= @RefExistsTabs+ ',' +@Tabname
else
set
@RefExistsTabs= @Tabname
set
@RefExists=1
end
fetch next from
lcur into @Colname,@Tabname
end
END
Example:
The below example is for checking dependcy records existense in all the foreignkey tables of
'Employees' with Primarykeycol Value of column EmployeeID with 4
declare @tabValue varchar(2000)
declare @colValue varchar(2000)
declare @RefExists int
declare @RefExistsTabs varchar(4000)
set @tabValue='Employees'
set @colValue='4'
EXEC GEN_SP_CHECK_REFERENCE_EXISTS @tabValue,@colValue,@RefExists OUTPUT ,@RefExistsTabs OUTPUT
if @RefExists=1
begin
Print 'Depndency Records Exists'
PRINT 'Dependency Exist Tables list are '+ @RefExistsTabs
end
else
Print 'Depndency Records does not Exists'
Output is as follows
-------------------
Depndency Records Exists
Dependency Exist Tables list are Orders,EmployeeTerritories
Note: The above procedure will work only for singel column Foreign key only.
if any one had better solution please mail me.
Murthy
June 21, 2004 at 9:05 am
Excellent, Just what I was looking for.
I made a few small mods, basically to present an easier to deal with result set. Here's what I ended up with:
This assumes an integer primary key on all your tables. It's easy to change if that's not the case.
The result set returned has the name of each table with dependent records and how many records exist. I combine multiple FK relationships into one count, so you won't get the same table in the list twice. I also remove all those tables that are related but have no related records. That way, You simply call this function and if the returned resultset contains no records, it will be safe to delete the record in question (since there are no foreign dependent rows).
Thanks again for you help!
Alter procedure spGetDependencyCounts (
@Table varchar(2000),
@RowID int
)
AS
BEGIN
set nocount ON
declare @Colname varchar(200), @Tablename varchar(200)
declare @cnt int
declare @lstrSql nvarchar(2000)
Declare lcur Cursor local for
Select syscolumns.Name,object_name(fkeyid) as FkeyTableName
from sysreferences ,syscolumns
where
sysreferences.fkeyid=syscolumns.id and
fkey1=syscolumns.colid
and object_name(rkeyid)=@Table
open lcur
create table #Temp (TableName Varchar(128), DependentRows int)
fetch next from lcur into @Colname,@Tablename
while @@fetch_status=0
begin
set @lstrSql= 'insert into #temp Select TableName = ''' + @TableName + ''', DependentRows = Count(' + @Colname + ') from ' + @TableName + ' where ' + @Colname + ' =' + cast(@RowID as varchar(16)) + ''
exec (@lstrSql)
fetch next from lcur into @Colname,@TableName
end
-- We group by here to handle situations where the same table is referenced by
-- several foreign keys in our source table
-- We also don't want to include FK tables that don't have any dependent rows
select TableName, Sum(DependentRows) as DependentRows from #Temp where DependentRows > 0 Group by TableName
drop table #Temp
set nocount OFF
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply