Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Search for one value in any column of any table inside a database

Tags:

tsql

Is there a way to search for one value (in my case it is a UID of the type char(64)) inside any column of any table inside one MS SQL Server database?

I'm sitting in front of a huge database without any idea how the tables had to be linked together. To find that out I'd like to list all tables and there columns that contain a certain value in any row. Is that possible?

One way could be to just dump the entire database into a text file and than use any text-editor to search for the value - but this would be pure pain if the database is too huge.

like image 761
Martin Avatar asked Apr 02 '09 10:04

Martin


People also ask

How do I find the value of a column in a database?

Use sys. For example, if we want to search only for the user-defined table, we use 'U' value for the type column. I filter records for the useful columns. You can get all columns using the select * statement. Similarly, we use the value 'P' for the stored procedure.

How do I search for a specific data in a SQL database?

SQL Server Management Studio Object Explorer browse to the database you want to search through. write the name (full or partial) of the database object in the Search text box. press Enter to start the search process.


1 Answers

How to search all columns of all tables in a database for a keyword?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

EDIT: Here's the actual T-SQL, in case of link rot:

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN  -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0 and SQL Server 2000 -- Date modified: 28th July 2002 22:50 GMT   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  SET NOCOUNT ON  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  WHILE @TableName IS NOT NULL BEGIN     SET @ColumnName = ''     SET @TableName =      (         SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))         FROM    INFORMATION_SCHEMA.TABLES         WHERE       TABLE_TYPE = 'BASE TABLE'             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName             AND OBJECTPROPERTY(                     OBJECT_ID(                         QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)                          ), 'IsMSShipped'                            ) = 0     )      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  END  SELECT ColumnName, ColumnValue FROM #Results  END 
like image 114
REA_ANDREW Avatar answered Sep 20 '22 17:09

REA_ANDREW