-- Listing 1

USE master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CheckRowCounts') DROP PROCEDURE dbo.sp_CheckRowCounts
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CheckKeyValues') DROP PROCEDURE dbo.sp_CheckKeyValues
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ResetNextKeyValue') DROP PROCEDURE dbo.sp_ResetNextKeyValue
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_OrganizeKeyValues') DROP PROCEDURE dbo.sp_OrganizeKeyValues
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_FindDuplicateRows') DROP PROCEDURE dbo.sp_FindDuplicateRows
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_ChangeParentValue') DROP PROCEDURE dbo.sp_ChangeParentValue
GO
-- Create Routines
GO
CREATE PROCEDURE dbo.sp_CheckRowCounts
    @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 @Task varchar(2000)

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

DECLARE @Wink int
DECLARE @Work int

DECLARE @Mark bit

DECLARE @PKey int
DECLARE @ZKey int

CREATE TABLE #DBAH (TWork int IDENTITY(1,1),
                    TMark bit,
                    TName varchar(100),
                    CName varchar(100),
                    TRows int,
                    CFrom int,
                    CThru int,
                    CLast int,
                    CNext int)

CREATE TABLE #DBAY (TWork int,
                    TRows int,
                    CFrom int,
                    CThru int,
                    CLast int,
                    CNext int)

INSERT #DBAH (TMark, TName, CName)
SELECT ISNULL(OBJECTPROPERTY(O.id,'TableHasIdentity'),0), 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)

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

SELECT @Wink = COUNT(*) FROM #DBAH

SET @Work = 0

WHILE @Work < @Wink

    BEGIN

    SET @Work = @Work + 1

    SELECT @Mark = TMark, @Name = TName, @Same = CName FROM #DBAH WHERE TWork = @Work

    SET @Task = 'INSERT #DBAY SELECT ' + CONVERT(varchar(10),@Work)

              + CASE WHEN @Mark = 0

                     THEN ',COUNT(*),0,0,0,0'

                     ELSE ',COUNT(*)'

                        + ',ISNULL(MIN(' + @Same + '),0)'

                        + ',ISNULL(MAX(' + @Same + '),0)'

                        + ',IDENT_CURRENT(' + CHAR(39) + @Name + CHAR(39) + ')'

                        + ',IDENT_CURRENT(' + CHAR(39) + @Name + CHAR(39) + ') + IDENT_INCR (' + CHAR(39) + @Name + CHAR(39) + ')' END

              + '  FROM ' + @Name

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

    IF @Status = 0 SET @Status = @Return

    END

UPDATE #DBAH SET
       TRows = Z.TRows
     , CFrom = Z.CFrom
     , CThru = Z.CThru
     , CLast = Z.CLast
     , CNext = Z.CNext
  FROM #DBAH AS W
  JOIN #DBAY AS Z
    ON W.TWork = Z.TWork

SELECT CASE WHEN @PCUltra = 0 THEN SUBSTRING(TName,@TDo4,100) ELSE TName END AS TName, TRows, CFrom, CThru, CLast, CNext FROM #DBAH ORDER BY TName

DROP TABLE #DBAH

DROP TABLE #DBAY

SET NOCOUNT OFF

RETURN (@Status)

GO
CREATE PROCEDURE dbo.sp_CheckKeyValues
    @DBFetch varchar(4000),
    @DBField varchar(100),
    @DBUltra 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 @Task varchar(6000)

DECLARE @Bank varchar(4000)

DECLARE @Wish varchar(100)

DECLARE @RMin int
DECLARE @RMax int
DECLARE @ZMax int

DECLARE @Save int

DECLARE @Rows int

CREATE TABLE #DBAZ (Work int IDENTITY(1,1), RMin int, RMax int)

CREATE TABLE #DBAT (Work int IDENTITY(1,1), Wish int, Wink int)

SET @Bank = @TPre + @DBFetch

IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)

    BEGIN

    SET @Bank = CASE WHEN LEFT(LTRIM(@DBFetch),6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
    SET @Bank = REPLACE(@Bank,         CHAR(94),CHAR(39))
    SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
    SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))

    END

SET @Wish = REPLACE(@DBField,CHAR(32),CHAR(95))

SET @Task = 'INSERT #DBAT (Wish, Wink) SELECT T.' + @Wish + ', COUNT(*) FROM ' + @Bank + ' AS T GROUP BY T.' + @Wish + ' ORDER BY T.' + @Wish

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

IF @Status = 0 SET @Status = @Return

SELECT @Rows = COUNT(*) FROM #DBAT

SELECT @RMin = Wish, @Save = Wish - Work
  FROM #DBAT
 WHERE  Work = 1

SELECT @ZMax = Wish
  FROM #DBAT
 WHERE  Work = @Rows

WHILE ISNULL(@Rows,0) > 0

    BEGIN

    SET @Rows = 0

    SELECT @Rows = MIN(Work)
      FROM #DBAT
     WHERE  Wish - Work > @Save

    IF ISNULL(@Rows,0) > 0

        BEGIN

        SELECT @RMax = Wish
          FROM #DBAT
         WHERE  Work = @Rows - 1

        INSERT #DBAZ (RMin, RMax) VALUES (@RMin, @RMax)

        SELECT @RMin = Wish, @Save = Wish - Work
          FROM #DBAT
         WHERE  Work = @Rows

        END

    ELSE

        BEGIN

        INSERT #DBAZ (RMin, RMax) VALUES (@RMin, @ZMax)

        END

    END

IF @DBUltra = 0

    BEGIN

      SELECT Z.RMin              AS RangeFrom
           , Z.RMax              AS RangeThru
           , Z.RMax - Z.RMin + 1 AS RangeSize
           , SUM(T.Wink)         AS RangeRows
        FROM #DBAZ AS Z
        JOIN #DBAT AS T
          ON T.Wish BETWEEN Z.RMin AND Z.RMax
    GROUP BY Z.RMin
           , Z.RMax
    ORDER BY Z.RMin

    END

ELSE

    BEGIN

      SELECT I.RMax + 1          AS RangeFrom
           , T.RMin - 1          AS RangeThru
           , T.RMin - I.RMax - 1 AS RangeSize
           , 0                   AS RangeRows
        FROM #DBAZ AS I
        JOIN #DBAZ AS T
          ON I.Work = T.Work - 1
    ORDER BY 1

    END

DROP TABLE #DBAZ

DROP TABLE #DBAT

SET NOCOUNT OFF

RETURN (@Status)

GO
CREATE PROCEDURE dbo.sp_ResetNextKeyValue
    @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 @Task varchar(2000)

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

DECLARE @Wink int
DECLARE @Work int

DECLARE @PKey int
DECLARE @ZKey int

DECLARE @Rows int

DECLARE @Some int

CREATE TABLE #DBAH (TWork int IDENTITY(1,1),
                    TName varchar(100),
                    CName varchar(100),
                    TRows int,
                    CFrom int,
                    CThru int,
                    CLast int,
                    CNext int)

CREATE TABLE #DBAM (TWork int,
                    TRows int,
                    CFrom int,
                    CThru int,
                    CLast int,
                    CNext int)

INSERT #DBAH (TName, CName)
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)
   AND ISNULL(OBJECTPROPERTY(O.id,'TableHasIdentity'),0) > 0

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

SELECT @Wink = COUNT(*) FROM #DBAH

SET @Work = 0

