T-SQL (SS2K8) :: Search All Tables

Dec 19, 2014

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.

View 5 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Search All Tables For Primary Key Value?

Feb 26, 2015

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?

View 2 Replies View Related

T-SQL (SS2K8) :: FULL TEXT Search With CONTAINS

Oct 27, 2014

Example

Select *
from TableName Where Contains(ColumnToBeSearched, 'testtosearch')

QUESTION: Can the 'testtosearch' be another table field like ''' + <AnotherTBLField> + '''Is there anyway to make the population of 'testtosearch' to be based on another table field (say when you join to it)?If not what else can I do, another function maybe?

View 0 Replies View Related

T-SQL (SS2K8) :: Full-Text Search Over XML Columns

Jun 5, 2014

Are the XML tags searched, as well as, the contents within the tags, when an xml column is included in a full-text index? Or is it only the contents within the tags? Would I need to combine full-text search with XPath/XQuery to get at the actual tags?

View 0 Replies View Related

T-SQL (SS2K8) :: Isolating A List With Pattern Search

Jul 14, 2014

I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.

WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
UNION ALL
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
UNION ALL
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Search Query Including Security?

Jul 17, 2014

I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules.

Basically I have a massive custom search query which I now have filter even further. [URL] for previous discussions on this query, which I'm currently happy with).

so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.

each transaction can be see by

* Everyone If no record found in security table for transaction.

* If even just one transaction found, the below have permission to view it.

- Person who raised transaction

- person currently responsible for it.

- person currently working on it.

- everyone in the security table for this transaction.

- Admins for this Transaction Type.

So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.And I think I have them all EXCEPT someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.

The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.

ALTER PROCEDURE [dbo].[uspJobSearch]
@csType as nvarchar(20) = '-1',
@status as nvarchar(20) = '-1',
@startID as nvarchar(50) = '',
@endID as nvarchar(50) = '',
@complaintType as nvarchar(50) ='',

[code]....

View 0 Replies View Related

T-SQL (SS2K8) :: Search Through Rows With Specific Values

Sep 16, 2015

I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.

On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.

I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.

USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, OperationCodeVARCHAR(10) NOT NULL
, EmployeeCode VARCHAR(10) NOT NULL

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Search Special Characters In Column Of Table

Jul 14, 2014

I am using SQL Server 2008. In one of my table, one column has values like

"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"

So to fetch the data having only special characters in it, I used below query

Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].

View 2 Replies View Related

T-SQL (SS2K8) :: OR Query - Search For Items And Separate Each Word

Oct 17, 2014

I have this query currently:

select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup
from updatedb
left join opencall
on updatedb.callref=opencall.callref

where udindex = '0'
and suppgroup = 'SUPPORT'
and (updatetxt like '%' + @Word + '%')

And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.

They would like it to be more fuzzy with "and" and "or". How can I adapt this?

View 8 Replies View Related

T-SQL (SS2K8) :: Multiple Parameters Using Full Search Index?

Dec 1, 2014

Using a full search index with the following query works with just one parameter.

declare @P0 varchar(50) = '"First*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0)

How do I make it work if I have two parameters, while also protecting the parameters from injection attacks?

declare @P0 varchar(50) = '"First*"'
declare @P1 varchar(50) = '"Second*"'
SELECT *
FROM TableName
where contains ((Col1, Col2), @P0 AND @P1)

If there weren't parameters, you put single quotes around the '@P0 AND @P1' to get this query to work.

In addition, while "where contains((Col1,Col2),@P0) and contains((Col1,Col2),@P1)" works, it appears to increase the execution time.

View 0 Replies View Related

T-SQL (SS2K8) :: Search For A String In Expression By Number Of Its Appearance - Charindex

Apr 3, 2015

I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:

DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh',
@SearchString VARCHAR(10)= '_',
@OccuranceNumber SMALLINT = 1
DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0
WHILE @Sno < @OccuranceNumber BEGIN

[Code] .....

Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.

View 9 Replies View Related

T-SQL (SS2K8) :: Search Based On Table Name And Add Code To Existing Stored Procedures

Jun 30, 2014

Below is sample SQL:

SQL_1: Creates a database
SQL_2: Creates 2 stored procedures

Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID.

INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'SQL_1:
USE [master]
GO

[code]...

View 6 Replies View Related

