Finding A Column ??????
May 2, 2008Hi,
How to find if a column exists in Database Table ?
thanx in advance
ADVERTISEMENT
Finding Tables Having A Particular Column
May 19, 2008Hi All,
How to find all the tables of a database containing a column . for example,how list all the tables of employee database having employeeid ?
Cheers,
Mathi
India.
Finding Column Name Associated With View
Apr 30, 2007I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.
TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --
___________________________________________________
--just example
go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee
go
go
create view v_PersonalD
as
select SSN from Employee
Finding A Column In A Database
Jul 23, 2005Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno
View 2 Replies View RelatedFinding All References To A Column
Jul 20, 2005I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
View 1 Replies View RelatedError Finding Identity Column
Jan 13, 2008I'm using SQL 2005 Server Management Studio -
I go to the Northwind database - new query window and paste this code, I just found on this forum, to find the Identity column:SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Fred' AND C.status = 128 ORDER BY C.colid SELECT C.name AS Colname, UPPER(U.name) AS Coltype, C.status AS Cstatus, C.ColId, C.Id AS Cid, C.length, C.xprec, C.xscale FROM syscolumns C JOIN sys.tables O ON C.id = O.object_id JOIN systypes U ON C.xusertype = U.xusertype WHERE O.name = 'Products' AND C.status = 128 ORDER BY C.colid
However, I get an error message saying:Invalid object name 'sys.tables'.
What might I be missing here?
Finding Max Date And Populate New Column
Jan 7, 2014Aim – Find the latest #Account.[Last_Post_Date] for the Fdmsaccountno and populate the latest date for that Parentid. In a new column Called “Parent Last Post Date"
Current results
FdmsaccountnoParentidLast_Post_Date
87802012188487823334288920140105
87802012788187823334288920140103
87802012888987823334288920131231
87870180988087823334288920131217
87802012088687823334288920131204
87823334288987823334288920131201
87870180888287823334288920131130
87802011588687823334288920131120
87875705088487823334288920131011
Desired results
FdmsaccountnoParentidLast_Post_DateParent Last Post Date
8780201218848782333428892014010520140105
8780201278818782333428892014010320140105
8780201288898782333428892013123120140105
8787018098808782333428892013121720140105
8780201208868782333428892013120420140105
8782333428898782333428892013120120140105
8787018088828782333428892013113020140105
8780201158868782333428892013112020140105
8787570508848782333428892013101120140105
My query is
select
#Account.Fdmsaccountno,
#Account.Parentid,
#Account.[Last_Post_Date],
from #Account
inner join [Dim_Outlet] on #account.FDMSAccountNo = [Dim_Outlet].FDMSAccountNo
where #Account.Parentid = '878233342889'
order by [Last_Post_Date] desc
Finding Dashes/hyphens In A Column.
Jul 23, 2005Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
View 5 Replies View RelatedData Access :: Finding Min Value Corresponding To Other Column
Aug 12, 2015Query :-
SELECT
MAX(TIME_VAL) AS Duration1 ,
MIN(TIME_VAL) AS Duration2 ,
-- VALUE OF LATITUDE CORRSPONDING TO MAX(TIME_VAL)
-- VALUE OF LATITUDE CORRSPONDING TO MIN(TIME_VAL)
(Select LONGITUDE from (select LONGITUDE , row_number()
[Code] ....
I am not getting Correct values for StartLongitude , EndLongitude. How can i achieve this to get the val of latitude corssponding to max/ min of time_val column.
Finding The Difference Between Two Column From Two Different Datasets
Nov 21, 2007Hi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
Finding Max/Min Value In A Column When Row Data Repeat
Feb 13, 2008
I have a table. In that table I have a list by student number that lists the entry dates into a particular grade. When trying to list only the first time entered, there is no unique way to identify one row from another other than the date. Is there a way to use max or min to only pull one date per student number? I have done a series of case when statements and I am able to get it down to 1 to 2 entries per student number, but I need to get it down to only 1 date per student number.
Thank you for your help
SELECT DISTINCT mx.stu_num,
CASE WHEN er.STU_NUM = ep.STU_NUM
THEN er.enterdater
ELSE CASE WHEN ea.STU_NUM = ep.STU_NUM
THEN ea.enterdatea
ELSE CASE WHEN eb.STU_NUM = ep.STU_NUM
THEN ep.enterdatep
ELSE eb.enterdateb
END
END
END AS entrydate
FROM dbo.mx AS mx LEFT OUTER JOIN
dbo.v_EntryDate9_R AS er ON mx.stu_num = er.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_P AS ep ON mx.stu_num = ep.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_A AS ea ON mx.stu_num = ea.stu_num LEFT OUTER JOIN
dbo.v_EntryDate9_B AS eb ON mx.stu_num = eb.stu_num
Finding UNIQUE Values In Only One Column
Jan 28, 2008I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
Finding Matching Data In One Column That Does Not Match In Another
Nov 18, 2005I have a table that stores part numbers and manufactuers. Somehow this table has become corrupt showing different manufacturers with the same part numbers.
I know this will take a bit of manual digging to fix, but I want to find a way to pull all rows that have the same part number that have different manufacturers, or just pull up any "duplicate" part numbers and I can determine what is right or wrong as far as the manufactuers and make those changes.
I have tried this, but it does not seem to want to work.
Code:
Select * from my_table
Where partnumber = (select partnumber from my_table) and compName <> (select compName from my_table)
I have tried other variations of the same, but nothing seems to want to show me just the items that have the same part numnbers and different manufacturers. I do not care if there are duplicates of the same part number/manufacturer entries, just if the part number is duplicated where the manufacturers are not the same.
These are the rows I want to edit and group by part number. I have almost a million rows of entries and this is not something I want to go through row by row. :-P
Any ideas?
SQL Server 2008 :: Finding Column Within All Tables In DB
May 1, 2015I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;
T-SQL (SS2K8) :: Finding Rows From CSV Column With CSV Parameter
Nov 2, 2015I have a split string function that will take a comma delimited string and give back a table with all the values.I have a table that has a column with a comma delimited comma delimited list of states.
I can use a where clause to find one state in the table (such as all records that have CA in the states string).But need to find out how to find all the rows that have all or any of the states from a comma delimited parameter.Here is the schema
CREATE FUNCTION [dbo].[split] (@list nvarchar(MAX))
RETURNS @tbl TABLE (svar nvarchar(10) NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
[code]....
Finding Stored Procs That Ref/Update A Column
Jul 23, 2005Could any suggest to me a good way to programmatically identify which SPsupdate a database column. I would like to create a cross reference for ourdatabase.
View 5 Replies View RelatedFinding The Highest Or Largest Number In A Column Problem
Dec 19, 2006Example data
CA1000
CA10001
CA10002
CA10003
CA11597
CA11603
CA1001
CA998
CA999
As you can see, CA11603 is the largest number in this list.
When I try the follow sql code,
SELECT
MAX([MyCode])
FROM
[MyTable]
WHERE (SUBSTRING([MyCode], 1, 2) = 'CA')
The largest number comes back as CA997
When I try
MAX([MyCode])
FROM
[MyTable]
WHERE [MyCode] LIKE 'CA%'
The largest number comes back as CA997
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA') ORDER BY Schoolmastercode
The largest comes back as CA10001
When I try....
SELECT
TOP 1 (SchoolMasterCode)
FROM
SchoolMaster
WHERE (SUBSTRING(SchoolMasterCode, 1, 2) = 'CA')
The largest comes back as CA1278
What am I doing wrong?
TSQL Script For Finding Column Names In Stored Procedures
Feb 23, 1999Does anyone have a TSQL utility (that they can share) that recursively searchs sysobjects for a matching input parameter string(for instance column name) for stored procedure object properties that returns the stored procedure name?
View 1 Replies View RelatedFinding Value Of One Column Associated With The Max Value Of Another Column
Feb 4, 2008I've got a rowset (intermediate result in a large query) that's equivalent to this table:
create table T (
K bigint,
V bigint,
W tinyint,
primary key(K,W)
)
What I want is "for each K, find the V associated with the largest W". Or,
select K,V from T t1 where W = (select max(W) from T t2 where t2.K = t1.K)
What I'd like is a solution that does not involve a self-join on the table (which the sub-query effectively is). The rowset can be sorted by (K asc, W desc), in which case I'm looking for the first (K,V) pair for each unique value of K.
In the larger query, T could have 100's of thousands of rows, and as I mentioned, T is the result of another query on my base tables. I can express T as a CTE easily enough, but the resulting query plan (using the sub-select, as above) ends up performing the query that generated T repeatedly in order to implement the self join.
I can save T into a temp table - in fact, that's what I'm doing now - but I'd rather avoid using a temp table if possible. This query needs to be of the highest possible performance, so if there's a no-temp-table, no-self-join solution, I'm all ears!
I could imagine making a CLR UDF that does the filtering that I want, but I'm not convinced that it'd be a good choice - any insights on whether that's worth trying would be appreciated as well.
SQL Search :: Query For Finding Two Columns Data In Third Column And Getting Data From It?
Jul 6, 2015getting result as given below.
Input:
id Name Data
101 AA PQ102BBRAJAKIRANBUBLU
102 BB RS101AAEROJUCHALABAGUNDI
103 CC TU104GGANDICANKILLANYONE
OUTPUT:
id Name Data
101 AA 101AAEROJUCHALABAGUNDI
102 BB PQ102BBRAJAKIRANBUBLU
103 CC
Finding 'Error Column' Causing Error
Apr 16, 2007Good Morning,
Am I new at this so please bear with me. I searched the site and found threads on identifying the meaning of an error code and have instituted that scripting. It is telling me that I have a 'The data value cannot be converted for reasons other than sign mismatch or data overflow.' error. It identifies the colum as 5301.
I searched the threads and found one telling me I could do an advanced editor search on the lineage id to find the column being referenced. I have traced the entire process using this information and cannot find a reference lineage id of 5301. Was that thread information accurate, and if so what do I do now? If not, can someone tell me how the heck I find column 5301? I cannot load my table from a flat file because of this.
Work so far:
I have checked for integrity between column definitions and source flat file. I applied derived column changes to make the data transform to the appropriate data type/order where necessary. This part works without error. (Or seems to, there is no error output from this piece.) It is only on the final attempt to load that the process errors with these messages.
Thank you in advance to anyone who can help me.
Rog
Integration Services :: Add New Column Based On Existing Column Using Derived Column Task?
Jul 28, 2015I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Finding Something In SQL
Jun 22, 2007Hello,
I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?
Any help would be greatly appreciated.
Finding Right Row
Mar 11, 2008i have
create table test
(id_text int
,text_name varchar(10)
,id_author int)
insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)
select id_text, id_author from test
and i need to get out only id_text with the first author.
any solutions for sql server 2000?
thank you
Finding The Max Value
Aug 16, 2006I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.
I tried the following expression:
=iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")
but this only works if the first day is the maximum value.
5/21/2006
?????
254
Sun, 5/21/2006
1000
147
Mon, 5/22/2006
1400
254
Tue, 5/23/2006
1830
141
Wed, 5/24/2006
1830
107
Thu, 5/25/2006
0830
210
Fri, 5/26/2006
1900
36
Sat, 5/27/2006
1000
149
Any ideas are greatly appreciated!
Finding The Next Record
Apr 26, 2007I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not
Row1 Date1,falseRow2 Date2,trueRow3 Date3,falseRow4 Date4,false
so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow
Finding Data
Feb 2, 2008I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike
Finding SQL Triggers
Feb 29, 2008I have a table TEST.
I want to find the triggers which use this table test.
For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST,
I want to find this particular trigger.
Any ideas?
I am using SQL server 2000
thank you
Finding The Unique....
Aug 28, 2001hai
I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table
structure.
Thanks in advance
satya
Finding Duplicates In SQL
Jul 26, 2002I have a customer database with the following structure:
Dept ID (int)
Section (varchar)
I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.
Example Data:
Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance
The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.
I am not in control of the data design and can't create a bitwise field to achieve this.
tia,
Steve
Finding Suid
Oct 18, 1999How can I find out a user's suid or login name from within a stored procedure?
View 1 Replies View RelatedFinding The First Distinct Value
Sep 8, 2004Hi
My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.
Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.
How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)
Finding Relationships
Sep 23, 2005Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer..
I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.
EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.
Thanks
Steve