I would like to do a key word search in a small sql server db (30Mo) (search in all tables).
I opted to export the db and perform this search.
The proble is :
when we use export DTS with Excel as a source, each table is put in a tab (in the Excel document).
when we use a text file as a source. I have the chose to select only one table (to all tables).
A solution for this
i have 13 table in my sql server nd they have no relation
how can i search a keyword in all tables?
Hi all
my question is how to search into whole tables into specific database and result be as following
TableName ColumnName WordMatching
kindly any suggest or help i will be appreciated
Thanks
i have two tables,
Opportunity
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL ,
[OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OpportunityTypeID] [numeric](10, 0) NOT NULL ,
[SLABased] [int] NOT NULL ,
[LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateCreated] [datetime] NOT NULL ,
[AccountID] [int] NOT NULL ,
[GeographyID] [int] NOT NULL ,
[VerticalID] [int] NOT NULL ,
[BDMID] [int] NOT NULL ,
[Probability] [int] NOT NULL ,
[PASStatus] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL
and SKILL
[SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[OpportunityID] [numeric](18, 0) NOT NULL ,
[OrderId] [numeric](18, 0) NOT NULL ,
[PracticeID] [int] NULL ,
[SkillID] [int] NOT NULL ,
[NoOfPeople] [int] NOT NULL ,
[Clientinterview] [int] NOT NULL ,
[Location] [int] NOT NULL ,
[JDAttached] [int] NOT NULL ,
[JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [int] NULL ,
[Experience] [int] NULL ,
[InsertedDate] [datetime] NULL ,
[InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdatedDate] [datetime] NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UpdatedFlag] [int] NULL ,
[GeoLocation] [int] NULL
)
i want to make a stored procedure for custom search on these two tables
with the following fields given to the user as an option to make his
choice..
from opportunity table -
OpportunityTypeID,SLABased,AccountID ,
GeographyID,
VerticalID,
BDMID,
Probability
and from skill table
SkillID, Location, GeoLocation
and return all the fields of opportunity table.
Can some make the stored procedure for me..
thanks a lot.
well i am using vb.net and would like to know how to search a specific table in the databasefor egif table1 exists in database then drop table1end if ...something like that
View 2 Replies View RelatedI drew short straw on some reports work and am spending an eternity searching through catalogs looking for tables and columns. I'm a little new to SQL. Is there a way to search a catalog for table column names?
View 2 Replies View RelatedHow to search all columns of all tables for a keyword?:o
that in MS sql
thanks in advance..
I need to search all tables in my user database to find a value. I don't know what the column name is.
I know of one table that contains the value, but I need to look through all the tables and all there columns to see if I can find if this value exists in more than one place. It is an int value - 394789.
Hi,
I want to search through 3 tables (TableB, TableC, TableD) to find
which hdr_ctl_nbr(s) are on those tables but not on TableA. In other words, TableA should match the combined tables B, C, and D but it doesn't, so I want to find what's missing on TableA. I know how to search TableB (see SELECT below) but how would I add TableC and TableD to this statement ? Thanks, Jeff
Select hdr_ctl_nbr, count(*) from TableA c
where c.hdr_ctl_nbr not exists (select hdr_ctl_nbr from TableB)
group by c.hdr_ctl_nbr
I'm looking for a stored procedure (or query) to search an entiredatabase for a specific string value and replace it with another. I'msure I saw an SP for this a while back by someone, but cannot find itagain. The SP took the search string and replace string as parametersand did the rest. Any ideas where I can find this ?Bear in mind, the idea is that this can be re-used and run on anydatabase, so it would have to find all tables and search through those.TaRyan
View 2 Replies View RelatedI'm working on search query for a troubleticket system.There are two tables I want to search, the description in the tickets tableand the corresponding notes in the notes table. The problem is there is aone to many relationship between the tickets (one) and the notes (many)tables.I only need the ticket number of any ticket that finds the search string inthe description or any of the corresponding notes.
View 2 Replies View RelatedHello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
Hello Everyone,
Is there a way to search all the tables in a database for a value that is found in a specific column? Another problem is that although this specific column will be found in most of the tables in the database, it has a different two or three digit prefix in the column name for each table.
I think the logic will go something like this...
As the script jumps from table to table, it should first look for a column whose name ends with ***ITM. If it does not find a column with ***ITM it should move on to the next table. If it does, then search the ***ITM column for my value. If it does not find my value, then move to the next table. But if it does, change my value to a new value.
Any help will be greatly appreciated.
Thank you all...
Hi there,
I am currently seaching a db for all tables that have the same column name, for example, 'qdate'. Can anyone let me know how I can write a script that will search a db for all tables with this column name 'qdate'?
Thanks for your help!!!!!!
From
NewtoSql
I found a few 'SearchAllTables' scripts out there to find a value in any table/column/row of any Database.
My problem is that none of these database search queries seem to look at primary key values.
The value I'm looking for is a primary key - I need to find all the other tables that have this primary key value.
how I could accomplish this?
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
Anyone have any recommendations on how to bring together a search across multiple tables? Like I'd love to do a search where i searched for the product name, company name or tag all in one query (though all are in separate tables)... is that possible? Individually, it's easy, but combined it's very hard.
View 3 Replies View RelatedIs their a way to search every table in a database for a particular character string that might appear in any column in any of the tables?
For example suppose in a hypothetical situation that the character string "http://www.DrSeusOnTheLoose.com" appears randomly in a database, but we do not know which tables it occurs in and in what columns. How can we do a search to find such a character string?
As you know, this is easily accomplished in MS Windows XX or through a dos command line script. But when it comes to searching a whole database, I don't know how to do this. Can someone please help me?
Ralph
is it possible to use a full-text search query on two tables at the same time? for example, say i have a table of items, and another table of descriptions... would i be able to search both those tables for a string in one query? they are currently in indexed in separate catalogs, would i need to put them both in one?
thanks
hi friends,
I know how to create full text catalog with single table in single database.
But i need to know the steps to create full text catalog with multiple tables in single database.
Hello,We are developing an application against an MS SQL Server 2000 databasewhich requires that we implement full-text searching across columns inmultiple tables. The research that we have conducted seems to indicatethat this is not directly possible within SQL Server 2000. And we canfind no way to implement this as the catalogs that are generated aretable specific.As a work-around, we are planning to create a secondary table usedstrictly for searching which is going to denormalize and combine thesearchable data into one catalog. We just want to confirm that this isthe recommended approach or if there is an alternate solution anyonehas used. Please note that upgrading to SQL Server 2005 is not anoption.Thanks.John FlemingJoin Bytes!
View 1 Replies View RelatedIs it possible to search a string/value across 1000's of tables and just display the table name and column name which it is in. I don't need to know every instance of the string/value only that I can find it in a given table name.
View 9 Replies View RelatedI need a query to publish the front page of a blog. Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments. I would like to be able to do this in one sql statement, if possible.
The table structure is below, you can assume the first image returned from the image table is the primary image.
CREATE TABLE [dbo].[Blogs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BlogTitle] [nvarchar](200) NULL,
[BlogText] [nvarchar](max) NULL,
[Tags] [nvarchar](200) NULL,
[Code] ....
Hi,
I have tried this code from http://jtkane.spaces.live.com/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.entry for full-text search on multiple tables & columns.
Here's my code:
SELECT * from [tStaffDir] AS e, [tStaffDir_PrevEmp] t,CONTAINSTABLE([tStaffDir], *, @Name) as AwhereA.[KEY] = e.[ID] andt.[ID] = e.[ID]
I have FT the both the tables above and I am able to get results from the [tStaffDir] table but not the [tStaffDir_PrevEmp] table.The [tStaffDir_PrevEmp] table does have a column (which is [ID]) that is indexed, unique and non-Nullable.Please advise what I should do and look out for.
Many Thanks.
Hi, There have been a number of posts on querying multiple tables using full text in SQL Server. I haven't quite found the answer I'm looking for, and I'm aware that in 2005 some full text functionality changed. I have an Events table which joins to a Venue table and also a many-to-many link (via a table called EventsToArtists) to an Artists table.When a user searches for "Rolling Stones Wembley" (without the apostrophes), I want the query to look in the following columns:Event.EventNameEvent.DescriptionArtists.ArtistNameVenue.VenueNameI have done quite a bit of testing on this, and haven't yet got the desired results. Rolling Stones will be found in the Artists table, and Wembley in the Venue table. At the moment, if I search for Rolling Stones, it behaves as expected. But if search for the venue name as well I get no results. I'm pretty sure this is because EventName wouldn't contain all key words and so the record is excluded from the results. At the moment I am using INNER JOINs between the tables....perhaps I should be executing a full text search on each table's catalogue and then do a UNION to join the resultsets?Hope you can point me in the right direction.Thanks
View 5 Replies View Relatedi have more tables
exmaple:
table1(id,title,content,date)
table2(did,type,title,text1,text2,requestdate)
table3(subid,value1,value2,value3,value4)
.......................15 tables left,some exmaple
now i want use stored procedure do search for asp.net
asp.net send search keyword to sql server,sql server get keywords execute search and return results to web application(asp.net)
i don't konw how what write t-sql,please friends help me.
because table colums is different,so need write t-sql put the temp DATA TO Temp DataTable,last return Temp DataTable to asp.net,asp.net execute split pager display data,eventable colums is different,so on asp.net links is different,need t-sql programming,hope friends can help me complete this effect(i need code,C#/VB.NET +T-SQL).
i at ago like this do.
<%@ Page Language="C#" ValidateRequest="false"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private void page_init()
{
if (!IsSecure())
{
Response.Redirect("/sitemap/default.mspx?request=error&code=1065");
}
}
private void page_load(object sender, EventArgs e)
{
string CurrentPageValue = "";
string Qu = "";
Double PageValue = 0;
CurrentPageValue = Request.QueryString["PageN"];
Qu = Request.QueryString["QuKey"];
Qu = ReplaceStr(Qu);
if (Qu != null)
{
if (IsNumber(CurrentPageValue))
{
PageValue = Convert.ToDouble(CurrentPageValue.Trim());
Init_Data(PageValue,Qu);
}
else
{
PageValue = 1;
Init_Data(PageValue,Qu);
}
}
else
{
ExecuteError();
}
}
private void Init_Data(Double CurrentPage,string QuKey)
{
try
{
string ConStr = GetConnectionString();
string sql1 = "select id,title,date from table1 where title like '%" + QuKey + "%' order by date desc";
string sql2 = "select sid,type,title,content,date from table2 where type like '%" + QuKey + "%' or title like '%"+QuKey+"%' or content like '%"+QuKey+"%' order by date desc";
string sql3 = "select subid,text1,text2,date from table3 where text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' order by date desc";
string sql4 = "select RequestId,headIntro,HeadInfo,HeadCode,Text1,Text2,Text3,ItemText From table4 where headintro like '%" + QuKey + "%' or HeadInfo like '%" + QuKey + "%' or HeadCode like '%" + QuKey + "%' or text1 like '%" + QuKey + "%' or text2 like '%" + QuKey + "%' or text3 like '%" + QuKey + "%' or itemtext like '%" + QuKey + "%' order by date desc";
//expmale for test,i have 17 tables,so write some table do test
System.Data.DataTable ResultsTable = new System.Data.DataTable();
ResultsTable.Columns.Add("id", System.Type.GetType("Int32"));
ResultsTable.Columns.Add("title", System.Type.GetType("String"));
ResultsTable.Columns.Add("linkurl", System.Type.GetType("String"));
ResultsTable.Columns.Add("someIntro", System.Type.GetType("String"));
ResultsTable.Columns.Add("date", System.Type.GetType("DateTime"));
ResultsTable.Columns[0].Unique = true;
ResultsTable.Columns[0].AutoIncrement = true;
ResultsTable.Columns[0].AutoIncrementStep = 1;
ResultsTable.PrimaryKey = new System.Data.DataColumn[] { ResultsTable.Columns[0] };
System.Data.SqlClient.SqlConnection SearchConnect = new System.Data.SqlClient.SqlConnection(ConStr);
System.Data.SqlClient.SqlDataAdapter ada1 = new System.Data.SqlClient.SqlDataAdapter(sql1,SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada2 = new System.Data.SqlClient.SqlDataAdapter(sql2, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada3 = new System.Data.SqlClient.SqlDataAdapter(sql3, SearchConnect);
System.Data.SqlClient.SqlDataAdapter ada4 = new System.Data.SqlClient.SqlDataAdapter(sql4, SearchConnect);
System.Data.DataSet Ds = new System.Data.DataSet();
SearchConnect.Open();
ada1.Fill(Ds, "table1");
ada2.Fill(Ds, "table2");
ada3.Fill(Ds, "table3");
ada4.Fill(Ds, "table4");
string ReTitle = "";
string ReUrl = "";
string ReIntro = "";
DateTime ReDate = System.DateTime.Now;
if (Ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[0].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[0].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url1";
ReIntro = Ds.Tables[0].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
if (Ds.Tables[1].Rows.Count > 0)
{
for (int i = 0; i < Ds.Tables[1].Rows.Count; i++)
{
System.Data.DataRow TempRow;
TempRow = ResultsTable.NewRow();
ReTitle = Ds.Tables[1].Rows[1].ToString();
//url need check before count get detail
//for test temp defined
ReUrl = "url2";
ReIntro = Ds.Tables[1].Rows[1].ToString();
ReDate = Convert.ToDateTime(Ds.Tables[0].Rows[2]);
TempRow[1] = ReTitle;
TempRow[2] = ReUrl;
TempRow[3] = ReIntro;
TempRow[4] = ReDate;
ResultsTable.Rows.Add(TempRow);
}
}
//........loop to table end
//................................
Ds.Dispose();
ada1.Dispose();
ada2.Dispose();
ada3.Dispose();
ada4.Dispose();
SearchConnect.Dispose();
if (SearchResults.Rows.Count > 1)
{
DisplayData(CurrentPage, QuKey, SearchResults);
}
else
{
SearchResults.Dispose();
ExecuteError();
}
}
catch
{
ExecuteError();
}
}
private void DisplayData(Double PageNumber, string Keywords, System.Data.DataTable CurrentData)
{
Double TotalPage = 0;
Double TotalRow = 0;
Double TempPage1 = 0;
decimal TempPage2 = 0;
int StartValue = 0;
int EndValue = 0;
int PageSize = 30;
TotalRow = CurrentData.Rows.Count;
TempPage1 = TotalRow/PageSize;
TempPage2 = Convert.ToDecimal(TotalRow / PageSize);
if (Convert.ToDouble(TempPage2 - TempPage1) > 0)
{
TotalPage = TotalRow / PageSize + 1;
}
else
{
TotalPage = TotalRow / PageSize;
}
if (TotalPage < 1) { TotalPage = 1; }
if (PageNumber > TotalPage) { PageNumber = TotalPage; }
if (PageNumber == TotalPage)
{
EndValue = TotalRow;
}
else
{
EndValue = PageNumber * PageSize;
}
StartValue = PageNumber * PageSize - PageNumber;
for (int j = StartValue; j < EndValue; j++)
{
System.Web.UI.WebControls.TableRow Tr = new TableRow();
System.Web.UI.WebControls.TableCell Td1 = new TableCell();
System.Web.UI.WebControls.TableCell Td2 = new TableCell();
System.Web.UI.WebControls.TableCell Td3 = new TableCell();
Td1.Controls.Add(new LiteralControl(CurrentData.Rows[j][1].ToString()));
Td2.Controls.Add(new LiteralControl(CurrentData.Rows[j][2].ToString()));
Td3.Controls.Add(new LiteralControl(CurrentData.Rows[j][3].ToString()));
Tr.Cells.Add(Td1);
Tr.Cells.Add(Td2);
Tr.Cells.Add(Td3);
SearchResults.Rows.Add(Tr);
}
System.Web.UI.WebControls.TableRow StateTr = new TableRow();
System.Web.UI.WebControls.TableCell StateTd = new TableCell();
StateTd.ColumnSpan = 3;
if (PageNumber - 1 > 0)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber - 1) + "&qu="+Keywords+">prev</a>"));
}
if (PageNumber !=TotalPage)
{
StateTd.Controls.Add(new LiteralControl("<a href=default.aspx?page=" + Convert.ToString(PageNumber + 1) + "&qu="+Keywords+">Next</a>"));
}
StateTr.Cells.Add(StateTd);
SearchResults.Rows.Add(StateTr);
CurrentData.Dispose();
SearchResults.Dispose();
//rows count > 200,runtime at 1.6 seconds left,
//rows count > 1000,runtime at 8 seconds left,
//rows count >10000,runtime at 12 seconds left
//execute speed is very bad,so i want use sql stored procedure search,but i don't write t-sql,hope friends hope me,thanks
}
private void ExecuteError()
{
SearchResults.Controls.Add(new LiteralControl("we're sorry,we unable find even contain your key data,please try other keywords.suppot to ...."));
SearchResults.Dispose();
}
private bool IsSecure()
{
if (!Page.Request.IsSecureConnection)
{
return true;
}
else
{
return false;
}
}
private bool IsNumber(string Restr)
{
try
{
if (Restr != null)
{
string TempStr = Restr.Trim();
if (TempStr != "")
{
System.Text.RegularExpressions.Regex MyTry = new Regex("@[0-9]*$");
if (MyTry.IsMatch(TempStr))
{
Double valueR;
Double = Convert.ToDouble(TempStr);
if (valueR >= 1 && valueR <= 10000)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
else
{
return false;
}
}
catch
{
return false;
}
}
private string GetConnectionString()
{
try
{
string ConnectionString = "datasource=......,initial catalog=databasename,trusted_connection=yes;integrate security info=true;";
return ConnectionString;
}
catch
{
return "!";
}
}
private string ReplaceStr(string k)
{
try
{
if (k != null)
{
string r = k.Trim();
if (r != "")
{
r = r.Replace("~", "");
r = r.Replace("!", "");
r = r.Replace("@", "");
r = r.Replace("#", "");
r = r.Replace("$", "");
r = r.Replace("%", "");
r = r.Replace("^", "");
r = r.Replace("&", "");
r = r.Replace("*", "");
r = r.Replace("(", "");
r = r.Replace(")", "");
r = r.Replace("\", "");
r = r.Replace("|", "");
r = r.Replace("[", "");
r = r.Replace("]", "");
r = r.Replace("{", "");
r = r.Replace("}", "");
r = r.Replace(":", "");
r = r.Replace(";", "");
r = r.Replace("'", "");
r = r.Replace("", "");
r = r.Replace("<", "");
r = r.Replace(">", "");
r = r.Replace("!", "");
r = r.Replace(",", "");
r = r.Replace(".", "");
r = r.Replace("?", "");
r = r.Replace("/", "");
r = r.Trim();
return r;
}
}
else
{
return "";
}
}
catch
{
return "";
}
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Do Search SQL Server Page</title>
</head>
<body>
<asp:Table runat="server" ID="SearchResults">
</asp:Table>
</body>
</html>
MyTest:
SQL SERVER 2005+WINDOWS SERVER 2003+.NET 2.0
hi friends,
i need the steps for full text search with more than one tables in single database. I know the steps for full text search by single table in single database.
Thanks in advance
I have a string which I need to know where it came from in a database.I don't want to spend time coding this so is there a ready made scriptwhich takes a string as a parameter and searches all the tables whichcontain varchar type columns and searches these columns and indicate whichtables contain that string?Full text search is not enabled.--Tonyhttp://dotNet-Hosting.com - Super low $4.75/month.Single all inclusive features plan with MS SQL Server, MySQL 5, ASP.NET,PHP 5 & webmail support.
View 1 Replies View RelatedI need to look at all tables in a database that has a column name of GEO
Then look for all values in each table where the GEO value is NULL and delete each of the records found...
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies View RelatedOur clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....