SQL Server: Find first column with specified value
Returns first column of any table which contains a search value
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Josep Coves Barreiro -- Create date: 10/02/2015 -- Description: Returns the first column of the table which contains a search value -- ============================================= BEGIN DECLARE @v_count int DECLARE @v_table varchar(200) DECLARE @sql nvarchar(500) DECLARE @ParmDefinition nvarchar(500) DECLARE @i_column nvarchar(500) DECLARE @p_table nvarchar(2000) DECLARE @p_text_search nvarchar(2000) declare table_columns cursor for select c.name from sys.columns c inner join sys.tables t on c.object_id = t.object_id where t.name = @p_table OPEN table_columns FETCH NEXT FROM table_columns INTO @i_column while @@FETCH_STATUS = 0 BEGIN SET @ParmDefinition = '@vcount int OUTPUT' set @sql = 'select @vcount =COUNT(1) from '+@p_table+' where '+@i_column+' = '''+@p_text_search+'''' EXECUTE sp_executeSQL @sql,@ParmDefinition, @vcount=@v_count OUTPUT IF (@v_count > 0) begin CLOSE table_columns DEALLOCATE table_columns PRINT @i_column BREAK end FETCH NEXT FROM table_columns into @i_column END CLOSE table_columns DEALLOCATE table_columns END GO |
