Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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!
Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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+'%'
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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
Reply With Quote
  #6 (permalink)  
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
Reply With Quote
  #7 (permalink)  
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
Reply With Quote
  #8 (permalink)  
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%'
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:28 PM.


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