Pasted

/****** Object:  StoredProcedure [dbo].[p_UTIL_FindFields]    Script Date: 26-Jun-14 12:16:05 ******/
/* Written by Lars Fosdal, http://plus.lars.fosdal.com */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[p_UTIL_FindFields]
(   @Name  NVARCHAR(255),
    @Value NVARCHAR(255),
    @Verbose BIT = 0
)
AS
BEGIN
 
    PRINT N'[dbo].[p_UTIL_FindFields] @Name = N''' + @NAME + ''', @Value = N''' + @Value + ''''
    Print 'Finding Fields with names like ' + @Name + ' where value = ' + @Value
 
    DECLARE @iCount int
    DECLARE @iTables int = 0
    DECLARE @iErrors int = 0
    DECLARE @sSQL NVARCHAR(MAX)
    DECLARE @sSQLParams NVARCHAR(255)
    DECLARE @sColName NVARCHAR(255)
    DECLARE @sTableName NVARCHAR(255)
 
    DECLARE sTableList CURSOR FOR
       SELECT c.name AS ColName, t.name AS TableName
       FROM sys.columns c
          JOIN sys.tables t ON c.object_id = t.object_id
       WHERE c.name LIKE @Name
 
    OPEN sTableList
 
    FETCH NEXT FROM sTableList
    INTO @sColName, @sTableName
    WHILE @@FETCH_STATUS = 0 
    BEGIN       
    IF @Verbose = 1 PRINT @sTableName + '.' + @sColName
 
    SET @sSQL = 'select @countOut = count(*) from ' + @sTableName + ' where ' + @sColName + ' =  ' + @Value;
    SET @sSQLParams = N'@countOut int OUTPUT';
       
    BEGIN TRY
          SET @iTables = @iTables + 1
      EXECUTE sp_executesql @sSQL, @sSQLParams, @countOut = @iCount OUTPUT;
          IF @iCount > 0 PRINT @sSQL + ' returns ' + Convert(nvarchar(50), @iCount) 
        END TRY
        BEGIN CATCH
      IF @Verbose = 1 PRINT N'Not testable'
          SET @iErrors = @iErrors + 1
    END CATCH 
       
        FETCH NEXT FROM sTableList
       INTO @sColName, @sTableName
    END
    PRINT N'Checked ' + Convert(nvarchar(50), @iTables) + ' table and column combos with ' + Convert(nvarchar(50), @iErrors) + ' errors'
 
    CLOSE sTableList
    DEALLOCATE sTableList
 
END