I know the values stored in the column, but i want to retrieve the column name

1

Dear colleagues

Please assist me.
I have a table in SQL that have a lot of columns (1500).
I know the values that are stored in a certain column, how do i retrieve the column name by knowing the values that are stored in it.

2

Is this a one time thing or something that needs to run continuously?

Also what data type is the value you are looking for? String, boolean, date, int, float?

3

it is a one time thing, the data type is string.

4 
use Your_database
go

select 'select * from ' + t.name + ' where ' + c.name + ' = ''chicken'' union ' 
from sys.tables t join sys.columns c on t.object_id = c.object_id  
join sys.types tp on tp.system_type_id = c.system_type_id
where t.name = 'budgets' -->your 1500 column table name goes here
  and tp.name in (
			'text',
			'ntext',
			'varchar',
			'char',
			'nvarchar',
			'nchar'
  )

run this then copy the result of the query, remove the trailing union.

5

it is blank

6

show us what your query looks like

7

use DB
go

select t.name, c.name
from sys.tables t join sys.columns c on t.object_id = c.object_id
join sys.types tp on tp.system_type_id = c.system_type_id
where t.name = 'Table' -->your 1500 column table name goes here

8

which is exactly not what I provided you.
follow these instructions

9

What you provided me is blank when i use it as it is. It does not show where i must put my table and where i must put the word to be searched.

10

change the word budgets to the name of your 1500 column table and change the word chicken to the value you are looking for.

11

hi

there is this view
INFORMATION_SCHEMA.COLUMNS

select table_name,column_name
from
INFORMATION_SCHEMA.COLUMNS

in that a whole lot of details you can get
including table name column name

hope it helps
:slight_smile: :slight_smile:

https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/

12

maybe this will help