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

qlckcl4x 于 2023-05-21 发布在 SQL Server

关注(0)|答案(1)|浏览(120)

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.

sql-server

来源：https://stackoverflow.com/questions/76262772/how-to-find-the-column-of-a-specific-value-in-a-table-sql-server

1条答案

按热度按时间

nlejzf6q1#

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

赞(0）回复(0）举报 2023-05-21

我来回答

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

1条答案

相关问题

热门标签

最新问答