Searching complete database

  • 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]:)

  • 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.

  • 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]:)

  • 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

  • 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

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

    "Keep Trying"

  • 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