Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    21

    Post Unanswered: Searching entire database

    Is there any way to do a complete database search in SQL server? For instance, if I have a criteria "DBFORUMS", I would like to scan through all user tables in my database to get all records with the word "DBFORUMS" stored, just like want we are doing in "Quick Search" in dbforums site.

    Any ideas?

    Thanks in advance.

  2. #2
    Join Date
    Sep 2003
    Posts
    364
    Here's a script you can modify to do what you're looking for.
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2004
    Posts
    21

    Thumbs up

    Originally posted by peterlemonjello
    Here's a script you can modify to do what you're looking for.
    Thank you very much. This is very useful.
    Do you know if there is anything built in SQL Server to do the same task?

    Thanks!

  4. #4
    Join Date
    Sep 2003
    Posts
    364
    Nope, I haven't found anything.

    FYI...I found this script on SQLServerCentral's website. They have a lot of usefull scripts to download.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or....

    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.

  6. #6
    Join Date
    Jan 2004
    Posts
    21

    Thumbs up

    Thank you!!!

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sweet.
    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
  •