Re: find a value in database

From: John Gilson (jag_at_acm.org)
Date: 03/31/04 

Date: Wed, 31 Mar 2004 06:30:48 GMT

"joe" <pearl_77@hotmail.com> wrote in message news:OH$PyRnFEHA.2732@tk2msftngp13.phx.gbl...
> Hi, I have a bunch of tables in database. I need to find out which tables
> contain values "wsbook", is it possible or no?
>
> Note: it's not table names, "wsbook" is data from tables

Here's a UDF that will generate SQL code that, when executed, will
search each character string column of sufficient length in each table
of the database and return those column names that have an occurrence
of the indicated string.

CREATE FUNCTION GenerateCodeForStringColumnCheck
(@string VARCHAR(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @code VARCHAR(8000)
SET @code = ''
SELECT @code =
               @code +
               'SELECT ''"' + TABLE_SCHEMA + '"'' AS table_schema, ''"' +
               TABLE_NAME + '"'' AS table_name, ''"' +
               COLUMN_NAME + '"'' AS column_name' +
               ' WHERE EXISTS (SELECT * FROM "' +
               TABLE_SCHEMA + '"."' + TABLE_NAME + '" WHERE "' +
               COLUMN_NAME + '" = ''' + @string + ''') UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE CHARACTER_MAXIMUM_LENGTH >= LEN(@string)
RETURN(SUBSTRING(@code, 1, LEN(@code) - LEN('UNION ALL ')))
END

DECLARE @code VARCHAR(8000)
DECLARE @searchString VARCHAR(20)
SET @searchString = 'wsbook'
SET @code = dbo.GenerateCodeForStringColumnCheck(@searchString)
EXEC(@code) 

--
JAG

Relevant Pages

  • run stored proc from excel with a parameter
    ... declare @sql nvarchar ... [Database Name] ... [Custodian Name] ... [Near Dups File Count] ...
    (microsoft.public.excel.programming)
  • Re: CDatabase CRecordset - How to get the number of rows?
    ... What is the value of dataStr in the CRecordset::Open call. ... > // Declare a long to hold the row count for the fast method ... > // Declare a long to hold the row count for the slow method ... >>> I'm now testing my DLL with the real database. ...
    (microsoft.public.vc.mfc)
  • Re: Update databases
    ... Compares if all tables in one database have analog in second ... declare @sqlStr varchar ... exec ('declare @Name sysname select @Name=name from ... -- ##CompareStr - will be used to pass comparing strings into dynamic script ...
    (microsoft.public.sqlserver.programming)
  • Looking for data dictionary/definition integrity checking utility
    ... This script searches for a value in the database. ... declare @tbl varchar ... -- char and numeric ... open CRR ...
    (microsoft.public.sqlserver.programming)
  • Sending e-mail from Access
    ... from a book that may have been intended for Outlook 2000 ... Option Compare Database ... 'Declare the RecordSet ... Dim objRecSet As Object ...
    (microsoft.public.access.externaldata)