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

Stored Procedure: sp_FindString

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


USE master
GO
CREATE PROCEDURE dbo.sp_FindString
    @PCAdmin varchar(2000),
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBAdmin int = 0,
    @DBUltra bit = 0,
    @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 @Rich varchar(2000)

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

DECLARE @PKey int
DECLARE @ZKey int
DECLARE @FKey int

DECLARE @Rank smallint
DECLARE @Kind varchar(20)
DECLARE @Mask bit
DECLARE @Bond bit

DECLARE @Work int

CREATE TABLE #DBAH (FKey int, Rank smallint)

CREATE TABLE #DBAY (FKey int, Rank smallint)

SET @Work = CASE WHEN ISNULL(@DBAdmin,0) < 2 THEN 2 ELSE @DBAdmin END - 1

SET @Rich = REPLACE(@PCAdmin,CHAR(39),CHAR(39)+CHAR(39))
SET @Rich = REPLACE(@Rich   ,CHAR(45)+CHAR(45),CHAR(32))
SET @Rich = REPLACE(@Rich   ,CHAR(47)+CHAR(42),CHAR(32))

INSERT #DBAY
SELECT O.id, C.colid
  FROM sysobjects AS O
  JOIN syscolumns AS C
    ON O.id = C.id
  JOIN systypes AS T
    ON C.xusertype = T.xusertype
 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 T.name     LIKE '%char'
   AND C.length > @Work
   AND (C.isnullable = 0 OR @DBUltra = 0)
   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

DECLARE Search CURSOR FAST_FORWARD FOR
 SELECT O.name
      , C.name
      , Z.FKey
      , Z.Rank
   FROM sysobjects AS O
   JOIN syscolumns AS C
     ON O.id = C.id
   JOIN #DBAY AS Z
     ON O.id    = Z.FKey
    AND C.colid = Z.Rank

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

OPEN Search

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

FETCH NEXT FROM Search INTO @Name, @Came, @FKey, @Rank

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

WHILE @@FETCH_STATUS = 0 AND @Status = 0

    BEGIN

    SET @Task = ' INSERT #DBAH (FKey, Rank)'
              + ' SELECT ' + CONVERT(varchar(10),@FKey)
              + '      , ' + CONVERT(varchar(10),@Rank)
              + '   FROM ' + @Name
              + '  WHERE ' + @Came + ' LIKE ' + CHAR(39) + '%' + @Rich + '%' + CHAR(39) + ' AND 0 = 0'

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

    IF @Status = 0 SET @Status = @Return

    FETCH NEXT FROM Search INTO @Name, @Came, @FKey, @Rank

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

    END

  SELECT CASE WHEN @PCUltra = 0 THEN SUBSTRING(O.name,@TDo4,100) ELSE O.name END AS TName, C.name AS CName, COUNT(*) AS Rows
    FROM sysobjects AS O
    JOIN syscolumns AS C
      ON O.id = C.id
    JOIN #DBAH AS Z
      ON O.id    = Z.FKey
     AND C.colid = Z.Rank
GROUP BY O.name, C.name
ORDER BY O.name, C.name

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

DROP TABLE #DBAH

DROP TABLE #DBAY

SET NOCOUNT OFF

RETURN (@Status)

GO

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