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