Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Unanswered: Searching for a word on each table on very field

    Hi everybody,

    is it possible using SQL Server search for a word on each
    table on each field?

    Thanks

    Fabio

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It sounds like you want Full Text Search, but be careful what you ask for, lest you get it! This gets pretty "expensive" in terms of disk and other resources.

    -PatP

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    this is not what I am looking for...

    I explain it better..
    I'm working on a database created by some other people
    and I have to uderstand in which tables some content is
    stored by the application (I can access only to the db not
    the application source). I know what the application store
    but I don't know where (and there are 100+ tables in the db).

    So I need a way starting from some information guessed from
    the datas inserted in the application to locate where the data
    is actually stored.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd cheat and use SQL Profiler, but I'm rather lazy.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yeah - we migrated a third party system to another thrird party system and I had to reproduce some reports fast. This meant locating fields and tables based on a sample of the data they contained.

    I wrote some vb to do this with all the SQL Server tables linked to an Access db. It is ugly and inefficient (even more inefficient than it sounds) but it does the job - after about 40 mins for my db.

    I am happy to share (if required) once I have checked that the code is not even worse than I remember it
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2006
    Posts
    1

    Search all tables for data stored within

    Maybe this is what you are after ?? This SP searches all fields in all tables in the database for the specified data (Usage: searchalltables 'yourcriteria')
    Attached Files Attached Files

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is the code I use, which also searches for non-varchar values:
    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 = 'JEFF' --Enter the value to search for as a quoted string.
    set @SearchStrings = 'L' --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
  •