sql search all tables for attributes

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

showing results for - "sql search all tables for attributes"
Mariangel
02 Sep 2020
1SELECT
2  sys.columns.name AS ColumnName,
3  tables.name AS TableName
4FROM
5  sys.columns
6JOIN sys.tables ON
7  sys.columns.object_id = tables.object_id
8WHERE
9  sys.columns.name = 'ColumnName'
Ethan
15 Sep 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
68
queries leading to this page
sql search string all tablessql server search value in all tables value intsql find all value in listsql server find value in all tablessearch data table for a value sql serversearch in all tables for a column sql serversql query find value in all tablessql server search all tables for valuesearch for value across all tables in sqlmssql search all tables for valuefind a table in all sql server databasessearch a value in all tables sqlcan we find any value in all tables in sql server databasehow to search for a table in sql how to search tables in sqlsql server search in all tablesmssql search value in all tablesfind value in all tables sqlsearch tables for values in column sqlsearch whole table for value in sqlsql query to search for a string in all tablessearch for value in a table in table sqlsql find all instances of a artibutesearch a value in all tables sql serversql server find table in all databasessql search field value in all tablessearch a value from all tables where sql serverfind something in all table sqlsql search in all tablessql search all tables in db for stringsql search all tablessql find value in all tablessql server search text in all tablessql find in all tablessearch a value in all tables in sql serversql server find tables in databasehow to find a value in a table sqlsearch all tables of a value in sqlsql find all tables with a particular valuesql find all columns with a particular valuesql search for a value in all tablessql server search table in all databasessearch value in all database tablesfind value in all field sqlsql search entire database for valuesearch sql table for valuet sql how to search data in all tableshow to search all tables in a database for a valuesql search all tables for valuehow to search value in table sqlsearch sql tables for valuesearch sql database table for valuehow to search through all tables in sqlto sql search tables rows for a valuesearch all tables in sql for a valuesql search for value in all tablessearch all tables in a database for a valuefind a column value in all of the databasesearch for column value in all tables sqlsql find all values with string in itsearch a table for a value sqlsearch all tables for value sqlsearch all tables for valuesql search in table and return itsql query to find tables with a specific value in any columnsearch field in all tables sqlsql query to find tables with a specific value in a columnsql search value from all tablessearch value in all tables sqlsql search for a value in the tablesql search tables for columncopyright c2 a9 2002 narayana vyas kondreddi all rights reserved sql server search for value in all tablessql search in tableshow to search all tables in a database for a value sql servercheck value all tables in sqlfind value from sql tablesql search table for valuesql search string in all tablessql server search tables for valuesearch all sql tables for a valuehow to search for a column in all tables in sqlsearch all tables in database for valuesearch for a value in all tables sqlsearch all database tables for a valuesearch all tables in sqlsearch for a table in sql servercheck a value is used in entire databaseget all tables in sqlserver databasehow to search all tables in sqlsearch table in all database sql serversql select in all tables contains valuesql server how to find a value in all tablessearch table on sql servert sql search tables for valuesearch value in table sqlsql search all tables for attributessql search in all tables sql serversql query search all tables for valuesearch all tables for string sqlsearch in all tables sqlsql search for a field in all tablessql search all tables for stringcheck if a field has certain value sqlmoddify table sqlsearch whole table for value sqlsearch tables in db for valuesql search all tables for attributes