Searching complete database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Searching complete database

Searching complete database

Rate Topic

Display Mode

Topic Options

Author

Message

Jaya Chitra

Posted Wednesday, November 19, 2008 11:11 PM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:29 AM
Points: 920, Visits: 470

Dear Friends,

In my application, I have the below requirement.

When the user specifies a keyword, I have to search the complete database tables and column values which matches the specified keyword.

For e.g.
If the user specifies 'sample', then the search has to search all the column values in all the tables and has to give back the data from each table i.e. from student these are the matches, from course these are the matches etc.,.

Can any one tell me how to do it in SQL?

Thanks a lot in advance.

Nothing is impossible with Hard Work:)

Post #605589

Gayathri.Varadarajan

Posted Wednesday, November 19, 2008 11:37 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 5:16 AM
Points: 1,090, Visits: 388

Just check if this may help you:
You can start from sysobjects and syscolumns
Get the datatype and the search value as parameters
:For each object in sysobjects where type ='U'
:for each column of the sysobject where xtype = @datatype
check if the value exists in the table for the column selected
if the value exists then insert the table name and the column
name in ur table variable.
proceed until for all columns and all tables.

Post #605598

Jaya Chitra

Posted Thursday, November 20, 2008 12:56 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:29 AM
Points: 920, Visits: 470

Dear Friend,

OOps, I want to perform search in the data in the columns of all the tables not the column names.

When the user gives 'sample' then I have to search the entire data in all the tables in my database and where ever the match is found that records has to be retrieved and shown to the users. The columns are fixed.

Any way, Thanks for your suggestion.

Nothing is impossible with Hard Work:)

Post #605619

Gayathri.Varadarajan

Posted Thursday, November 20, 2008 1:22 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 5:16 AM
Points: 1,090, Visits: 388

Ya i understand u require the values of the columns.The below script will do the same
Modify this script to suit your need and to meet the performance.The previous post will give the logic of this script:
create table t1 (i int ,j varchar(20))
create table t2 (k int ,l varchar(20))
create table t3 (m int ,n numeric(10,3))

insert into t1 values (1,'hello')
insert into t1 values (2,'world')
insert into t1 values (3,'excellent')

insert into t2 values (1,'hello')
insert into t2 values (2,'happy')
insert into t2 values (3,'great')

insert into t3 values (1,2.5)
insert into t3 values (2,5.5)
insert into t3 values (3,7.5)
declare @name varchar(50)
declare @col varchar(50)
declare @value varchar(50)
declare @str varchar(1000)
declare @i int
declare @count int
select @value = 'hello'

declare @table table(id int identity(1,1), tablename varchar(50),columnname varchar(50))

insert into @table
select a.name, b.name
from syscolumns b,sysobjects a
where a.id = b.id
and a.type = 'U'
and b.xtype = 167
and a.name in ('t1','t2','t3')
select @count = count(*) from @table
select @i = 0
while (@i <=@count)
begin
select @name = tablename ,@col = columnname
from @table where id = @i
select @str = 'select * from ' + @name + ' where ' + @col + ' = ''' + @value +''''
select @str
exec(@str)
select @i = @i + 1
end

Post #605624

krayknot

Posted Thursday, November 20, 2008 1:41 AM

Old Hand

Group: General Forum Members
Last Login: Friday, October 07, 2011 1:41 AM
Points: 346, Visits: 534

Jaya Chitra (11/19/2008)

--To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO

Here is the complete stored procedure code:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

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
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results
END

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Post #605628

ChiragNS

Posted Thursday, November 20, 2008 1:52 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

why not use sp_msforeachtable . Search this site for more info.

"Keep Trying"

Post #605632

Jaya Chitra

Posted Thursday, November 20, 2008 2:17 AM

SSC Eights!

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:29 AM
Points: 920, Visits: 470

Dear Friends,

Thanks a lot :).

The script what "krayknot" has given is working fine and that's what I need too.

Thanks a lot for all of us who has replied and helped me in timely manner.

Thanks a lot.

Nothing is impossible with Hard Work:)

Post #605638

« Prev Topic | Next Topic »

Permissions