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