search all sql tables for a value

Solutions on MaxInterview for search all sql tables for a value by the best coders in the world

showing results for - "search all sql tables for a value"
Juan José
27 Feb 2019
1DECLARE @SearchStr nvarchar(100)
2SET @SearchStr = '## YOUR STRING HERE ##'
3 
4 
5-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
6-- Purpose: To search all columns of all tables for a given search string
7-- Written by: Narayana Vyas Kondreddi
8-- Site: http://vyaskn.tripod.com
9-- Updated and tested by Tim Gaunt
10-- http://www.thesitedoctor.co.uk
11-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
12-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
13-- Date modified: 03rd March 2011 19:00 GMT
14CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
15 
16SET NOCOUNT ON
17 
18DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
19SET  @TableName = ''
20SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
21 
22WHILE @TableName IS NOT NULL
23 
24BEGIN
25    SET @ColumnName = ''
26    SET @TableName = 
27    (
28        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
29        FROM     INFORMATION_SCHEMA.TABLES
30        WHERE         TABLE_TYPE = 'BASE TABLE'
31            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
32            AND    OBJECTPROPERTY(
33                    OBJECT_ID(
34                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
35                         ), 'IsMSShipped'
36                           ) = 0
37    )
38 
39    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
40         
41    BEGIN
42        SET @ColumnName =
43        (
44            SELECT MIN(QUOTENAME(COLUMN_NAME))
45            FROM     INFORMATION_SCHEMA.COLUMNS
46            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
47                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
48                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
49                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
50        )
51 
52        IF @ColumnName IS NOT NULL
53         
54        BEGIN
55            INSERT INTO #Results
56            EXEC
57            (
58                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
59                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60            )
61        END
62    END   
63END
64 
65SELECT ColumnName, ColumnValue FROM #Results
66 
67DROP TABLE #Results
Elena
02 Oct 2018
1DECLARE @SearchStr nvarchar(100)
2SET @SearchStr = '## YOUR STRING HERE ##'
3 
4 
5-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
6-- Purpose: To search all columns of all tables for a given search string
7-- Written by: Narayana Vyas Kondreddi
8-- Site: http://vyaskn.tripod.com
9-- Updated and tested by Tim Gaunt
10-- http://www.thesitedoctor.co.uk
11-- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
12-- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
13-- Date modified: 03rd March 2011 19:00 GMT
14CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
15 
16SET NOCOUNT ON
17 
18DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
19SET  @TableName = ''
20SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
21 
22WHILE @TableName IS NOT NULL
23 
24BEGIN
25    SET @ColumnName = ''
26    SET @TableName = 
27    (
28        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
29        FROM     INFORMATION_SCHEMA.TABLES
30        WHERE         TABLE_TYPE = 'BASE TABLE'
31            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
32            AND    OBJECTPROPERTY(
33                    OBJECT_ID(
34                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
35                         ), 'IsMSShipped'
36                           ) = 0
37    )
38 
39    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
40         
41    BEGIN
42        SET @ColumnName =
43        (
44            SELECT MIN(QUOTENAME(COLUMN_NAME))
45            FROM     INFORMATION_SCHEMA.COLUMNS
46            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
47                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
48                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
49                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
50        )
51 
52        IF @ColumnName IS NOT NULL
53         
54        BEGIN
55            INSERT INTO #Results
56            EXEC
57            (
58                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
59                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
60            )
61        END
62    END   
63END
64 
65SELECT ColumnName, ColumnValue FROM #Results
66 
67DROP TABLE #Results
68
queries leading to this page
check a value is used in entire databasesearch for a specific row in all the tables in a databasesql search value from all tablest sql how to search data in all tablessearch all tables all columns sqlselect data from all tables that contain value sqlhow to find specific value in sql databasehow to find which column value belongs to sql serversearch value in all tables sqlsearch in all tables sqlfind a column value in all of the databasesql server check for a column value in all tableswhat are indexes in database tablessql find all instances of a artibutesql server search table in all databasessql find all columns with a particular valuessms find value in tablesearch all tables and fields for valuehow to search through all tables in sqlsql query to find tables with a specific value in any columnsql search all tables for attributesget value matching tables for all tables sql querysearch all tables in sql for a valuesearch for value in a table in table sqlsql find tables with valuemssql search all tables for valuesearch in all tables for a column sql serversearch table in all database sql serversql server search all tables for valuesearch sql tables for valuesql search every column in every table for a stringsearch value in all tables sql serversql server search for value in all tableshow to search all columns of all tables in a database for a keyword sql serverhow to find a particular value in sql databasesearch string and stored data in sql table sql query to search all tables for a valuefind string in sql databasesql server search in all tablessql search all tables for valuesql search whole table for valuesql query to find values in a specific table columnsearch sql db for tables that contain valuesql search field value in all tableshow to search all tables in a database for a value sql servermssql search database tables for valuesql server search text in all tablessearch a value in all tables sqlsearch entire database for string sql serverhow to get all specific value from table in sqlcheck value all tables in sqlget all tables in sqlserver databasehow to search tables in sqlsql store a value from a table searchsql server how to find a value in all tablessearch for data in columns in database sql serverquery to search all tables in databasesql server search value in all tablessql search for values in columnfind column contents in database sqlsql search in tablessearch sql table for valuems sql find a value in all columns from tablehow to find a value in a table sqlsql get all columns where table has valuesearch field value in all columns in 1 table query in sqlsql find in all tablessql server find value in all tablessearch table with column name and specific value in sqlsearch field in all tables sqlhow to search all tables in sqlget sql data on all tables that matches a part of the vlauehow to search for a column in all tables in sqlsearch keyword in entire sql tablesql search tables for columnhow to set defalut value null in mysql for all tablesearch for value in all tables sqlto sql search tables rows for a valuesql search in all tablessql server search values in all columnssearch all tables for value sqlsearch all tables for valuesql search all tables for stringsql query to find tables with a specific value in a columnselect a value in all databasesearch which tables have a certain value sqlsql search all tables in db for stringms sql search value in all tablesfind something in all table sqlsql search in table and return itdynamodb get all values in a table nodecount from table in sql and selecthow to find table and column name where value is presentsql scan all tables for valueupdate values in a search table sql serversearch all database tables for a valuefind value in all tables sqlsearch table on sql serversql find all tables with a particular valuesearch all tables for string sqlsearch a value in all tables in sql serverfind value from sql tablems sql find value in all tablessql server find table in all databasessql server search all tablessearch data table for a value sql serversql search for a field in all tablesfind all tables that have a certain column sqlsearch all columns and all tables for valuehow to search all tables in a database for a valuesql server script find all columns with datahow to copy from one table to other in ms sql by select statementc 23 search all tableshow to search for a table in sql find specific value in sql columnsql find all values with string in itselect value from all tables in databasefind value in whole database sql serversearch for item in all tables sql serversearch select html between two dates php sqlsearch specific value in table all columnssearch value in table sqlsql server all table searchsearch whole table for value in sqlsearch all tables in sqlfind element in column sqlsql find a value in all tablessql query to search all tables search all tables in database for valuecan we find any value in all tables in sql server databasesearch for a value in all tables sqlcheck which tables have a certain value sqlsql query look string through all tablesql query search for value in columnsql find value in tablessql select in all tables contains valuesql find value in all tablessearch filed value in all columns in 1 tablefind a value in all tablessearch tables for values in column sqlsql query 2c find a value in a table 2c all columns 2c syntaxsearch for a table in sql serverhow to search data in tables in sqlcopyright c2 a9 2002 narayana vyas kondreddi all rights reserved sql search string all tablessearch entire table for value sqlhow to create a search query that looks at all tables and fields sql serverfind a table in all sql server databasessql search in all tables sql serversql server find tables in databasehow to check entire database has this column valuesearch sql database table for valuesearch text in column sql database sql serverhow to find a particular value from sql tablesearch a table for a value sqlfind location with specific value in sql serversearch tables in db for valuecheck if a field has certain value sqlmoddify table sqlsql search for a value in the tablefind specific value in all tables sql servert sql search tables for valuesearch for value in sql databasesearch all sql tables for a valuefind value in a any table sql serversql query find everysearch value in all database tableshow to find table with specific value in sql serversql search for value in all tablessql query find value in all tablessearch from text in all column in all db sql serversql all value from all tablessql query to search for a string in all tablessql find value in column in all databaseshow to search all tables in sql database for a valuefind a value in all tables sql serversql search entire database for valuemssql search value in all tableshow to search all tables and fields for value sql serversearch whole table for value sqlsql command search all tablessql search for text in all tablessql search string in all tablesfind value in all field sqlsearch all tables in a database for a valuehow to find value in all tables sqlquery to search a column value in databasesql server search value in all tables value intsql search for a value in all tablessql search table for column valuesearch for column value in all tables sqlsql select all tables change data from 1 specific columnhow to search value in table sqlsql find all value in listcheck value in all tablesql find table that contains this stringfind value in sql database all tablessearch all tables of a value in sqlsql search table for valuesql server search tables for valuesearch a value in all tables sql serversql query search all tables for valuesql search all tablessql search all columns for valuesql search for value in tableget a value from all tables in sqlsearch a value from all tables where sql serversearch for value across all tables in sqlhow to search a specific value in all tablessql query search with complete table for stringsearch all sql tables for a value