WHILE @Work < @Wink

    BEGIN

    SET @Work = @Work + 1

    SELECT @Name = TName, @Same = CName FROM #DBAH WHERE TWork = @Work

    SET @Task = 'INSERT #DBAM SELECT ' + CONVERT(varchar(10),@Work)

              + ',COUNT(*)'

              + ',ISNULL(MIN(' + @Same + '),0)'

              + ',ISNULL(MAX(' + @Same + '),0)'

              + ',IDENT_CURRENT(' + CHAR(39) + @Name + CHAR(39) + ')'

              + ',IDENT_CURRENT(' + CHAR(39) + @Name + CHAR(39) + ') + IDENT_INCR (' + CHAR(39) + @Name + CHAR(39) + ')'

              + '  FROM ' + @Name

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

    IF @Status = 0 SET @Status = @Return

    END

UPDATE #DBAH SET
       TRows = Z.TRows
     , CFrom = Z.CFrom
     , CThru = Z.CThru
     , CLast = Z.CLast
     , CNext = Z.CNext
  FROM #DBAH AS W
  JOIN #DBAM AS Z
    ON W.TWork = Z.TWork

SET @Work = 0

WHILE @Work < @Wink

    BEGIN

    SET @Work = @Work + 1

    SELECT @Name = TName, @Rows = TRows, @PKey = CThru, @ZKey = CLast FROM #DBAH WHERE TWork = @Work

    IF @ZKey <> @PKey

        BEGIN

        IF @Rows = 0

            BEGIN

            SELECT @Some = SUM(dpages) FROM sysindexes WHERE id = OBJECT_ID(@Name) AND indid < 2

            IF ISNULL(@Some,0) > 0

                BEGIN

                SET @Task = 'DBCC CHECKIDENT (' + CHAR(39) + @Name + CHAR(39) + ', RESEED, 0)'

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

                IF @Status = 0 SET @Status = @Return

                END

            END

        ELSE

            BEGIN

            SET @Task = 'DBCC CHECKIDENT (' + CHAR(39) + @Name + CHAR(39) + ', RESEED, ' + CONVERT(varchar(10),@PKey) + ')'

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

            IF @Status = 0 SET @Status = @Return

            END

        END

    END

DROP TABLE #DBAH

DROP TABLE #DBAM

SET NOCOUNT OFF

RETURN (@Status)

GO
CREATE PROCEDURE dbo.sp_OrganizeKeyValues
    @DBTable varchar(100),
    @DBUltra 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 @Task varchar(8000)

DECLARE @Rich varchar(8000)

DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(4000)
DECLARE @DBAU varchar(8000)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)
DECLARE @Tame varchar(100)

DECLARE @Wish varchar(100)

DECLARE @Mark bit

DECLARE @PKey int
DECLARE @ZKey int
DECLARE @TKey int
DECLARE @IKey int

DECLARE @Rows int

DECLARE @Work tinyint

DECLARE @Wink tinyint

CREATE TABLE #DBAS (PKey int, ZKey int, TKey int, IKey int IDENTITY(1,1))

SET @Name = REPLACE(@DBTable,CHAR(32),CHAR(95))

SET @Wish = @TPre + @Name

SELECT @Tame = C.name
     , @Mark = ISNULL(OBJECTPROPERTY(O.id,'TableHasIdentity'),0)
  FROM sysobjects AS O
  JOIN syscolumns AS C
    ON O.id = C.id
 WHERE O.name = @Wish
   AND C.colid = 1

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

SET @Task = 'INSERT #DBAS (PKey) SELECT ' + @Tame + ' FROM ' + @Wish + ' ORDER BY ' + @Tame

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

IF @Status = 0 SET @Status = @Return

SELECT @Rows = COUNT(*) FROM #DBAS

IF @DBUltra <> 0

    BEGIN

    UPDATE #DBAS SET ZKey = I.IKey, TKey = I.IKey FROM #DBAS AS I LEFT JOIN (SELECT * FROM #DBAS) AS O ON I.IKey = O.PKey WHERE O.IKey IS NULL

    UPDATE #DBAS SET                TKey = I.IKey FROM #DBAS AS I WHERE I.IKey <> I.PKey

    END

