LarsFosdal

SQL Server Finding fields with a specific value in any table

Jun 26th, 2014
260
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /****** Object:  StoredProcedure [dbo].[p_UTIL_FindFields]    Script Date: 26-Jun-14 12:16:05 ******/
  2. /* Written by Lars Fosdal, http://plus.lars.fosdal.com */
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE PROCEDURE [dbo].[p_UTIL_FindFields]
  8. (   @Name  NVARCHAR(255),
  9.     @Value NVARCHAR(255),
  10.     @Verbose BIT = 0
  11. )
  12. AS
  13. BEGIN
  14.  
  15.     PRINT N'[dbo].[p_UTIL_FindFields] @Name = N''' + @NAME + ''', @Value = N''' + @Value + ''''
  16.     Print 'Finding Fields with names like ' + @Name + ' where value = ' + @Value
  17.  
  18.     DECLARE @iCount int
  19.     DECLARE @iTables int = 0
  20.     DECLARE @iErrors int = 0
  21.     DECLARE @sSQL NVARCHAR(MAX)
  22.     DECLARE @sSQLParams NVARCHAR(255)
  23.     DECLARE @sColName NVARCHAR(255)
  24.     DECLARE @sTableName NVARCHAR(255)
  25.  
  26.     DECLARE sTableList CURSOR FOR
  27.        SELECT c.name AS ColName, t.name AS TableName
  28.        FROM sys.columns c
  29.           JOIN sys.tables t ON c.object_id = t.object_id
  30.        WHERE c.name LIKE @Name
  31.  
  32.     OPEN sTableList
  33.  
  34.     FETCH NEXT FROM sTableList
  35.     INTO @sColName, @sTableName
  36.     WHILE @@FETCH_STATUS = 0
  37.     BEGIN      
  38.     IF @Verbose = 1 PRINT @sTableName + '.' + @sColName
  39.  
  40.     SET @sSQL = 'select @countOut = count(*) from ' + @sTableName + ' where ' + @sColName + ' =  ' + @Value;
  41.     SET @sSQLParams = N'@countOut int OUTPUT';
  42.        
  43.     BEGIN TRY
  44.           SET @iTables = @iTables + 1
  45.       EXECUTE sp_executesql @sSQL, @sSQLParams, @countOut = @iCount OUTPUT;
  46.           IF @iCount > 0 PRINT @sSQL + ' returns ' + Convert(nvarchar(50), @iCount)
  47.         END TRY
  48.         BEGIN CATCH
  49.       IF @Verbose = 1 PRINT N'Not testable'
  50.           SET @iErrors = @iErrors + 1
  51.     END CATCH
  52.        
  53.         FETCH NEXT FROM sTableList
  54.        INTO @sColName, @sTableName
  55.     END
  56.     PRINT N'Checked ' + Convert(nvarchar(50), @iTables) + ' table and column combos with ' + Convert(nvarchar(50), @iErrors) + ' errors'
  57.  
  58.     CLOSE sTableList
  59.     DEALLOCATE sTableList
  60.  
  61. END
RAW Paste Data