--------------------------------------------------------------------

Listing 2

--------------------------------------------------------------------


USE master

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ExamineChildren') DROP PROCEDURE dbo.sp_ExamineChildren
GO
CREATE PROCEDURE dbo.sp_ExamineChildren
    @DBTable varchar(100),
    @DBValue int
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @TPre varchar(10)

DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint

SET @TPre = ''

SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1

DECLARE @Task varchar(4000)

DECLARE @Name varchar(100)
DECLARE @Item varchar(100)
DECLARE @PKey varchar(100)

CREATE TABLE #Work (Name varchar(100), Rows int)

SET @Item = @TPre + @DBTable

SELECT @PKey = C.name
  FROM sysobjects AS O
  JOIN syscolumns AS C
    ON O.id = C.id
 WHERE O.name = @Item
   AND C.colid = 1

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

  DECLARE Tables CURSOR FAST_FORWARD FOR
   SELECT O.name
     FROM sysobjects AS O
     JOIN sysreferences AS R
       ON O.id = R.fkeyid
    WHERE R.rkeyid = OBJECT_ID(@Item)
 ORDER BY O.name

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

OPEN Tables

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

FETCH NEXT FROM Tables INTO @Name

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

WHILE @@FETCH_STATUS = 0 AND @Status = 0

    BEGIN

    SET @Task = 'INSERT #Work SELECT ' + CHAR(39) + @Name + CHAR(39) + ', COUNT(*) FROM ' + @Name + ' WHERE ' + @PKey + ' = ' + CONVERT(varchar(10),@DBValue)

    IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

    IF @Status = 0 SET @Status = @Return

    FETCH NEXT FROM Tables INTO @Name

    SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

    END

CLOSE Tables DEALLOCATE Tables

SELECT Name, Rows FROM #Work ORDER BY Name

DROP TABLE #Work

SET NOCOUNT OFF

RETURN (@Status)

GO

--------------------------------------------------------------------
 