CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
satyajit
satyajit

Reputation: 2700

Searching for particular value of a column

I want to query for a particular value lets say "AYX" in some particular column of some particular table in a database.I need to get the list of tables and columns basically having value as "AYX"..How do I go for it?Is it possible? I am using SQL SERVER 2008

Upvotes: 1

Views: 148

Answers (3)

EricZ
EricZ

Reputation: 6205

@Aaron Bertrand had a very nice script.

I just want to point out that there is a free tool SSMS Tools Pack can search data in all table/views.

SSMS Tools Pack

Upvotes: 1

anon
anon

Reputation: 

DECLARE @string NVARCHAR(32) = N'AYX';


CREATE TABLE #results
(
    [column] NVARCHAR(768), 
    [value] NVARCHAR(MAX)
);

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'SELECT ''' 
    +  QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + '.' + QUOTENAME(name) + ''', ' + QUOTENAME(name) + ' FROM ' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + ' WHERE ' + QUOTENAME(name) + ' LIKE ''%' + @string + '%'';
    '
FROM sys.columns 
WHERE system_type_id IN (35, 99, 167, 175, 231, 239)
AND OBJECTPROPERTY([object_id], 'IsMsShipped') = 0;

INSERT #results EXEC sp_executesql @sql;

SELECT [column],[value] FROM #results;

DROP TABLE #results;

Upvotes: 3

Cade Roux
Cade Roux

Reputation: 89661

You'll need to use dynamic/code generated query.

Have a look at SELECT * FROM INFORMATION_SCHEMA.COLUMNS to get your list of columns in the database.

Restrict to appropriate datatypes with a WHERE clause on that table/view.

Code generate the queries to do the search: SELECT '{TABLE_NAME}' AS TABLE_NAME, '{COLUMN_NAME}' AS COLUMN_NAME, COUNT(*) AS ROW_COUNT FROM {TABLE_NAME} WHERE {COLUMN_NAME} LIKE '%{SEARCH}%'

UNION ALL the resulting queries together (add WHERE ROW_COUNT <> 0 to an outer query)

Upvotes: 0

Related Questions