x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

How do I search a database for the umpteenth time

OK, so I got tired of writing this...so I decided to build a bells and whistles full database search.  Now at first blush, the reaction that this is bad....and it is...but I've been handed so many poorly designed databases, I felt for this persons post.  So now I'm done...I don't have to write it again....I guess we could expanded it to server the entire server....

EDIT: Modified to Search Text and ntext as well.  Changes are highlighted in Red.  Anyone know why the Row counts are still being produced?

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
   SPID int
 , SearchDate datetime
 , TABLE_CATALOG sysname
 , TABLE_SCHEMA sysname
 , TABLE_NAME sysname
 , COLUMN_NAME varchar(255)
 , DATA_TYPE varchar(25)
 , SEARCHARG varchar(2000)
 , RESULTS varchar(4000))
GO

CREATE PROC myDBSearch99
 @SearchArg varchar(2000)
AS
  BEGIN
 SET NOCOUNT ON
 DECLARE @sql varchar(8000), @Date datetime
 DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname, @DATA_TYPE varchar(25)
 SELECT @date = GetDate() 

 DELETE FROM  myTableSearch99 WHERE SPID = @@SPID

 DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME , c.DATA_TYPE
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext','datetime')
     AND t.TABLE_NAME <> 'myTableSearch99'

 OPEN myCursor99
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE

 INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
 SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

 WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'SET NOCOUNT ON ' + CHAR(13)
    +' INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,DATA_TYPE,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + '''' + @DATA_TYPE + ''',' + ''''+ @SearchArg + '''' + ','
    + 'CONVERT(varchar(4000),'+@COLUMN_NAME+')'
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
    + ' WHERE PATINDEX( ' + '''' + '%' + @SearchArg + '%' + '''' + ','
    + CASE WHEN @DATA_TYPE = 'datetime' THEN 'CONVERT(varchar(25),'+@COLUMN_NAME
    + ',121)' ELSE @COLUMN_NAME END
    + ') <> 0'

--    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME, @DATA_TYPE
   END

 SET NOCOUNT OFF


  SELECT   
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , DATA_TYPE
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
  WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
 CLOSE myCursor99
 DEALLOCATE myCursor99 
  END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

EXEC myDBSearch99 '1996-07-04'
GO


SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

This was the original

 

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTableSearch99(
   SPID int
 , SearchDate datetime
 , TABLE_CATALOG sysname
 , TABLE_SCHEMA sysname
 , TABLE_NAME sysname
 , COLUMN_NAME varchar(255)
 , SEARCHARG varchar(2000)
 , RESULTS varchar(4000))
GO

CREATE PROC myDBSearch99
 @SearchArg varchar(2000)
AS
  BEGIN
 SET NOCOUNT ON
 DECLARE @sql varchar(8000), @Date datetime
 DECLARE @TABLE_CATALOG sysname, @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @COLUMN_NAME sysname
 SELECT @date = GetDate() 

 DECLARE myCursor99 CURSOR FOR
  SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.Columns c
     INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_CATALOG = t.TABLE_CATALOG
     AND c.TABLE_SCHEMA  = t.TABLE_SCHEMA
     AND c. TABLE_NAME   = t.TABLE_NAME
   WHERE t.TABLE_TYPE = 'BASE TABLE'
     AND c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','datetime')

 OPEN myCursor99
 FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME

 INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME, SEARCHARG)
 SELECT @@SPID, CONVERT(varchar(25),@Date,120), @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @SearchArg

 WHILE @@FETCH_STATUS = 0
   BEGIN
  SELECT @sql = 'INSERT INTO myTableSearch99(SPID,SearchDate,TABLE_CATALOG,TABLE_SCHEMA, ' +
    + 'TABLE_NAME,COLUMN_NAME,SEARCHARG,RESULTS) '
    + 'SELECT ' + CONVERT(varchar(15),@@SPID) + ',' + ''''
    + CONVERT(varchar(25),@Date,120) + '''' + ',' + '''' +
    + @TABLE_CATALOG + ''',''' + @TABLE_SCHEMA + ''',''' +  @TABLE_NAME + ''',' + ''''
    + @COLUMN_NAME + ''',' + ''''+ @SearchArg + '''' + ',' + @COLUMN_NAME
    + ' FROM ' + '['+ @TABLE_CATALOG + '].[' + @TABLE_SCHEMA + '].[' +  @TABLE_NAME + '] '
    + ' WHERE ' + @COLUMN_NAME + ' LIKE ''%' + @SearchArg + '%'''
--  SELECT @sql
  EXEC(@sql)
  FETCH NEXT FROM myCursor99 INTO @TABLE_CATALOG, @TABLE_SCHEMA, @TABLE_NAME, @COLUMN_NAME
   END

 SET NOCOUNT OFF


  SELECT   
    SPID
  , SearchDate
  , TABLE_CATALOG
  , TABLE_SCHEMA
  , TABLE_NAME
  , COLUMN_NAME
  , SEARCHARG
  , RESULTS
   FROM    myTableSearch99 o
  WHERE SPID = @@SPID
    AND SearchDate = ( SELECT MAX(SearchDate)
    FROM myTableSearch99 i
          WHERE i.SPID = o.SPID)
 CLOSE myCursor99
 DEALLOCATE myCursor99 
  END
GO

EXEC myDBSearch99 'Ale'
GO

EXEC myDBSearch99 'Stout'
GO

SELECT * FROM myTableSearch99 ORDER BY SearchDate DESC, COLUMN_NAME
GO

SET NOCOUNT OFF
TRUNCATE TABLE myTableSearch99
DROP TABLE myTableSearch99
DROP PROC myDBSearch99

Print | posted on Tuesday, February 08, 2005 1:20 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: How do I search a database for the umpteenth time

Brett,
You are the Man!! I was just about to start coding this for searching audit tables.. Show me what user X did today?

The Audit tables are in another DB so this will be perfect..I'll add a NOLOCK clause in there for giggles...
2/9/2005 7:49 PM | DavidM
Gravatar

# Updated_By

I would imagine each of your audit tables have a very specific column where the user id was stored...I would modify it to look for a very specific column and the return the rows.

As a matter of fact I would do that differently..

I'll see what I would write against some of my audit tables...I'll post another article

2/10/2005 9:27 AM | Brett
Gravatar

# re: How do I search a database for the umpteenth time

On sql7, not configured for usa, Calling the proc gets error:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

2/14/2005 8:53 AM | Mike B
Gravatar

# Really?

I guess you could remove the search for datetime values...I don't have a 7 environment left anymore.

If you figure out excatly where the problem lies, I'll make sure I post the correction.

Thanks for looking.

2/14/2005 9:55 AM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET