Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /****** 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement