Is 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 RepliesIs there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
Currently we have requirement like below.
Input @Filename ="233-sssee-FILENAMETYPE@ssss.xml"
In the DB i have column values like below.
FileType Val
FILENAMETYPE Direct
Now my requirement is to search for FIleTYPE in above table by passing @Filename as parameter and that should return Val as response. How to write a search query for this type.
I need to categorize some rows of a column into specific type. Criteria for the category search is to find a string which is enclosed at both the ends "_"
Example:
_you_,
are_you_,
are_you_jack
But when I give condition like '%_you_% I end up getting records which are like "areyou_".
create table #test
(n varchar(30))
insert into #test values
('sql_2012'),
('ssis_msbi_'),
('ssrs_msbi'),
('ssasmsbi_')
select n,
case
when n like '%_msbi_%' then 'yes'
else 'no'
end as type
from #test
In the above query I need type as "yes" only for "ssis_msbi_". But the above query results with "yes" even for ssasmsbi_. What I need to do, to get just the data which has "_msbi_" in it.
Hi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.
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] ....
Is 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
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 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 RelatedAnyone 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 Relatedhi 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 RelatedHi,
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 RelatedThe recipe preparation instructions are stored in a table by RecipeID. The prep instructions are in a single VARCHAR(MAX) column and look something like this:
1. Boil Water
2. Add noodles
3. Add cheese sauce
4. Stir well
Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row. How would I accomplish this in T-SQL?
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
CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);
[Code] ....
Now I have to pass multiple param values to it. I was trying this but didnt get any success
exec
[SP_test]'LOC','LOP'
I have the following two tables....
tblTimeEntry
-entryID
-entryDate
-entryUser
-entryJob
-entryTask
-entryWeekNo
tblWagesWeeks
-weekID
-weekDay
-date
I want to select all of the date and weekDay values from tblWagesWeeks for a specific weekID. I also want to show all entries fromtblTimeEntry for the weekID when a record exists. If data does not exist in fromtblTimeEntry I want to display a blank entry but still need weekDay and date from tblWagesWeeks.
I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)
Later for some other purpose, downloaded the same day's data into five small tables.
Now when I compare the total row count of Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match.
So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID CustomerName VoucherType Outbound Inbound CustomerType
----------------------------------------------------------------------------------------------
1 ABC Sales 10000 0 Dealer
2 MNC Sales 9000 0 Dealer
3 MNC Sales 4000 0 Dealer
Table 2 - tblCreditors
ID Name OpeningBalance
----------------------------------------------------------------------------------------------
1 ABC 20000
2 MNC 15000
3 XBM 18000
4 XYZ 12000
best way to go about this. My query works great and displays the information I require.
EG all demographic for ED, IP & OP. However if a patient has a record in each of the tables then I get the same info multiple times.
What I'm trying to do is a Select Distint against the entire query (below).
eg Select Distinct * from all below.
DECLARE @StartDate datetime = '01 oct 2015';
DECLARE @EndDate datetime = '30 OCT 2015';
--***OUTPATIENT DQ QUERY***--
SELECT distinct
HEYNo,
NHSNo2,
NHSNo1,
[code]....
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
View 8 Replies View RelatedConditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.
View 13 Replies View RelatedThere are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.
If object_ID(N'Bezeichnungen') is not NULL
Drop table Bezeichnungen;
GO
create table Bezeichnungen
(
Artikelnummer nvarchar(18),
Artikelbezeichnung nvarchar(80),
Artikelgruppe nvarchar(13),
[code]...
I have a query that involves tables from 2 different databases. Using the query analyzer I can construct my query and it works great. Now I am trying to implement the query into my code and I am confused about I handle the connection string(s) since I am now using 2 different databases.
Can anybody help?
Cheers,
AzF
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.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.