Search all table columns for value?

Question

I searched for the answer to this question, but all I can find are people who ask how to search all columns of ALL tables in the database for a value. I just want to search all columns for a specific table. The people who came up with all the tables are complex and hard to understand exactly where he is looking for a particular table. Can someone help me? Thanks

Answer 1

Just use a third-party tool. There are several that are 100% free, and you will not go wrong with any of them, because they will save you a lot of time.

ApexSQL search (search for both schema and data), SSMS Toolpack (search for schema and data, but not free for SQL Server 2012), SQL Search (data search only).

Honestly, I don’t really understand why even very experienced database administrators worry about writing scripts for this, if they can use some kind of tool that will do the job for free.

Answer 2

I have no idea about the column types or data values ​​you are looking for, but I would suggest that you are trying to find a substring of several columns of text.

This is a job for Full Text Search .

Do not waste time on LIKE '%' + @SearchStr + '%' . You have to write a lot of complex code to support it, and this solution will not work anyway.

Answer 3

In a similar question, I mentioned SQL Workbench / J.

A team that searches for a database can also be limited to only one table. Therefore, even if this question was specific to PostgreSQL, this tool also works for SQL Server, as far as I know.

Answer 4

I modified this saved proc to take the table name as the second parameter and just search for this table for the data:

 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SearchOneTable]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SearchOneTable] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[SearchOneTable] ( @SearchStr nvarchar(100) = 'A', @TableName nvarchar(256) = 'dbo.Alerts' ) AS BEGIN CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) --SET NOCOUNT ON DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') --SET @SearchStr2 = QUOTENAME(@SearchStr, '''') --exact match SET @ColumnName = ' ' WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) AND TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND QUOTENAME(COLUMN_NAME) > @ColumnName ) IF @ColumnName IS NOT NULL BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END SELECT ColumnName, ColumnValue FROM #Results END GO 

Answer 5

Here's a solution that, like the @ Decker97 approach, computes from metadata whose columns are text searchable. Assumes 2005+ for using XML PATH, as well as sys.columns, sys.tables, etc. Supports TEXT / NTEXT / CHAR / NCHAR / VARCHAR / NVARCHAR and even puts the leading N in the search bar where necessary. Does not support XML columns. What he does slightly different is that he returns a single set of results for each table, not for each individual column, so you only get one row in each row of the table, even if several columns match. If the goal is to understand how this works and not just have a solution, it will probably take a little more than that ... maybe I should write about this problem (I probably should not be lazy and actually create lists of columns instead of just using SELECT *).

 DECLARE @SearchTerm NVARCHAR(32) = 'foo'; DECLARE @TableName NVARCHAR(128) = NULL; SET NOCOUNT ON; DECLARE @s NVARCHAR(MAX) = ''; WITH [tables] AS ( SELECT [object_id] FROM sys.tables AS t WHERE (name = @TableName OR @TableName IS NULL) AND EXISTS ( SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id] AND system_type_id IN (35,99,167,175,231,239) ) ) SELECT @s = @s + 'SELECT ''' + REPLACE(QUOTENAME(OBJECT_SCHEMA_NAME([object_id])),'''','''''') + '.' + REPLACE(QUOTENAME(OBJECT_NAME([object_id])), '''','''''') + ''',* FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])) + ' WHERE ' + ( SELECT QUOTENAME(name) + ' LIKE ' + CASE WHEN system_type_id IN (99,231,239) THEN 'N' ELSE '' END + '''%' + @SearchTerm + '%'' OR ' FROM sys.columns WHERE [object_id] = [tables].[object_id] AND system_type_id IN (35,99,167,175,231,239) ORDER BY name FOR XML PATH(''), TYPE ).value('.[1]', 'nvarchar(max)') + CHAR(13) + CHAR(10) FROM [tables]; SELECT @s = REPLACE(@s,' OR ' + CHAR(13),';' + CHAR(13)); /* make sure you use Results to Text and adjust Tools / Options / Query Results / SQL Server / Results to Text / Maximum number of characters if you want a chance at trusting this output (the number of tables/columns will certainly have the ability to exceed the output limitation) */ SELECT @s; -- EXEC sp_executeSQL @s; 

Answer 6

This is similar to the fact that you just want to know which table and column are stored, and not that you want to know what your code is executing, or change it. I also had this problem and this solved it:

Download your database in SQL format (for example, using phpmyadmin), open it with a text editor and find the events you need.

Answer 7

A small workaround that requires a bit less copy-paste, since the command can be easily created using queries.

Invert the IN statement in the WHERE as VALUE IN <fields> (as opposed to the more common use case of FIELD IN <values> ).

 SELECT col_1, col_2, ... , col_n FROM <table> WHERE CAST(<value> AS varchar(max)) IN ( CAST(col_1 AS varchar(max)), CAST(col_2 AS varchar(max)), ..., CAST(col_n AS varchar(max)) ) 

Since varchar is a fairly flexible data type, it becomes quite reliable (you can throw ISNULL / NULLIF to change as needed) and, depending on the use case, it can possibly be used for more than one search value.

A more robust solution using dynamic execution and PL / SQL would be to write a procedure for dynamically building the representation of the target table (by reading, for example, the MySQL information_schema schema, Oracle SYS schema, etc.), Limited Where Offer containing the input string, hard-coded into a series of "OR" -concatenated / IN clauses for filtering conditions.

Answer 8

I encountered this problem, as a rule, after loading the data from the CSV file, where I had to modify the commas, "in the text fields, so that the data is loaded correctly and once in SQL Server, it becomes necessary to change the changed character back to a comma, and this useful for finding the whole table.Greg Robidou in mssqltips published a stored procedure that does just that, looking for the columns of the specified table for a specific String value.You can find it along with SPROC, which does not use the cursor and details here:

https://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

I placed the original SPROC below:

 USE master GO CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS DECLARE @sqlCommand VARCHAR(8000) DECLARE @where VARCHAR(8000) DECLARE @columnName sysname DECLARE @cursor VARCHAR(8000) BEGIN TRY SET @sqlCommand = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE' SET @where = '' SET @cursor = 'DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM ' + DB_NAME() + '.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ''' + @schema + ''' AND TABLE_NAME = ''' + @table + ''' AND DATA_TYPE IN (''char'',''nchar'',''ntext'',''nvarchar'',''text'',''varchar'')' EXEC (@cursor) OPEN col_cursor FETCH NEXT FROM col_cursor INTO @columnName WHILE @@FETCH_STATUS = 0 BEGIN IF @where <> '' SET @where = @where + ' OR' SET @where = @where + ' [' + @columnName + '] LIKE ''' + @stringToFind + '''' FETCH NEXT FROM col_cursor INTO @columnName END CLOSE col_cursor DEALLOCATE col_cursor SET @sqlCommand = @sqlCommand + @where PRINT @sqlCommand EXEC (@sqlCommand) END TRY BEGIN CATCH PRINT 'There was an error. Check to make sure object exists.' PRINT error_message() IF CURSOR_STATUS('variable', 'col_cursor') <> -3 BEGIN CLOSE col_cursor DEALLOCATE col_cursor END END CATCH 

Answer 9

I found the best answer is only for select * from the table, and then copy and paste into Excel and press Ctrl + F