i like to write a sql query to find out similar columns in different tables.
i have something like this. but my logic is wrong.
select *
from information_schema.columns a
join information_schema.columns b
on a.column_name = b.column_name
and a.table_name <> b.table_name
I have two tables in the same SQL database. Both have a similar numericfield. One field has the same number as the other field but is prefixedwith various letters. I know how to use LIKE but won't know the partialstring I am looking for. I am trying to use LIKE '%' + Field A orsomething that will do this.Eg.Table 1 Field A is 'A12345"Table 2 Field B is '12345"I want to find every record in Table 1 Field A that contains the exactField B data.*** Sent via Developersdex http://www.developersdex.com ***
View 3 Replies View RelatedHi,I m searching for some Script / Function ... to find difference in datab/w 2 similar tables (exactly same fields structure) in sql 2000.plz update me asap !thanks in advance !
View 2 Replies View RelatedHi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:
TelNo1 TelNo2 TelNo3
555-44-33 555-43-88 555-46-89
into:
Tel_Number
555-44-33
555-43-88
555-46-89
I tried using the union statement but i just did'nt succed. Can anyone help me,please
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
I am trying to write an SQL command for my crystal report. I need to compare the same column in 3different tables & get the data from each table for only the matching data.. I understand I need to create a temporary table, get the data into it & then work around.. I am quite new to SQL.
Eg: Considering one customer account
Table 1
Cust.No Name Amt_Counter AmtPaid
123.456 sam 0 0
123.456 sam 1 50
Table 2
Cust.No Name Freq_Counter Frequency
123.456 sam 1 0
123.456 sam 2 15
[code]....
I am using this query to search checkbox values.
SqlCommand cmd = new SqlCommand("Select * from Details where Emp_Code in (" + selectedValues + ")", con);
I want to join a table called Materials to this now. Material table also has an Emp_Code column. How can I write a select sql query to fetch Emp_Code from both Details and Materials table.
SqlCommand cmd = new SqlCommand("select D.Emp_Code, M.Emp_Code from Details D, Materials M where D.Emp_Code = M.Emp_Code in (" + selectedValues + ")", con);
For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.
If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip
Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018
The query I am using
select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip
from Loan AS L
LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedHi is there any way to find no. of columns in a database table
like we can do that in Oracle by typing DESC tablename
San
Hi I want to find 3 different columns maximum values in one shot. Like I tried to use a reader to go through results but it kept coming back with index out of bounds. Right now to get it to work I got to use a ExecuteScalar() get the first columns max value and then open the connection again(since it seems after ExecuteScalar() it closes the connection) and then do the ExecuteScalar() again. Open the connection again and do the ExecuteScalar() again. Theres got to be a better way of doing this.
View 3 Replies View RelatedIm having trouble finding a solution to comparing columns to find the lowest value.
Example
Row 1
COL 1 = 10
COL 2 = 20
COL 3 = 60
COL 4 = 5
COL 5 = 35
I would want to return 5.
I tried to write a case statement such as below but am unable to use Logical Operators.
WHEN C1 <= C2 and C1<= C3 THEN C1
WHEN C2 <= C3 THEN C2
ELSE C3
any help would be great.
Thanks,
Nick
In every database we have 'sysobjects' table storing names of all objects(including all keys) in our data base. How can I find all columns, that the key is made of? Sorry about my english :-/
View 1 Replies View Related Can anyone pls help me to solve my problem in SSRS. Also I wanted to plot the 'Ratio' in to a chart against the 'Year'.
The scenario is:
Year Amount_A Amount_B Ratio(Amount_A/Amount_B)
05 x y x/y
06 a b a/b
07 m n m/n
Its very needy.
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?
TableName: EmployeeCloumns: EmployeeID EmployeeName If I do "Select * From Employee", is there a way to do something like this...
Loop through each result IterateThroughControls.ID &= dbTable & dbColumnName & "<br />"EndSo that the end result would assign ID values...EmployeeEmployeeIDEmployeeEmployeeName
Hi,
I have a sql query which gives me the result set with lots of columns and rows.
a b c allocated unallocated
- ------ 75458702 0484095809
------- 534534 8743857
------- 953459034 90584395
i have to find of which is the smallest number in both allocated and unallocated columns -
here in this case
it would be 534534.
how do i do this ?
Thanks
Hello,
I want to find the columns which execute updatetriggers. Is there in sqlserver a way to find out?
In Oracle you can say:
SELECTCOLUMN_NAME
FROMDBA_TRIGGER_COLS
WHERETRIGGER_OWNER = 'MyOwner'
AND TRIGGER_NAME = 'JobUpdate'
AND TABLE_NAME = 'Job'
AND COLUMN_LIST = 'YES'
I tried: EXEC sp_depends "dba.JobUpdate".
This says nothing about the updatecolumns.
Can anybody help?
thanx and greetz
C Dunnink
The Netherlands
I am trying to produce a report in Application express. I want to find records where there is a difference between 2 columns eg
Debt_amount - billed_amount
where debt >billed amount
I only want to see records where the debt is more than the amount billed
Dear experts,
how can i find the ntext datatype columns in a database?
please guide me
Dear Experts, i need one query to find all the indexed columns with table names ,column names and type of indexes....
i'm trying with sysindexes, but i was unable to finish it....i'm not clear about keys column in sysindexes...
please guide me.
thank you very much
I need to create an function similar to the "MATCH" function in Excelthat evaluates a number within a set of numbers and returns whetherthere is a match. I have put the example of what I see in excel in thecheck column. The "0" answer in the result column is in the fourthaccount in the list. Somehow I need to loop through the accountscomparing the result to the total and indicate a match in the checkcolumn. It wouldn't even need to tell me the row number; it could be a0 or 1.account total result check123770266.84124.2112377026131.050 412377026164.38-33.33123770260131.051237702678.7152.3412377167-31.34221.891237716731.34159.211237716738.55152 51237716731.34159.211237716715238.5512377167490.91-300.36123771670190.55123771670190.5512377167-31.3443.341237716731.34-19.341237716738.55-26.551237716731.34-19.3412377167152-14012377167490.91-478.9112377167012123771670121237736347.058412377363131.05012377363-45.38176.4312377363-47.05178.11237736347.0484.0112377363-47.04178.091237736347.058412377363541.11-410.06123773630131.0512377363672.15-541.11237750737.64152.91
View 3 Replies View RelatedFor example if we clearly define a foreign key in the master and child table as following script -
CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int CONSTRAINT fk_master_child
FOREIGN KEY (fkey) REFERENCES master(pkey))
We can find out the two tables reference relationship by looking at INFORMATION_SCHEMA tables.
However, if the two tables are created in this way €“
CREATE TABLE master(pkey int PRIMARY KEY, data varchar(10))
GO
CREATE TABLE child (fkey int)
Does any one know how to programming verify the actual reference relationship exists between pkey and fkey in these two tables?
Thanks
Is there a query I can write against an INFORMATION_SCHEMA or against the system tables to determine if a column is an identity column?
View 8 Replies View RelatedHi,
i have a problem where in i have to display the greatest marks scored by each student.
How can i do this?? Is there any built in TSQL function.
Rgds..,
Aazad
How do i Find all Columns that have Identity Property in a database. That is i will like to know all columns in a database that are identity columns.
I am using SQL Server 2005. Thanks
I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision
Table
IDCustomerRefnumnumberdate decision
16511114/17/2015Approved
16521125/1/2015Declined
16531216/10/2015Approved
16542116/15/2015Tentative
Expected
Customer 1 had a max of refnum of 2 and 1st one on number
IDCustomerRefnumnumberdate decision
16531216/10/2015Approved
16542116/15/2015Tentative
We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database. The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.
We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).
I googled, but I get only all the columns in tables of database.
I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded databases).
I have the following table:
f_namef_countryf_ID
ABCUS123
DEFGB123
ABCUS456
GHIGB789
etc.
I need to run a query to discover all instances where a f_name and f_country pair exists for more than one f_ID. ABC/US is one such example; IDs 123 and 456 have this pair.
CREATE TABLE XYZ
(
ID int DEFAULT 5000,
NAME VARCHAR(100)
)
I have many tables that have many columns with default values
Is there a way to get a listing of
TAB_NAME, COL_NAME, Data_type, Default_Value
How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3
Result:
Column1: Text2 New
Column3: Text4 Old Text3 New
How do i find indexes of the columns of all the tables of thedatbase...........most importantly in SQL server 2000Thanks a lot
View 3 Replies View Related