CODEWITHSUNDEEP
sql-serversql-server-2008
Jill Clover
Jill Clover

Reputation: 2318

search a string in a table without knowing the column

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

Upvotes: 1

Views: 2899

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

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

Upvotes: 1

ban
ban

Reputation: 71

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++.

Upvotes: 0

Rajesh Bhat
Rajesh Bhat

Reputation: 811

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.

Upvotes: 0

Related Questions