-- Listing 1

USE master

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DeletePrimaryKeys') DROP PROCEDURE dbo.sp_DeletePrimaryKeys
GO
CREATE PROCEDURE dbo.sp_DeletePrimaryKeys
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL
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 @KPre varchar(10)

DECLARE @KDo3 tinyint
DECLARE @KDo4 tinyint

SET @KPre = 'key'

SET @KDo3 = LEN(@KPre)
SET @KDo4 = LEN(@KPre) + 1

DECLARE @Task varchar(2000)

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

-- Delete Primary Keys

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT T.name, O.name
    FROM sysobjects AS O
    JOIN sysobjects AS T
      ON O.parent_obj = T.id
   WHERE ISNULL(OBJECTPROPERTY(T.id,'IsMSShipped'),1) = 0
     AND O.xtype = 'PK'
     AND LEFT(T.name,@TDo3) = @TPre
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(T.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(T.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(T.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(T.name,@TDo4,100) NOT LIKE @PCExtra)
ORDER BY T.name, 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, @Item

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Delete Primary Keys'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'ALTER TABLE ' + @Name + ' DROP CONSTRAINT ' + @Item

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'ALTER TABLE ' + @Name + ' DROP CONSTRAINT ' + @Item

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CreatePrimaryKeys') DROP PROCEDURE dbo.sp_CreatePrimaryKeys
GO
CREATE PROCEDURE dbo.sp_CreatePrimaryKeys
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0
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 @KPre varchar(10)

DECLARE @KDo3 tinyint
DECLARE @KDo4 tinyint

SET @KPre = 'key'

SET @KDo3 = LEN(@KPre)
SET @KDo4 = LEN(@KPre) + 1

DECLARE @Task varchar(2000)

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

DECLARE @Wish varchar(40)

SET @Wish = CASE WHEN @PCUltra = 0 THEN 'NON' ELSE '' END + 'CLUSTERED'

-- Create Primary Keys

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT O.name, C.name
    FROM sysobjects AS O
    JOIN syscolumns AS C
      ON O.id = C.id
   WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
     AND RTRIM(O.type) = 'U'
     AND LEFT(O.name,@TDo3) = @TPre
     AND O.name NOT LIKE 'adt%'
     AND O.name NOT LIKE '%dtproper%'
     AND O.name NOT LIKE 'dt[_]%'
     AND C.colid = 1
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(O.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra)
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, @Item

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Create Primary Keys'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'ALTER TABLE ' + @Name + ' ADD CONSTRAINT ' + @KPre + 'PK' + SUBSTRING(@Name,@TDo4,100) + ' PRIMARY KEY ' + @Wish + ' (' + @Item + ')'

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'ALTER TABLE ' + @Name + ' ADD CONSTRAINT ' + @KPre + 'PK' + SUBSTRING(@Name,@TDo4,100) + ' PRIMARY KEY ' + @Wish + ' (' + @Item + ')'

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DeleteForeignKeys') DROP PROCEDURE dbo.sp_DeleteForeignKeys
GO
CREATE PROCEDURE dbo.sp_DeleteForeignKeys
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL
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 @KPre varchar(10)

DECLARE @KDo3 tinyint
DECLARE @KDo4 tinyint

SET @KPre = 'key'

SET @KDo3 = LEN(@KPre)
SET @KDo4 = LEN(@KPre) + 1

DECLARE @Task varchar(2000)

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

-- Delete Foreign Keys

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT T.name, O.name
    FROM sysobjects AS O
    JOIN sysobjects AS T
      ON O.parent_obj = T.id
   WHERE ISNULL(OBJECTPROPERTY(T.id,'IsMSShipped'),1) = 0
     AND O.xtype = 'F '
     AND LEFT(T.name,@TDo3) = @TPre
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(T.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(T.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(T.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(T.name,@TDo4,100) NOT LIKE @PCExtra)
ORDER BY T.name, 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, @Item

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Delete Foreign Keys'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'ALTER TABLE ' + @Name + ' DROP CONSTRAINT ' + @Item

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'ALTER TABLE ' + @Name + ' DROP CONSTRAINT ' + @Item

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CreateForeignKeys') DROP PROCEDURE dbo.sp_CreateForeignKeys
GO
CREATE PROCEDURE dbo.sp_CreateForeignKeys
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL
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 @KPre varchar(10)

DECLARE @KDo3 tinyint
DECLARE @KDo4 tinyint

SET @KPre = 'key'

SET @KDo3 = LEN(@KPre)
SET @KDo4 = LEN(@KPre) + 1

DECLARE @Task varchar(2000)

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

DECLARE @FKey varchar(100)

SET @FKey = '*ID'

-- Create Foreign Keys

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT O.name, C.name, T.name
    FROM sysobjects AS O
    JOIN syscolumns AS C
      ON O.id = C.id
    JOIN sysobjects AS T
      ON C.name = REPLACE(@FKey,'*',SUBSTRING(T.name,@TDo4,100))
     AND C.colid > 1
   WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
     AND RTRIM(O.type) = 'U'
     AND LEFT(O.name,@TDo3) = @TPre
     AND O.name NOT LIKE 'adt%'
     AND O.name NOT LIKE '%dtproper%'
     AND O.name NOT LIKE 'dt[_]%'
     AND ISNULL(OBJECTPROPERTY(T.id,'IsMSShipped'),1) = 0
     AND RTRIM(T.type) = 'U'
     AND LEFT(T.name,@TDo3) = @TPre
     AND T.name NOT LIKE 'adt%'
     AND T.name NOT LIKE '%dtproper%'
     AND T.name NOT LIKE 'dt[_]%'
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(O.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra)
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, @Item, @PKey

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Create Foreign Keys'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'ALTER TABLE ' + @Name + ' ADD CONSTRAINT ' + @KPre + 'FK' + SUBSTRING(@Name,@TDo4,100) + SUBSTRING(@PKey,@TDo4,100) + ' FOREIGN KEY (' + @Item + ') REFERENCES ' + @PKey + ' (' + @Item + ')'

        FETCH NEXT FROM Tables INTO @Name, @Item, @PKey

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'ALTER TABLE ' + @Name + ' ADD CONSTRAINT ' + @KPre + 'FK' + SUBSTRING(@Name,@TDo4,100) + SUBSTRING(@PKey,@TDo4,100) + ' FOREIGN KEY (' + @Item + ') REFERENCES ' + @PKey + ' (' + @Item + ')'

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item, @PKey

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_DeleteForeignKeyIndexes') DROP PROCEDURE dbo.sp_DeleteForeignKeyIndexes
GO
CREATE PROCEDURE dbo.sp_DeleteForeignKeyIndexes
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL
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 @IPre varchar(10)

DECLARE @IDo3 tinyint
DECLARE @IDo4 tinyint

SET @IPre = 'idx'

SET @IDo3 = LEN(@IPre)
SET @IDo4 = LEN(@IPre) + 1

DECLARE @Task varchar(2000)

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

-- Delete Indexes

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT O.name, I.name
    FROM sysobjects AS O
    JOIN sysindexes AS I
      ON O.id = I.id
    JOIN sysobjects AS T
      ON I.name = @IPre + SUBSTRING(T.name,@TDo4,100)
     AND I.indid BETWEEN 2 AND 254
   WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
     AND RTRIM(O.type) = 'U'
     AND LEFT(O.name,@TDo3) = @TPre
     AND O.name NOT LIKE 'adt%'
     AND O.name NOT LIKE '%dtproper%'
     AND O.name NOT LIKE 'dt[_]%'
     AND RTRIM(T.type) = 'U'
     AND LEFT(T.name,@TDo3) = @TPre
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(O.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra)
ORDER BY O.name, I.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, @Item

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Delete Indexes'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'DROP INDEX ' + @Name + '.' + @Item

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'DROP INDEX ' + @Name + '.' + @Item

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CreateForeignKeyIndexes') DROP PROCEDURE dbo.sp_CreateForeignKeyIndexes
GO
CREATE PROCEDURE dbo.sp_CreateForeignKeyIndexes
    @DBUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBAdmin tinyint       = NULL
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 @IPre varchar(10)

DECLARE @IDo3 tinyint
DECLARE @IDo4 tinyint

SET @IPre = 'idx'

SET @IDo3 = LEN(@IPre)
SET @IDo4 = LEN(@IPre) + 1

DECLARE @Task varchar(2000)

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

DECLARE @Wish varchar(20)

SET @Wish = CONVERT(varchar(10),ISNULL(@DBAdmin,90))

-- Create Indexes

 DECLARE Tables CURSOR FAST_FORWARD FOR
  SELECT O.name, C.name, K.name
    FROM sysobjects AS O
    JOIN syscolumns AS C
      ON O.id = C.id
    JOIN sysreferences AS R
      ON O.id    = R.fkeyid
     AND C.colid = R.fkey1
     AND C.colid > 1
    JOIN sysobjects AS K
      ON R.rkeyid = K.id
   WHERE ISNULL(OBJECTPROPERTY(O.id,'IsMSShipped'),1) = 0
     AND RTRIM(O.type) = 'U'
     AND LEFT(O.name,@TDo3) = @TPre
     AND O.name NOT LIKE 'adt%'
     AND O.name NOT LIKE '%dtproper%'
     AND O.name NOT LIKE 'dt[_]%'
     AND (@DBIntra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBIntra)+'|') > 0)
     AND (@DBExtra IS NULL OR CHARINDEX('|'+SUBSTRING(O.name,@TDo4,100)+'|','|'+(@DBExtra)+'|') = 0)
     AND (@PCIntra IS NULL OR               SUBSTRING(O.name,@TDo4,100)     LIKE @PCIntra)
     AND (@PCExtra IS NULL OR               SUBSTRING(O.name,@TDo4,100) NOT LIKE @PCExtra)
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, @Item, @PKey

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

IF @DBUltra = 0

    BEGIN

    PRINT '-- Create Indexes'
    PRINT SPACE(0)

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        PRINT 'CREATE INDEX ' + @IPre + SUBSTRING(@PKey,@TDo4,100) + ' ON ' + @Name + ' (' + @Item + ') WITH FILLFACTOR = ' + @Wish

        FETCH NEXT FROM Tables INTO @Name, @Item, @PKey

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

        END

    PRINT SPACE(0)

    END

ELSE

    BEGIN

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Task = 'CREATE INDEX ' + @IPre + SUBSTRING(@PKey,@TDo4,100) + ' ON ' + @Name + ' (' + @Item + ') WITH FILLFACTOR = ' + @Wish

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

        IF @Status = 0 SET @Status = @Return

        FETCH NEXT FROM Tables INTO @Name, @Item, @PKey

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

        END

    END

CLOSE Tables DEALLOCATE Tables

SET NOCOUNT OFF

RETURN (@Status)

GO

-- 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

-- Listing 3

USE master

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_FetchRelatedRows') DROP PROCEDURE dbo.sp_FetchRelatedRows
GO
CREATE PROCEDURE dbo.sp_FetchRelatedRows
    @DBChain varchar(2000),
    @DBWhere varchar(2000) = NULL,
    @DBAdmin int           = NULL,
    @DBField varchar(100)  = NULL,
    @DBValue int           = NULL,
    @PCField varchar(100)  = NULL,
    @PCValue int           = NULL
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 @DBAT varchar(40)

DECLARE @Task varchar(8000)

DECLARE @Bank varchar(8000)

DECLARE @Cash varchar(8000)

DECLARE @Loan varchar(800)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)
DECLARE @PKey varchar(100)
DECLARE @ZKey varchar(100)

DECLARE @Lock varchar(100)

DECLARE @Link varchar(100)

DECLARE @Work int

DECLARE @Wish int

DECLARE @Rows int

SET @DBAT = '##FetchRows' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAT + CHAR(39) + ') DROP TABLE ' + @DBAT

EXECUTE (@Task)

CREATE TABLE #Work (Work int IDENTITY(1,1), Name varchar(100), PKey varchar(100), Bank varchar(7700))

SET @Wish = 1

SET @Work = CHARINDEX('|',(@DBChain)+'|')

WHILE @Work > 0

    BEGIN

    SET @Name = SUBSTRING(@DBChain,@Wish,@Work-@Wish)

    INSERT #Work (Name) VALUES (@Name)

    SET @Wish = @Work + 1

    SET @Work = CHARINDEX('|',(@DBChain)+'|',@Wish)

    END

UPDATE #Work SET
       PKey = C.name
  FROM #Work AS T
  JOIN sysobjects AS O
    ON O.name = @TPre + T.Name
  JOIN syscolumns AS C
    ON C.id = O.id
   AND C.colid = 1

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

SELECT @Rows = COUNT(*) FROM #Work

IF @DBWhere IS NOT NULL

    BEGIN

    SET @Cash = REPLACE(@DBWhere,'WHERE'       ,CHAR(32))
    SET @Cash = REPLACE(@Cash,         CHAR(94),CHAR(39))
    SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
    SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))

    END