T-SQL (SS2K8) :: Implementing Full Text Or Universal Search Facility In Website

Nov 6, 2014

How to implement a universal search in my site And it must be give result precised up to 90%.

Scenario is like that whenever customer come to our site he/she can search for example: Car parts

My Table structure is like below:

Column 1 Column 2 Column 3 Column 4 column 5 column 6 (Keyword)
Toyota camry 2000 wheel ABC123 Toyota camry 2000 wheel abc123
Toyota camry 2000 wheel cover 123 Toyota camry 2000 wheel cover 123

Now i have created a full text search index on column 6(keyword)

so whenever someone search exactly describe in column 6 it gives result

For ex: if i search (Toyota Camry wheel) with CONTAINS Option

Select * from mytable where Contains
(Keyword,'Toyota and Camry and wheel')

It gives me rows which contains Toyota Camry wheel and other rows which having wheel cover also. So i get multiple rows. Because of we have very large table which contains data in millions its not to feasible to have that thing.

another thing is :

if i search (Toyota Camry wheels) with CONTAINS Option
Select * from mytable where Contains
(Keyword,'Toyota and Camry and wheels')

It won't give any result back to me which is embarrassment for me. Because customer can search anything so if its not giving result its bad impression.

Also i tried to use FREETEXT option but it gives result in combination of all this word which i searched. so its huge amount of data in result.

View 2 Replies View Related

Search In All Tables

Oct 13, 2004

i have 13 table in my sql server nd they have no relation
how can i search a keyword in all tables?

View 2 Replies View Related

Search In All Tables

Aug 26, 2004

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

View 5 Replies View Related

Search Into Tables

Nov 13, 2007



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

View 4 Replies View Related

Sql Query To Search In Two Tables

Jun 25, 2007

 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.

View 3 Replies View Related

Search For Tables In Database

Jan 22, 2008

 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 Related

Search Tables And Get Columns

Nov 10, 2005

I 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 Related

Search All Columns Of All Tables

Feb 5, 2008

How to search all columns of all tables for a keyword?:o
that in MS sql

thanks in advance..

View 2 Replies View Related

Search Multiple Tables

Feb 14, 2006

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

View 7 Replies View Related

Search All Tables And Replace

Jul 23, 2005

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 Related

Search Query Between Two Tables

Jul 23, 2005

I'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 Related

T-SQL (SS2K8) :: How To Merge Two Tables

May 27, 2014

I've One table that contains all current appointments booked as below.

RNappt_idclinic_idderived_location_idprovider_idappt_date_timeappt_date_endtime
1102186011435/27/14 8:305/27/14 9:00 AM
2102196011435/27/14 9:305/27/14 10:00 AM
3102206011435/27/14 10:305/27/14 11:00 AM
4102216011435/27/14 14:005/27/14 2:30 PM
5102226011435/27/14 16:005/27/14 4:30 PM
6102236011435/27/14 16:305/27/14 5:00 PM

for Same Clinic,location and Provider there's a schedule data for same day 24 hours

having every five mintues as below:

clinic_idderived_location_idprovider_idstart_timeendtimeunblock
601143 8:01 8:040
6011438:05 8:100
601143
60114313:0113:041
601143
60114313:5514:001
601143 1
60114317:5518:000
60114323:550:001

for rows having no start and endtime assume it as regular intervals.

So i need to show available appointment with duration one hour with the available schedule which is for every five minutes

Like My first appointment for today starts at 8:30 but 8- 8:30 is unblock so there could be an appointment but as this chunk is less than 60 so need to create it

For Schedule table below is what i've to create for temporary basis as this will be available in nightly load in a table.

DECLARE @num int=5
,@LASTtime TIME =CAST('23:55' as TIME)
,@Time TIME =CAST('00:00' as TIME)
,@Timeprev TIME =CAST('00:00' as TIME)
WHILE ( @Time<>@LASTtime)
BEGIN

[code]....

View 8 Replies View Related

T-SQL (SS2K8) :: Joining On 3 Tables

May 19, 2015

I am having trouble with joining 3 tables.1 table is the primary call it jobs, second is material, third is called labor...job will have all jobs, SOMETIMES the job will have material, sometimes job will have ONLY labor, BUT most of the time it will have job, material and labor...

Column
job Labor Material
1 2 3
2 - 2
3 3 -
4 5 6

