Toad World® Forums

Ability to search for data in a database

Please add the ability to search for data values in a SQL Database.

I had to download another tool just to do this.

Just curious, what did you use?

The free tool from ApexSQL

Why not use the power of SQL instead?

stackoverflow.com/.../how-do-i-find-a-value-anywhere-in-a-sql-server-database

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

DECLARE @Results TABLE(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

I have done something like that in the past, but the add-on I found for SSMS operates a lot faster, easier to use (it's an add-in) and does not require me to alter the database I am searching, and its free.