This blog is useful to all my friends who are working on the .Net Technology and wants to enhance their skills as well their problem solving ability.

Wednesday, February 15, 2017

SQL Server database level Store procedure to check dependent records

sp to check dependent records
CREATE PROCEDURE [dbo].[sp_IsDependentRecordExist]
 @tableName varchar(250),
 @idValue varchar(50)

AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    declare @tableCount as int
 declare @RowID as int
 declare @recordExist as bit
 Declare @tbl varchar(200)
 Declare @col as varchar(200)
 declare @refCount as int
 set @recordExist = 0
 --create temp table for dependent table
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END
 CREATE TABLE #dependentTables
  (tableid int,Parent_Table varchar(250),Parent_Column varchar(100),Reference_Table varchar(250),Reference_Column varchar(100)) 
 -- INSERT data INTO #dependentTables
 INSERT INTO #dependentTables
  (tableid,Parent_Table,Parent_Column,Reference_Table,Reference_Column)
 SELECT
  row_number() over (order by (select NULL)) as tableid,
  SO_P.name as Parent_Table
  ,SC_P.name as Parent_Column
  ,SO_R.name as Reference_Table
  ,SC_R.name as Reference_Column
 from sys.foreign_key_columns FKC
  inner join sys.objects SO_P on SO_P.object_id = FKC.parent_object_id
  inner join sys.columns SC_P on (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id)
  inner join sys.objects SO_R on SO_R.object_id = FKC.referenced_object_id
  inner join sys.columns SC_R on (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id)
  where
   ((SO_R.name = @tableName) AND (SO_R.type = 'U'))

 SELECT @tableCount = COUNT(1) from #dependentTables
 SET @RowID = (SELECT TOP 1 tableid from #dependentTables order by tableid)
 --loop through dependent Tables
 WHILE (@tableCount > 0)
 BEGIN
 
  SELECT @tbl =Parent_Table from #dependentTables where tableid=@RowID
  SELECT @col=  Parent_Column from #dependentTables where tableid=@RowID
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  CREATE TABLE #Data (var int)
 
  if ((select count(1) from sys.columns where Name = N'IsDeleted' and Object_ID = Object_ID(@tbl)) > 0)
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue + ' and IsDeleted=0')
  end
  else
  begin
   INSERT #Data exec ('Select count(1) from ' + @tbl + ' where '+  @col +'='+ @idValue)
  end
   
  SELECT @refCount = var from #Data
 
  IF OBJECT_ID('tempdb..#Data') IS NOT NULL
  BEGIN
     DROP TABLE #Data
  END
  if @refCount>0
  begin
   set @recordExist = 1
   break
  end
  SET @RowID = @RowID + 1
  set @tableCount = @tableCount - 1
 END
 --drop temp tables
 IF OBJECT_ID('tempdb..#dependentTables') IS NOT NULL
 BEGIN
    DROP TABLE #dependentTables
 END

 select @recordExist as IsDependentRecordExist

END