How to find the column of a specific value in a table (SQL Server)

Question

I have a table with more than 10,000 records. When executing the query, I get an error message

Msg 245, Level 16, State 1, Line 10
Conversion failed when converting the varchar value 'balance' to data type int

The problem is I don't know which column has the value 'balance'.

And I have many, many columns. What can I do?

SELECT 
    CONCAT(' SELECT * FROM [DM].[X]  WHERE ''balance'' IN (',
           STRING_AGG(COLUMN_NAME, ','), ')')
FROM INFORMATION_SCHEMA.columns 
WHERE table_schema = 'DM' 
  AND table_name = 'X'
  AND DATA_TYPE IN ('int', 'nvarchar', 'varchar');

Then I'll get a column which I copy and paste it to SQL, then I run it But it does not work.

Is there a simple way to detect the column of a single value? If not, a more difficult way may also be okay.

I'm not sure why you wouldn't be able to derive this info from the query that gives you the error. Don't you have the query text? Can you show us? — Limonka, May 16 '23 at 12:03
if you think about what your query is doing, you should be able to figure it out. the final query looks like: select * from x where 'balance' in (colvarchar, colnvarchar, colint) — siggemannen, May 16 '23 at 12:07
and DATA_TYPE IN ('int','nvarchar','varchar'); -- you probably want to remove the 'int' part here — siggemannen, May 16 '23 at 12:08

score 0 · Answer 1 · answered May 16 '23 at 17:25

you can use this is query to find string value in all columns with sql dynamic query(use equal)

  DECLARE   @stringToFind VARCHAR(100)='balance',
   @schema sysname='DM', 
   @table sysname ='X'
   DECLARE @sqlCommand varchar(max) = '' 
       
   SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] = ''' + @stringToFind + ''' OR '
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_SCHEMA = @schema
   AND TABLE_NAME = @table 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')

   if(@sqlCommand<>'')
   BEGIN
     SET @sqlCommand= 'SELECT * FROM [' + @schema + '].[' + @table + '] 
WHERE '+@sqlCommand

     SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3)
   END

   
   EXEC (@sqlCommand)
   PRINT @sqlCommand

How to find the column of a specific value in a table (SQL Server)

1 Answers1