November 19, 2008 at 11:11 pm
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.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
November 19, 2008 at 11:37 pm
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.
November 20, 2008 at 12:56 am
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.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
November 20, 2008 at 1:22 am
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
November 20, 2008 at 1:41 am
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'
ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
ANDOBJECTPROPERTY(
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)
ANDTABLE_NAME= PARSENAME(@TableName, 1)
ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
ANDQUOTENAME(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
November 20, 2008 at 1:52 am
why not use sp_msforeachtable . Search this site for more info.
"Keep Trying"
November 20, 2008 at 2:17 am
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.
[font="Arial"]Nothing is impossible with Hard Work[/font]:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply