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