Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Find a Value among multiple tables

Hi,

I want to know if we can write a query to find a particular value in any of the tables in database. Say my DB has five tables each with different fields. Can i find a value "xyz" from these tables. Even getting table name would be useful.

Thanks,
Pankaj
0
Pankaj27
Asked:
Pankaj27
2 Solutions
 
Kevin3NFCommented:
Get table names:

Select [Name] from sysobjects where type = 'u'

try this for searching all over theplace:

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

I've used it with success in the past

0
 
obahatCommented:
This script will populate all tables and their columns into the temp table #tbl


SET NOCOUNT ON

DECLARE @CurrTableName SYSNAME
DECLARE @CurrColumnName SYSNAME

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
     DROP TABLE #tbl

SELECT a.TABLE_NAME AS TableName,
     a.COLUMN_NAME AS ColName
INTO #tbl
FROM INFORMATION_SCHEMA.COLUMNS a WITH (NOLOCK)
     INNER JOIN INFORMATION_SCHEMA.TABLES b WITH (NOLOCK)
     ON a.TABLE_NAME = b.TABLE_NAME
WHERE b.TABLE_TYPE = 'BASE TABLE'


For each entry in the #tbl table, you can the loop and query each underlying table. The problem is that the underlying columns may be of different types, which will make it a little more difficult to compare the data in those columns to the value that you are seeking. If for example, you are only interested in varchar/char/nvarchar/nchar columns, this task would be easier, and can be achieved by running the following script (which is inclusive of the above):


SET NOCOUNT ON

DECLARE @CurrTableName SYSNAME
DECLARE @CurrColumnName SYSNAME
DECLARE @DesiredValue VARCHAR(128)

SET @DesiredValue = 'image' -- or any other value

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
     DROP TABLE #tbl

IF OBJECT_ID('tempdb..#tblResults', 'U') IS NOT NULL
     DROP TABLE #tblResults

CREATE TABLE #tblResults (TableName SYSNAME, ColName SYSNAME, Value SQL_VARIANT)

SELECT a.TABLE_NAME AS TableName,
     a.COLUMN_NAME AS ColName
INTO #tbl
FROM INFORMATION_SCHEMA.COLUMNS a WITH (NOLOCK)
     INNER JOIN INFORMATION_SCHEMA.TABLES b WITH (NOLOCK)
     ON a.TABLE_NAME = b.TABLE_NAME
WHERE b.TABLE_TYPE = 'BASE TABLE'
     AND a.DATA_TYPE IN ('NVARCHAR', 'VARCHAR', 'NCHAR', 'CHAR')

SELECT TOP 1 @CurrTableName = TableName,
     @CurrColumnName = ColName
FROM #tbl WITH (NOLOCK)

WHILE @CurrTableName IS NOT NULL
BEGIN
      INSERT INTO #tblResults (TableName, ColName, Value)
      EXEC('     SELECT ''' + @CurrTableName + ''', ''' + @CurrColumnName + ''', '
      + '[' + @CurrColumnName + ']'
      + ' FROM ' + @CurrTableName
      + ' WHERE ' + @CurrColumnName + ' = ''' + @DesiredValue + '''')

     DELETE FROM #tbl
     WHERE TableName = @CurrTableName
          AND ColName = @CurrColumnName

     SELECT TOP 1 @CurrTableName = TableName,
          @CurrColumnName = ColName
     FROM #tbl WITH (NOLOCK)
END


SELECT * FROM #tblResults


Final comments:
1. The script may take quite some time to run, depending on the size of the DB, and such.
2. You can alter the query within the loop as you see fit.
3. The temp table #tblResults contains the table name, column name, and all rows that matched the search criteria.

Hope this helps.

Omri.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now