View 2 Replies View Related

T-SQL (SS2K8) :: Joining Two Tables

Jul 24, 2015

I've having difficulty joining two tables and getting the results I want. What I'm trying to accomplish is an Health Savings Account (HSA) upload file to go to our Insurance vendor. Here's a summary

Table1: PS_AL_CHK_DED

Description: This table stores all deductions in an employee's paycheck. I'm specifically looking for a field in that table called AL_DEDCD where it equals "H". This is the deduction from the employee's paycheck.

EXAMPLE:

> EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT
123456............6/30/2015..............H.............................100.00
123456............6/30/2015..............R..............................75.00
987654............6/30/2015..............H..............................50.00
987654............6/30/2015..............A..............................85.00
456123............6/30/2015..............H..............................200.00
456123............6/30/2015..............L...............................25.00


Table2: PS_AL_CHK_MEMO:

Description: This table stores the employer contribution based on their deduction. For example, we match 50% of their contribution. It is possible for the employee to have a deduction (stored in the ps_al_chk_ded table) but it is not possible for them to have an employer contribution (stored in ps_al_chk_memo) without a deduction (stored in the ps_al_chk_ded).

EXAMPLE:

>EMPLID..........................CHECK_DT........................MEMO_CD...................AL_AMOUNT
123456..............................6/30/2015........................H..................................50.00
123456..............................6/30/2015........................XX................................10.00
987654..............................6/30/2015........................H..................................25.00
987654..............................6/30/2015........................ZZ................................20.00
456123..............................6/30/2015........................MM. ............................40.00
456123..............................6/30/2015........................NN................................5.00

What I want in my output is a combination of these tables that looks at the employee in the ps_al_chk_ded table, takes only the records where al_dedcd = H and returns the corresponding employer contribution (memo_cd =H in al_chk_memo). So you almost have to join the two tables on all three of those fields and I can't get it to work. Here is what I would like the data to look like:

>> EMPLID...........CHECK_DT...........AL_DEDCD.............AL_AMOUNT(1)...............AL_AMOUNT(2)
123456............6/30/2015..............H.............................100.00.............................50.00
987654............6/30/2015..............H..............................50.00..............................25.00
456123............6/30/2015..............H..............................200.00............................0.00

This didn't seem like it was too complicated but no matter how I do it, I can never get the appropriate amount from the employer contribution. I attached a screenshot that shows my queries and result set. In the screenshot, it returned the employee deduction just fine but then returns 48.25 for the employer contribution. However, the MEMO_CD in that specific record is equal to T. In this case, it should return 0.00 for the employer contribution. If I try and add something like d.al_dedcd = c.memo_cd on the join, I barely get any results. The key is matching on those fields and then returning 0.00 when there is no record of MEMO_CD = H in the al_chk_memo table for the deduction in ps_al_chk_memo.

View 9 Replies View Related

How To Search In 2 Tables By Using SQL Stored Procedure??

Mar 7, 2008

Hello,
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

View 4 Replies View Related

How To Search For And Replace A Value In All Tables Using A Sql Script.

Mar 15, 2007

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

View 3 Replies View Related

Search Database For Tables With Specified Column Name

May 5, 2004

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

View 1 Replies View Related

SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase

Aug 9, 2006

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

View 3 Replies View Related

T-SQL (SS2K8) :: Compare Records In Tables?

Mar 3, 2014

There are four tables

1. Matter

MID, CID, RType

001, a, m
002, a, m
003, b, m
004, c, m

2. Category

CID. RType
a, T
b, T
c, T

3. Security assignmnet

RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04

4. Group

GID
g01
g02
g03
g04

I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"

In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"

How can I create qury to find all the possible record in the table #2?

View 9 Replies View Related

T-SQL (SS2K8) :: Get Difference Between Data In Two Tables

May 27, 2014

I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:

CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,

[Code] ....

Output should be like this

PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees

View 8 Replies View Related

T-SQL (SS2K8) :: Generating One Table From Two Tables

Jan 14, 2015

There are two tables as below:

Table 1
IDValue
F001A,B,C
F002B,C,D
F003A,C

Table 2
IDValue
D001A
D002B
D003C
D004D

what is best way to generate one table as below:

New table
F001D001
F001D002
F001D003
F002D002
F002D004
F002D003
F003D001
F003D003

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved