Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 10th, 2003, 01:01 AM
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to search entire DB[?]

Hi guys,

Is there a way to write a query to search all tables in a database, I've experimented with views and such like but haven't had much luck and searches on the web havne't heled me.

I want to search all content for a string such as 'hello'
Or alternatively search all Field names for my string 'firstname'

Something like that, any pointers or answers will be greatly appreciatiated.

TIA!
 
Old June 10th, 2003, 01:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There are solutions to this, but they involve either dynamic SQL or cursors. Also the assumption here is that your database structure is constantly changing, otherwise you would know the columns that are to be searched. In general if your database structure is regularly changing then it is flawed (IMHO). Still there is a need for something like this for administration scripts. I've put together something quick that might be of some use, or may at least point in the right direction. Execute this, cut off the final UNION and run it.

DECLARE @Text VarChar(50)
SET @Text = 'Test'

SET NOCOUNT ON

SELECT
'SELECT ''' + so.name + '.' + sc.name + '''
FROM dbo.' + so.name + '
WHERE ' + sc.name + ' LIKE ''%' + @Text + '%''
UNION'
FROM syscolumns sc
    INNER JOIN sysobjects so ON
    so.id = sc.id
    INNER JOIN systypes st ON
    st.type = sc.type
WHERE st.name IN ('nvarchar', 'varchar', 'nchar', 'char') AND
    so.type = 'u'

regards
David Cameron
 
Old June 10th, 2003, 09:51 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Wont this accomplish the same (I couldn't get other code to work):

DECLARE @Text VarChar(50)
SET @Text = 'jobcode'

SET NOCOUNT ON
select sc.name as TextToFind,so.name as Object
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
INNER JOIN systypes st ON st.type = sc.type
WHERE st.name IN ('nvarchar', 'varchar', 'nchar', 'char') AND so.type = 'u' and sc.name like '%'+@Text+'%'
 
Old June 10th, 2003, 06:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That is a good question. Blaise, do you want to search table structure for a certain string (eg in searching for 'test', a column called mytest will be returned), or do you want to search through the values held in the columns? The script I wrote was to do the second. To get it running, execute the code, cut and paste the results back into the window, remove the final UNION, and execute it.

regards
David Cameron
 
Old June 10th, 2003, 06:33 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

David - I was really wanting to get your code to run - but when I run it I get all kinds of problems with the syntax in both Embarcadero and M$ Query Analier
 
Old June 10th, 2003, 06:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sydney, NSW, Australia.
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That is strange. I've never used Embarcadero so I can't comment on that. Where do you get the problem? after you execute the code you should get something like this:

Code:
----------------------------------------------------- 
SELECT 'tblAccessLevel.txtAccessLevel'
FROM dbo.tblAccessLevel
WHERE txtAccessLevel LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtAccessLevel'
FROM dbo.tblAccessLevel
WHERE txtAccessLevel LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtDescription'
FROM dbo.tblAccessLevel
WHERE txtDescription LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtDescription'
FROM dbo.tblAccessLevel
WHERE txtDescription LIKE '%Test%'
UNION
which you need to edit to turn into:
Code:
                                                                                                                                                                                                                                                                 
SELECT 'tblAccessLevel.txtAccessLevel'
FROM dbo.tblAccessLevel
WHERE txtAccessLevel LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtAccessLevel'
FROM dbo.tblAccessLevel
WHERE txtAccessLevel LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtDescription'
FROM dbo.tblAccessLevel
WHERE txtDescription LIKE '%Test%'
UNION
SELECT 'tblAccessLevel.txtDescription'
FROM dbo.tblAccessLevel
WHERE txtDescription LIKE '%Test%'
I guess you could leave the comments and the white space. Then cut and paste this edited result from the output window to the execution window of QA and execute it.

(well I guess comments and white-space don't really matter)

regards
David Cameron
 
Old June 10th, 2003, 06:46 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Works great David - must have been doing fat finger slips early this morning.

Thanx
 
Old June 11th, 2003, 09:41 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here is another way using a SP and a cursor:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetText]
GO

/*
GetText '306341801'
*/

Create Procedure GetText
    @search_str varchar(1000)

AS

DECLARE
 @VAR1 SYSNAME,
 @VAR2 SYSNAME,
 @VAR3 NVARCHAR(4000),
 --@search_str NVARCHAR(1000),
 @counter INT,
 @dbname SYSNAME
-- SET SEARCH CONDITION HERE--
--SET @search_str='joe'
IF EXISTS (SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE NAME LIKE '#FTS_TABLE%')
DROP TABLE #FTS_TABLE
CREATE TABLE #FTS_TABLE (TABLE_NAME VARCHAR(150), CONTENT VARCHAR(1000),
                       CONTAIN VARCHAR(15), SEARCH_STRING VARCHAR(1000))
SET @counter=0
SELECT @dbname= db_name()
DECLARE CUR CURSOR FOR
      SELECT SYSOBJECTS.NAME , SYSCOLUMNS.NAME FROM SYSOBJECTS
      INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
      INNER JOIN SYSTYPES ON SYSCOLUMNS.XTYPE = SYSTYPES.XTYPE WHERE
      SYSOBJECTS.TYPE ='U' AND
      OBJECTPROPERTY(OBJECT_ID(SYSOBJECTS.NAME), N'ISMSSHIPPED') = 0 AND
      SYSTYPES.NAME IN('CHAR', 'VARCHAR','TEXT')--AND
      --SYSOBJECTS.NAME LIKE '%MAM_%'
      ORDER BY SYSOBJECTS.NAME, SYSCOLUMNS.COLID
 OPEN CUR
 FETCH NEXT FROM CUR INTO @VAR1, @VAR2
WHILE @@FETCH_STATUS = 0
 BEGIN
      SET @VAR3=N'IF EXISTS( SELECT 1 FROM '+@VAR1+' WHERE '+@VAR2+' LIKE '''+
      '%'+@search_str+'%'+''' )INSERT INTO #FTS_TABLE SELECT '
      +''''+UPPER(@VAR1+'('+''+@VAR2)+')'+' HAS STRING '''+' ,'+
      @VAR2+','+'''CONTAINING '''+','''+UPPER(@search_str)+''' FROM '+@VAR1+
      ' WHERE '+@VAR2+' LIKE '+''''+'%'+@search_str+'%'+''''
      EXEC SP_EXECUTESQL @VAR3
      IF @@ROWCOUNT>0
      set @counter=@counter+1
      FETCH NEXT FROM CUR INTO @VAR1, @VAR2
 END
 CLOSE CUR
 DEALLOCATE CUR
IF @counter=0
BEGIN
IF CHARINDEX('-',HOST_NAME())<>0
PRINT 'Sorry '+REPLACE(HOST_NAME(),SUBSTRING(HOST_NAME(), CHARINDEX('-',HOST_NAME()), 12),'')+
', no records matching your search string '''+upper(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
ELSE
PRINT 'Sorry '+HOST_NAME()+
', no records matching your search string '''+UPPER(@search_str)+''''+CHAR(13)+'found in database '+
@dbname+CHAR(13)+'Please try again with other strings.'
END
ELSE
SELECT TABLE_NAME 'TABLE(COLUMN)', CONTENT 'VALUE', CONTAIN ' ',
                  SEARCH_STRING 'YOUR SEARCH STRING'
                  FROM #FTS_TABLE WHERE TABLE_NAME NOT LIKE '%#FTS_TABLE%'




Similar Threads
Thread Thread Starter Forum Replies Last Post
Search button doesn't search Access DB cbones Visual Studio 2008 1 October 27th, 2008 07:36 PM
Speeding up DB search, how? rtr1900 Classic ASP Databases 3 December 18th, 2006 09:04 AM
Q about search DB... ugmos Classic ASP Databases 1 February 27th, 2006 07:49 PM
Problem using wildcards to search an Access DB taliesin Classic ASP Databases 4 June 23rd, 2003 11:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.