SET @Work = 1

SELECT @Name = Name, @PKey = PKey FROM #Work WHERE Work = @Work

SET @Lock = @TPre + @Name

SET @Bank = @TPre + @Name + ' WITH (NOLOCK)'

SET @Task = 'SELECT * INTO ' + @DBAT + ' FROM ' + @Bank + ' WHERE '

          + CASE WHEN @DBWhere IS NULL THEN '0 = 0' ELSE '(' + @Cash + ')' END

UPDATE #Work SET Bank = @Bank WHERE Work = @Work

IF @DBField IS NOT NULL AND @DBValue IS NOT NULL

    BEGIN

    IF EXISTS (SELECT *
                 FROM sysobjects AS O
                 JOIN syscolumns AS C
                   ON O.id = C.id
                WHERE RTRIM(O.type) = 'U' AND O.name = @Lock AND C.name = @DBField)

    SET @Task = @Task + ' AND ' + REPLACE(@DBField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@DBValue)

    END

IF @PCField IS NOT NULL AND @PCValue IS NOT NULL

    BEGIN

    IF EXISTS (SELECT *
                 FROM sysobjects AS O
                 JOIN syscolumns AS C
                   ON O.id = C.id
                WHERE RTRIM(O.type) = 'U' AND O.name = @Lock AND C.name = @PCField)

    SET @Task = @Task + ' AND ' + REPLACE(@PCField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@PCValue)

    END

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

