search a string in a table without knowing the column

Question

Note: this is NOT asking

1. how to select a string where the column name is known.
2. how to select a string in ALL tables (all google results relate to this one)

---

This is asking search in only ONE table.

SQL returns error info conversion failed when converting the nvarchar value S3N2V5.

I want to locate the column name where S3N2V5 exists.

No manual methods please. There are 1000000 columns.

---

Input S3N2V5

Output columnname1ofthistable

Answer 1

Assuming I understand the question, here is one way to get a list of all columns from a single table that contain the search value, using CASE:

Create and populate sample table (Please save us this step in your future questions)

CREATE TABLE T
(
    COL1 char(3),
    COL2 char(3),
    COL3 char(3),
    COL4 int
)

INSERT INTO T VALUES
('abc', 'def', 'nop', 1),
('klm', 'nop', 'qrs', 2),
('tuv', 'wzy', 'zab', 3)

Build your dynamic sql:

DECLARE @Search nvarchar(5) = 'nop'

DECLARE @SQL nvarchar(max) = 'SELECT CASE @Search'

SELECT @SQL = @SQL +' WHEN '+ COLUMN_NAME + ' THEN '''+ COLUMN_NAME +''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T'
AND LOWER(DATA_TYPE) LIKE '%char%' -- only search char, varchar, nchar and nvarchar columns

SELECT @SQL = 'SELECT ColumnName FROM (' + 
               @SQL + ' END As ColumnName FROM T) x WHERE ColumnName IS NOT NULL'

Execute: (Note that using sp_executeSQL is SQL Injection safe, since we do not concatenate the search parameter into the query, but using it as a parameter)

EXEC sp_executeSQL @SQL, N'@Search nvarchar(5)', @Search

Results:

ColumnName
COL3
COL2

Answer 2

DECLARE @MyValue NVarChar(4000) = 'searchstring';

SELECT S.name SchemaName, T.name TableName
INTO #T
FROM sys.schemas S INNER JOIN
     sys.tables T ON S.schema_id = T.schema_id;

WHILE (EXISTS (SELECT * FROM #T)) BEGIN
  DECLARE @SQL NVarChar(4000) = 'SELECT * FROM $$TableName WHERE (0 = 1) ';
  DECLARE @TableName NVarChar(1000) = (
    SELECT TOP 1 SchemaName + '.' + TableName FROM #T
  );
  SELECT @SQL = REPLACE(@SQL, '$$TableName', @TableName);

  DECLARE @Cols NVarChar(4000) = '';

  SELECT
    @Cols = COALESCE(@Cols + 'OR CONVERT(NVarChar(4000), ', '') + C.name + ') = CONVERT(NVarChar(4000), ''$$MyValue'') '
  FROM sys.columns C
  WHERE C.object_id = OBJECT_ID(@TableName);

  SELECT @Cols = REPLACE(@Cols, '$$MyValue', @MyValue);
  SELECT @SQL = @SQL + @Cols;
  select  substring(@SQL,charindex('.',@SQL)+1,charindex('(',@SQL)-charindex('.',@SQL)-8) as 'TableName'
  EXECUTE(@SQL);

  DELETE FROM #T
  WHERE SchemaName + '.' + TableName = @TableName;
END;

DROP TABLE #T;

This will give you table Name and the entire row from the table which contains the searchstring.

Answer 3

Apart from anwswers mentioned in post : Older Post

1) (using column name) SELECT table_name,table_schema FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name='sort_method';

I hope better you can take dump ( in.sql format ) and you can easily search the content using IDEs like N++.