Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Searching complete database Expand / Collapse
Author
Message
Posted Wednesday, November 19, 2008 11:11 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Wednesday, November 19, 2008 11:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Thursday, November 20, 2008 12:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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
Posted Thursday, November 20, 2008 1:22 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Thursday, November 20, 2008 1:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, 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
Post #605628
Posted Thursday, November 20, 2008 1:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, September 1, 2014 6:55 AM
Points: 2,366, Visits: 1,845
why not use sp_msforeachtable . Search this site for more info.


"Keep Trying"
Post #605632
Posted Thursday, November 20, 2008 2:17 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!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 »

Add to briefcase

Permissions Expand / Collapse