How I can search data in database like google? inc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How I can search data in database like google? inc

Author

Topic

sasystem
Starting Member

2 Posts

Posted - 2012-10-22 : 04:57:30

hi all, is it possible to Search data in database? with a single sql script or some tools will help me?
for example ,I have a HR database ,and I want to search 'CEO', $2354.00 or '12/4/1996' in all of the records.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-10-22 : 05:58:35

Declare
@SearchStr nvarchar(100)
SET @SearchStr ='CEO' -- Your desired text here

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+'')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+'' > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)+''
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN

EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-22 : 12:22:16

quote:
Originally posted by sasystem

hi all, is it possible to Search data in database? with a single sql script or some tools will help me?
for example ,I have a HR database ,and I want to search 'CEO', $2354.00 or '12/4/1996' in all of the records.

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-10-23 : 11:10:41

if you need to do this regularly then you probably want to consider some sort of search technology rather then trying this in db...

If its a once of then fine.

For example if you index the data into SOLR or similar then a common tactic is to include a field in your index that contains all the searchable fields from the other columns. Then you can issue general google like searches.

http://en.wikipedia.org/wiki/Apache_Solr

)link currently down due to amazon ec screw up:
http://lucene.apache.org/solr/

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources