Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: search in all tables

    I would like to do a key word search in a small sql server db (30Mo) (search in all tables).
    I opted to export the db and perform this search.

    The proble is :
    when we use export DTS with Excel as a source, each table is put in a tab (in the Excel document).
    when we use a text file as a source. I have the chose to select only one table (to all tables).

    A solution for this

  2. #2
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    Why you don't use a FULL TEXT search?
    Davide Mauri
    http://www.davidemauri.it

  3. #3
    Join Date
    Oct 2003
    Posts
    47
    I never used it ...

    Could you give me a brief idea : how to use it in order to search in all tables

  4. #4
    Join Date
    Feb 2003
    Location
    Milano, Italy
    Posts
    80
    If you need to do a keywords search i suppose that you need to search into text or char or varchar columns.

    If yes you can enable a FULL TEXT on the table you need. Once done you can write a query that perform a standard SELECT but using FREETEXT or CONTAINS in the WHERE clause.

    You have to deal with the search in all tables anyway....you can create another db where you aggregate all your tables in one single table and then enable full text on this last one. This will surely be better that exporting to excel or flat text file.
    Davide Mauri
    http://www.davidemauri.it

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831

    Keyword search in SQL Tables

    Hello,

    Here is a code snippet to try, but you may need to change a few of the criteria items:


    Set quoted_identifier off
    Set nocount off

    DECLARE @datatype int
    DECLARE @search varchar(55)
    DECLARE @tablename varchar(255)
    DECLARE @string varchar(255)
    DECLARE @columnname varchar(255)
    DECLARE @sql nvarchar(525)

    SET @search = '88888'

    -- create a temporary table to hold results of table count
    CREATE TABLE #DATACOUNT(tablename varchar(255),columnname varchar(255),string varchar(255))

    -- declare a cursor
    DECLARE tablenames_cursor CURSOR FOR
    SELECT o.Name,c.Name
    FROM sysobjects o
    JOIN syscolumns c on c.id = o.id
    JOIN systypes t on t.xtype = c.xtype
    where o.xtype = 'U' and substring(o.name,1,3) = 'tbl' and o.name <> 'tblCallRec' and c.name <> 'ChangeBy' and c.name <> 'CreateBy' and t.xtype in (167,61,48) order by o.Name,c.Name
    OPEN tablenames_cursor
    FETCH NEXT FROM tablenames_cursor INTO @tablename,@columnname
    WHILE (@@fetch_status <> -1)
    BEGIN
    SET @string = @columnname
    SET @SQL = " INSERT INTO #DATACOUNT"
    SET @SQL = @SQL + " SELECT 'Table' = '" + RTRIM(UPPER(@tablename)) + "','Column' = '" + RTRIM(UPPER(@string)) + "','String' = [" + @columnname + "]"
    SET @SQL = @SQL + " FROM " + RTRIM(UPPER(@tablename)) + " WITH(NOLOCK)"
    SET @SQL = @SQL + " GROUP BY [" + @columnname + "] Having CHARINDEX(" + "'" + @search + "',[" + @columnname + "]) > 0 "

    PRINT @SQL
    EXEC sp_executesql @SQL

    FETCH NEXT FROM tablenames_cursor INTO @tablename,@columnname
    END

    DEALLOCATE tablenames_cursor

    SELECT * FROM #DATACOUNT

    DROP TABLE #DATACOUNT


    Jon L. Campbell

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[mySprocSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[mySprocSearch]
    GO
    
    
    CREATE PROC mySprocSearch @SearchArg varchar(20)
    AS
    
    SET NOCOUNT ON
    
    DECLARE CSR CURSOR READ_ONLY FOR
    	SELECT  'INSERT INTO #TEMP (RowMatch, COLUMN_NAME, TABLE_NAME) '+
    		+'SELECT '+c.COLUMN_NAME+', '+''''	
    		+c.COLUMN_NAME+''''+' AS COLUMN_NAME, '+''''
    		+c.TABLE_NAME+''''+' AS TABLE_NAME '
    		+'FROM ['+ c.TABLE_NAME+'] '
    		+'WHERE '+'['+c.COLUMN_NAME+'] LIKE '+''''+'%'+@SearchArg+'%'+'''' AS SQL
    	 FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
    	   ON t.TABLE_NAME = c.TABLE_NAME 
    	WHERE DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
    	  AND t.TABLE_SCHEMA = 'dbo'
    	  AND t.TABLE_TYPE   = 'BASE TABLE' 
    	ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
    
    
    CREATE TABLE #TEMP(RowMatch varchar(7488), COLUMN_NAME sysname, TABLE_NAME sysname)
    
    DECLARE @SQL varchar(8000), @rc int
    
    OPEN CSR
    
    FETCH NEXT FROM CSR
    	INTO	@SQL
    
    WHILE @@FETCH_STATUS = 0
    
    	BEGIN
    		EXEC(@SQL)
    		SELECT @rc = @@ERROR
    		IF @rc <> 0 
    			BEGIN 
    				SELECT @rc AS ReturnCode, @SQL As SQLStatement
    				CLOSE CSR
    				DEALLOCATE CSR
    				Return -1
    			END
    		FETCH NEXT FROM CSR
    			INTO	@SQL
    	END
    
    SELECT COLUMN_NAME, TABLE_NAME, RowMatch FROM #TEMP
    
    CLOSE CSR
    DEALLOCATE CSR
    DROP TABLE #TEMP
    SET NOCOUNT OFF
    
    Return 0
    GO
    
    EXEC mySprocSearch 'Beer'
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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