IF @Status = 0 SET @Status = @Return

WHILE @Work < @Rows AND @Status = 0

    BEGIN

    SET @Work = @Work + 1

    SELECT @Name = Name, @PKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Work

    IF ISNULL(@DBAdmin,0) <= 0 SET @Cash = @Bank

    SET @Wish = @Work

    WHILE @Wish > 1

        BEGIN

        SET @Wish = @Wish - 1

        SELECT @Same = Name, @ZKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Wish

        IF ISNULL(@DBAdmin,0) <= 0 SET @Cash = @Bank

        SET @Link = NULL

        IF EXISTS (SELECT *
                     FROM sysobjects AS O
                     JOIN syscolumns AS C
                       ON O.id = C.id
                    WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @ZKey) SET @Link = @ZKey

        IF EXISTS (SELECT *
                     FROM sysobjects AS O
                     JOIN syscolumns AS C
                       ON O.id = C.id
                    WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Same AND C.name = @PKey) SET @Link = @PKey

        IF @Link IS NOT NULL

            BEGIN

            SET @Loan = ' JOIN ' + @TPre + @Name + ' WITH (NOLOCK) ON ' + @TPre + @Same + '.' + @Link + ' = ' + @TPre + @Name + '.' + @Link

            SET @Bank = @Bank + @Loan
            SET @Cash = @Cash + @Loan

            IF @PCField IS NOT NULL AND @PCValue IS NOT NULL

                BEGIN

                IF EXISTS (SELECT *
                             FROM sysobjects AS O
                             JOIN syscolumns AS C
                               ON O.id = C.id
                            WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @PCField)

                    BEGIN

                    SET @Loan = ' AND ' + @TPre + @Name + '.' + REPLACE(@PCField,CHAR(32),CHAR(95)) + ' = ' + CONVERT(varchar(10),@PCValue)

                    SET @Bank = @Bank + @Loan
                    SET @Cash = @Cash + @Loan

                    END

                END

            WHILE @Wish > 1

                BEGIN

                SET @Wish = @Wish - 1

                SELECT @Same = Name, @ZKey = PKey FROM #Work WHERE Work = @Wish

                SET @Link = NULL

                IF EXISTS (SELECT *
                             FROM sysobjects AS O
                             JOIN syscolumns AS C
                               ON O.id = C.id
                            WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Name AND C.name = @ZKey) SET @Link = @ZKey

                IF EXISTS (SELECT *
                             FROM sysobjects AS O
                             JOIN syscolumns AS C
                               ON O.id = C.id
                            WHERE RTRIM(O.type) = 'U' AND O.name = @TPre + @Same AND C.name = @PKey) SET @Link = @PKey

                IF @Link IS NOT NULL

                    BEGIN

                    SET @Loan = ' AND ' + @TPre + @Same + '.' + @Link + ' = ' + @TPre + @Name + '.' + @Link

                    SET @Bank = @Bank + @Loan
                    SET @Cash = @Cash + @Loan

                    END

                END

            END

        ELSE

            BEGIN

            IF @Wish = 1 UPDATE #Work SET Name = CHAR(45) WHERE Work = @Work

            END

        END

    UPDATE #Work SET Bank = @Cash WHERE Work = @Work

    END