ELSE

    BEGIN

    SELECT @IKey = MIN(IKey) FROM #DBAS WHERE PKey > @Rows

    SET @TKey = @Rows + 1

    SET @ZKey = @TKey - ISNULL(@IKey,@TKey)

    SET @TKey = 0

    WHILE @TKey < @ZKey

        BEGIN

        SELECT @PKey = MIN(IKey) FROM #DBAS WHERE PKey - IKey > @TKey

        UPDATE #DBAS SET ZKey = @PKey + @TKey, TKey = @PKey + @TKey WHERE IKey = @IKey

        SET @IKey = @IKey + 1

        SET @TKey = @TKey + 1

        END

    END

DECLARE Fields CURSOR FAST_FORWARD FOR
 SELECT C.name, C.colid
   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 O.name = @Wish
--  AND C.colid > 1

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

OPEN Fields

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

FETCH NEXT FROM Fields INTO @Same, @Wink

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

WHILE @@FETCH_STATUS = 0 AND @Status = 0

    BEGIN

    IF @Wink = 1 SET @DBAI =               @Same

    IF @Wink > 1 SET @DBAI = @DBAI + ',' + @Same

    IF @Wink = 2 SET @DBAO =               @Same

    IF @Wink > 2 SET @DBAO = @DBAO + ',' + @Same

    IF @Wink = 2 SET @DBAU =               @Same + '=M.' + @Same

    IF @Wink > 2 SET @DBAU = @DBAU + ',' + @Same + '=M.' + @Same

    FETCH NEXT FROM Fields INTO @Same, @Wink

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

    END

CLOSE Fields DEALLOCATE Fields

-- SET XACT_ABORT ON

   BEGIN TRANSACTION

SET @Task = CASE WHEN @Mark = 0 THEN '' ELSE ' SET IDENTITY_INSERT ' + @Wish + ' ON  ' END

          + ' INSERT ' + @Wish + ' (' + @DBAI + ')  '

SET @Rich = ' SELECT ZKey,' + @DBAO + ' FROM #DBAS AS I JOIN ' + @Wish + ' AS T ON I.PKey = T.' + @Tame + ' AND I.ZKey IS NOT NULL'

          + CASE WHEN @Mark = 0 THEN '' ELSE ' SET IDENTITY_INSERT ' + @Wish + ' OFF ' END

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

IF @Status = 0 SET @Status = @Return

IF @DBUltra <> 0

    BEGIN

    SET @Task = ' UPDATE ' + @Wish + ' SET ' + @DBAU

    SET @Rich = ' FROM #DBAS AS I JOIN ' + @Wish + ' AS T ON I.IKey = T.' + @Tame + ' AND I.ZKey IS NULL AND I.TKey IS NOT NULL'

              + ' JOIN (SELECT ' + @DBAI

              + ' FROM #DBAS AS W JOIN ' + @Wish + ' AS Z ON W.PKey = Z.' + @Tame + ' AND W.ZKey IS NULL AND W.TKey IS NOT NULL) AS M'

              + ' ON I.PKey = M.' + @Tame

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

    IF @Status = 0 SET @Status = @Return

    END

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(@Wish)

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 = 'UPDATE ' + @Name + ' SET ' + @Tame + ' = I.TKey FROM #DBAS AS I JOIN ' + @Name + ' AS T ON I.PKey = T.' + @Tame + ' AND I.TKey IS NOT NULL'

    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

SET @Task = 'DELETE ' + @Wish + ' WHERE ' + @Tame + ' > ' + CONVERT(varchar(10),@Rows)

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

IF @Status = 0 SET @Status = @Return

SET @Task = 'DBCC CHECKIDENT (' + CHAR(39) + @Wish + CHAR(39) + ', RESEED, 0)'

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

IF @Status = 0 SET @Status = @Return

SET @Task = 'DBCC CHECKIDENT (' + CHAR(39) + @Wish + CHAR(39) + ', RESEED   )'

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

IF @Status = 0 SET @Status = @Return

