Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching complete database


Searching complete database

Author
Message
Jaya Chitra
Jaya Chitra
SSC Eights!
SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)

Group: General Forum Members
Points: 924 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 WorkSmile
Gayathri.Varadarajan
Gayathri.Varadarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 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.
Jaya Chitra
Jaya Chitra
SSC Eights!
SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)

Group: General Forum Members
Points: 924 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 WorkSmile
Gayathri.Varadarajan
Gayathri.Varadarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 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
krayknot
krayknot
SSC-Addicted
SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)SSC-Addicted (408 reputation)

Group: General Forum Members
Points: 408 Visits: 534
Jaya Chitra (11/19/2008)
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.


--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
ChiragNS
ChiragNS
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2647 Visits: 1865
why not use sp_msforeachtable . Search this site for more info.

"Keep Trying"
Jaya Chitra
Jaya Chitra
SSC Eights!
SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)SSC Eights! (924 reputation)

Group: General Forum Members
Points: 924 Visits: 470
Dear Friends,

Thanks a lot Smile.

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 WorkSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search