SET @Work = 1

SELECT @Name = Name, @PKey = PKey, @Cash = Bank FROM #Work WHERE Work = @Work

IF ISNULL(@DBAdmin,0) <  0 SET @Cash = @Bank

SET @Task = 'SELECT DISTINCT ' + @DBAT + '.* FROM ' + REPLACE(REPLACE(@Cash,@Lock+CHAR(32),@DBAT+CHAR(32)),@Lock+CHAR(46),@DBAT+CHAR(46))

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

IF @Status = 0 SET @Status = @Return

WHILE @Work < @Rows AND @Status = 0

    BEGIN

    SET @Work = @Work + 1

    SELECT @Name = Name, @Cash = Bank FROM #Work WHERE Work = @Work

    IF ISNULL(@DBAdmin,0) <  0 SET @Cash = @Bank

    SET @Task = 'SELECT DISTINCT ' + @TPre + @Name + '.* FROM ' + REPLACE(REPLACE(@Cash,@Lock+CHAR(32),@DBAT+CHAR(32)),@Lock+CHAR(46),@DBAT+CHAR(46))

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

    IF @Status = 0 SET @Status = @Return

    END

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAT + CHAR(39) + ') DROP TABLE ' + @DBAT

EXECUTE (@Task)

DROP TABLE #Work

SET NOCOUNT OFF

RETURN (@Status)

GO