How to get sql table column names based on column field value

There is no easy way to retrieve a list of columns from a particular SQL Server table based on the column values. The only way is to create a user-defined function or a stored procedure and then retrieve its results.

Here is an example of stored procedure that returns a comma separated list of table columns that contain a particular value.

CREATE PROCEDURE [dbo].[usp_FindColumnsContainingTheValue](@table_name nvarchar(50), @val nvarchar(50))
AS
BEGIN
DECLARE @column_name nvarchar(50)
DECLARE @column_list nvarchar(1000)
DECLARE @count int
DECLARE @sql nvarchar(1000)

DECLARE my_cursor CURSOR FOR
SELECT column_name 
FROM information_schema.columns
WHERE table_name=@table_name
AND data_type IN('nvarchar','varchar', 'ntext', 'nchar')

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @column_name

SET @column_list=''

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=N'SELECT @countOUT=COUNT(*) FROM ' + @table_name + ' WHERE ' + @column_name + '=' + '''' + @val + ''''

EXEC sp_executesql 
        @query = @sql, 
        @params = N'@countOUT INT OUTPUT', 
        @countOUT = @count OUTPUT

 IF @count > 0 
 BEGIN
  IF LEN(@column_list) > 0
  BEGIN
   SET @column_list=@column_list + ',' + @column_name
  END
  ELSE
  BEGIN
   SET @column_list= @column_name
  END


 END
     FETCH NEXT FROM my_cursor
     INTO @column_name
END

CLOSE my_cursor
DEALLOCATE my_cursor
SELECT @column_list

END

As you can see, the stored procedure I wrote takes two parameters - table name and a value.

Now, as a way to illustrate on how this stored procedure can be used, I will take Northwind database table Orders and will use the above procedure to retrieve all the columns that contain value 'Brazil'.

EXECUTE dbo.usp_FindColumnsContainingTheValue 'Orders', 'Brazil'

Will return a single value 'ShipCountry', for it's the only column that contains a value 'Brazil' in one of the rows.