need a query fr thispls help

Question

Table : TEXTTABLE columns: NameTEXT her each user submit some texts(eg: stories etc..less tan..lett b 400 words)... wat i nedd is wen another user use my search stories option and wen he enters a key word for this: my query shud search the keyword in each TEXT column of my table TEXTTABLE and if No of exitnace of tat key word is greater tan Five..curresponding user and text shud retrive.... i need all this in one select statement.... help me pls

Sreekanth · Answer

can any one help pls??

Sreekanth · Answer

oh.....but can u post tat Procedures details: 1 if a single column (correctly a cell in table) contains data with 400 words...can filter using this procedure.... More clearly: user's search for INDIA..and my data contains the word INDIA in more tan 10 times....this query shud select rows containing tes critera...... ....sir

Anwarul Haque · Answer

Hi please go with this article for you solution. NEW!!! Subscribe to my newsletter: Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line See also: Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming administration replication and tools SQL Server security best practices SQL Server administration best practices Related books: How to search all columns of all tables in a database for a keyword? While browsing the SQL Server newsgroups every once in a while I see a request for a script that can search all the columns of all the tables in a given database for a specific keyword. I never took such posts seriously. But then recently one of my network administrators was troubleshooting a problem with Microsoft Operations Manager (MOM). MOM uses SQL Server for storing all the computer alert and performance related information. He narrowed the problem down to something specific and needed a script that can search all the MOM tables for a specific string. I had no such script handy at that time so we ended up searching manually. That's when I really felt the need for such a script and came up with this stored procedure SearchAllTables. It accepts a search string as input parameter goes and searches all char varchar nchar nvarchar columns of all tables (only user created tables. System tables are excluded) owned by all users in the current database. Feel free to extend this procedure to search other datatypes. The output of this stored procedure contains two columns: - 1) The table name and column name in which the search string was found - 2) The actual content/value of the column (Only the first 3630 characters are displayed) Here's a word of caution before you go ahead and run this procedure. Though this procedure is quite quick on smaller databases it could take hours to complete on a large database with too many character columns and a huge number of rows. So if you are trying to run it on a large database be prepared to wait (I did use the locking hint NOLOCK to reduce any locking). It is efficient to use Full-Text search feature for free text searching but it doesn't make sense for this type of ad-hoc requirements. Create this procedure in the required database and here is how you run it: --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

need a query fr this,pls help