IF @Status = 0 OR @@TRANCOUNT <> 1 COMMIT ELSE ROLLBACK

DROP TABLE #DBAS

SET NOCOUNT OFF

RETURN (@Status)

GO
CREATE PROCEDURE dbo.sp_FindDuplicateRows
    @DBTable varchar(100),
    @DBWhere varchar(2000) = NULL,
    @DBThere varchar(2000) = NULL,
    @DBUltra 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 @DBAE varchar(40)

DECLARE @Task varchar(8000)

DECLARE @Bank varchar(2000)

DECLARE @Cash varchar(2000)

DECLARE @Risk varchar(2000)

DECLARE @Heck varchar(2000)

DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(4000)
DECLARE @DBAU varchar(4000)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)
DECLARE @Tame varchar(100)
DECLARE @Came varchar(100)

DECLARE @Work int

DECLARE @Wink int

DECLARE @Rank int

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

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

EXECUTE (@Task)

SET @Bank = @TPre + REPLACE(@DBTable,CHAR(32),CHAR(95))

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 @Risk = ''
SET @DBAI = ''
SET @DBAO = ''
SET @DBAU = ''

IF @DBThere IS NOT NULL

    BEGIN

    SET @DBThere = REPLACE(@DBThere,         CHAR(94),CHAR(39)) + '|'
    SET @DBThere = REPLACE(@DBThere,CHAR(45)+CHAR(45),CHAR(32))
    SET @DBThere = REPLACE(@DBThere,CHAR(47)+CHAR(42),CHAR(32))

    SET @Rank = 1

    SET @Wink = 1

    SET @Work = CHARINDEX('|',@DBThere,@Wink)

    WHILE @Work > 0

        BEGIN

        SET @Heck = SUBSTRING(@DBThere,@Wink,@Work-@Wink)

        SET @Came = 'Column' + RIGHT(STR(@Rank+1000,4),3)

        SET @Risk = @Risk + CASE WHEN LEN(@Risk) > 0 THEN ', '    ELSE '' END + @Heck
        SET @DBAI = @DBAI + CASE WHEN LEN(@DBAI) > 0 THEN ', '    ELSE '' END + @Heck + CASE WHEN @Heck LIKE '%[^A-Z0-9]%' THEN ' AS ' + @Came ELSE '' END
        SET @DBAO = @DBAO + CASE WHEN LEN(@DBAO) > 0 THEN ', '    ELSE '' END + @Heck +                                         ' AS ' + @Came
        SET @DBAU = @DBAU + CASE WHEN LEN(@DBAU) > 0 THEN ' AND ' ELSE '' END + @Heck + ' = ' + @Came

        SET @Rank = @Rank + 1

        SET @Wink = @Work + 1

        SET @Work = CHARINDEX('|',@DBThere,@Wink)

        END

    END

