Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    46

    Unanswered: How do I write a query to look in any table for the value 'foo'?

    I've got a rather large database, approx 560 tables, that is about 10G. We're running into trouble when an application moves data from my database into GreatPlains accounting software.

    The problem seems related to the value 'foo' not coming across correctly. I've looked at all the tables that I think would be involved, but couldn't find the value. So, I'd like to write a script to sequence through the tables and check each column for the value.

    Anybody know how to write such a script.

    Thanks,

    alex8675

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    great pains.

    personally I have never used full text search but you might want to look at that.

    a better solution might be to use profiler to trace the transfer.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't want thanks. I want adulation.

    Here you go:
    Code:
    set nocount on
    
    --Script parameters
    declare	@Value as VARCHAR(50)
    declare	@SearchStrings char(1)
    declare	@SearchNumbers char(1)
    declare	@SearchDates char(1)
    
    ----------------------------------------------------------------------------------------------------
    --Script: ValueSearcher
    --blindman, 9/19/2005
    --Searches columns in user tables for a specified value.
    --Returns the location where the value is found, and the number of records containing that value.
    --Enter the value to be found in the Script parameter settings section below.
    --Column types and comparison methods can be defined using the @Search parameters.
    ----------------------------------------------------------------------------------------------------
    --Script parameter settings
    set @Value = 'foo' --Enter the value to search for as a quoted string.
    set @SearchStrings = 'E' --E=Exact string search, L=Search using Like operator, N=Do not search.
    set @SearchNumbers = 'N' --Y=Search for numbers, N=Do not search.
    set @SearchDates = 'N' --E=Exact datetime search, D=Search whole date parts only, N=Do not search.
    ----------------------------------------------------------------------------------------------------
    
    --Processing variables
    create table #Results (TableName sysname, ColumnName sysname, RecordCount bigint)
    declare	@SQLString varchar(4000)
    
    --check validity of parameters
    if IsNumeric(@Value) = 0 set @SearchNumbers = 'N'
    if IsDate(@Value) = 0 set @SearchDates = 'N'
    
    --Create SQL statements to search the database
    declare	SQLCursor cursor for
    	--exact string columns
    	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ''' + @Value + ''' having count(*) > 0'
    	from	sysobjects
    		inner join syscolumns on sysobjects.id = syscolumns.id
    		inner join systypes on syscolumns.xtype = systypes.xtype
    	where	sysobjects.type = 'U'
    		and systypes.name in ('char', 'nchar', 'nvarchar', 'sysname', 'uniqueidentifer', 'varchar')
    		and @SearchStrings = 'E'
    	UNION
    	--like string columns
    	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' like ''%' + @Value + '%'' having count(*) > 0'
    	from	sysobjects
    		inner join syscolumns on sysobjects.id = syscolumns.id
    		inner join systypes on syscolumns.xtype = systypes.xtype
    	where	sysobjects.type = 'U'
    		and systypes.name in ('char', 'nchar', 'nvarchar', 'sysname', 'uniqueidentifer', 'varchar')
    		and @SearchStrings = 'L'
    	UNION
    	--numeric columns
    	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ' + @Value + ' having count(*) > 0'
    	from	sysobjects
    		inner join syscolumns on sysobjects.id = syscolumns.id
    		inner join systypes on syscolumns.xtype = systypes.xtype
    	where	sysobjects.type = 'U'
    		and systypes.name in ('bigint', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smallint', 'smallmoney', 'tinyint')
    		and @SearchNumbers = 'Y'
    	UNION
    	--Exact datetime columns
    	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where ' + syscolumns.name + ' = ''' + @Value + ''' having count(*) > 0'
    	from	sysobjects
    		inner join syscolumns on sysobjects.id = syscolumns.id
    		inner join systypes on syscolumns.xtype = systypes.xtype
    	where	sysobjects.type = 'U'
    		and systypes.name in ('datetime', 'smalldatetime')
    		and @SearchDates = 'E'
    	UNION
    	--dateonly datetime columns
    	select	'insert into #Results (TableName, ColumnName, RecordCount) select ''' + sysobjects.name + ''', ''' + syscolumns.name + ''', count(*) from ' + sysobjects.name + ' where convert(char(10), ' + syscolumns.name + ', 120) = convert(char(10), convert(datetime, ''' + @Value + '''), 120) having count(*) > 0'
    	from	sysobjects
    		inner join syscolumns on sysobjects.id = syscolumns.id
    		inner join systypes on syscolumns.xtype = systypes.xtype
    	where	sysobjects.type = 'U'
    		and systypes.name in ('datetime', 'smalldatetime')
    		and @SearchDates = 'D'
    
    --Run the SQL Statements
    Open	SQLCursor
    Fetch next from SQLCursor into @SQLString
    while @@FETCH_STATUS = 0
    	begin
    		exec (@SQLString)
    		fetch next from SQLCursor into @SQLString
    	end
    Close	SQLCursor
    Deallocate SQLCursor
    
    --Display the results
    select	cast(TableName + '.' + ColumnName as char(60)) Location,
    	RecordCount
    from	#Results
    order by TableName,
    	ColumnName
    
    --Clean up
    drop table #Results
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •