How to search entire DB[?]

Blaise · June 10th, 2003, 01:01 AM

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!

David Cameron · June 10th, 2003, 01:19 AM

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

hortoristic · June 10th, 2003, 09:51 AM

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+'%'

David Cameron · June 10th, 2003, 06:30 PM

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

hortoristic · June 10th, 2003, 06:33 PM

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

David Cameron · June 10th, 2003, 06:39 PM

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

hortoristic · June 10th, 2003, 06:46 PM

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

Thanx

hortoristic · June 11th, 2003, 09:41 AM

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%'