ELSE

    BEGIN

     DECLARE Fields CURSOR FAST_FORWARD FOR
      SELECT C.name, C.colid
        FROM sysobjects AS O
        JOIN syscolumns AS C
          ON O.id = C.id
       WHERE O.name = @Bank
         AND C.name LIKE '%Name%'
    ORDER BY C.colid

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

    OPEN Fields

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

    FETCH NEXT FROM Fields INTO @Same, @Rank

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

    WHILE @@FETCH_STATUS = 0 AND @Status = 0

        BEGIN

        SET @Came = 'Column' + RIGHT(STR(@Rank+1000,4),3)

        SET @Risk = @Risk + CASE WHEN LEN(@Risk) > 0 THEN ', '    ELSE '' END + @Same
        SET @DBAI = @DBAI + CASE WHEN LEN(@DBAI) > 0 THEN ', '    ELSE '' END + @Same
        SET @DBAO = @DBAO + CASE WHEN LEN(@DBAO) > 0 THEN ', '    ELSE '' END + @Same + ' AS ' + @Came
        SET @DBAU = @DBAU + CASE WHEN LEN(@DBAU) > 0 THEN ' AND ' ELSE '' END + @Same + ' = '  + @Came

        FETCH NEXT FROM Fields INTO @Same, @Rank

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

        END

    CLOSE Fields DEALLOCATE Fields

    IF LEN(@Risk) = 0

         DECLARE Fields CURSOR FAST_FORWARD FOR
          SELECT C.name, C.colid
            FROM sysobjects AS O
            JOIN syscolumns AS C
              ON O.id = C.id
           WHERE O.name = @Bank
             AND C.colid > 1
        ORDER BY C.colid

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

        OPEN Fields

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

        FETCH NEXT FROM Fields INTO @Same, @Rank

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

        WHILE @@FETCH_STATUS = 0 AND @Status = 0

            BEGIN

            SET @Came = 'Column' + RIGHT(STR(@Rank+1000,4),3)

            SET @Risk = @Risk + CASE WHEN LEN(@Risk) > 0 THEN ', '    ELSE '' END + @Same
            SET @DBAI = @DBAI + CASE WHEN LEN(@DBAI) > 0 THEN ', '    ELSE '' END + @Same
            SET @DBAO = @DBAO + CASE WHEN LEN(@DBAO) > 0 THEN ', '    ELSE '' END + @Same + ' AS ' + @Came
            SET @DBAU = @DBAU + CASE WHEN LEN(@DBAU) > 0 THEN ' AND ' ELSE '' END + @Same + ' = '  + @Came

            FETCH NEXT FROM Fields INTO @Same, @Rank

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

            END

        CLOSE Fields DEALLOCATE Fields

    END

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

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

IF @DBUltra = 0

    BEGIN

    SET @Task = '   SELECT ' + @DBAI + ', COUNT(*) AS Rows'

              + '     FROM ' + @Bank + ' AS T'

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

              + ' GROUP BY ' + @Risk

              + '   HAVING COUNT(*) > 1'

              + ' ORDER BY ' + @Risk

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

    IF @Status = 0 SET @Status = @Return

    END

ELSE

    BEGIN

    SET @Task = '    SELECT ' + @DBAO + ', 0 As Rows, IDENTITY(int,1,1) AS Match INTO ' + @DBAE

              + '      FROM ' + @Bank + ' AS T'

              + '     WHERE 0 = 1'

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

    IF @Status = 0 SET @Status = @Return

    SET @Task = '   INSERT ' + @DBAE

              + '   SELECT ' + @DBAO + ', COUNT(*)'

              + '     FROM ' + @Bank + ' AS T'

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

              + ' GROUP BY ' + @Risk

              + '   HAVING COUNT(*) > 1'

              + ' ORDER BY ' + @Risk

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

    IF @Status = 0 SET @Status = @Return

    SET @Task = '   SELECT I.Match, I.Rows, T.*'

              + '     FROM ' + @Bank + ' AS T'

              + '     JOIN ' + @DBAE + ' AS I'

              + '       ON ' + @DBAU

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

              + ' ORDER BY I.Match, I.Rows, T.' + @Tame

    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) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE

EXECUTE (@Task)

SET NOCOUNT OFF

RETURN (@Status)

GO
CREATE PROCEDURE dbo.sp_ChangeParentValue
    @DBTable varchar(100),
    @DBValue int,
    @DBAdmin 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 @Same varchar(100)
DECLARE @Tame varchar(100)
DECLARE @Came varchar(100)

SET @Same = @TPre + @DBTable

SELECT @Tame = C.name
  FROM sysobjects AS O
  JOIN syscolumns AS C
    ON O.id = C.id
 WHERE O.name = @Same
   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(@Same)
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 = 'UPDATE ' + @Name + ' SET '

              + @Tame + ' = ' + CONVERT(varchar(10),@DBAdmin)

              + ' WHERE '

              + @Tame + ' = ' + 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

SET NOCOUNT OFF

RETURN (@Status)

GO