Finding Column Name Associated With View

Apr 30, 2007

I 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

View 5 Replies


ADVERTISEMENT

Finding A View Or Sp

Mar 19, 2004

According to a developer that worked on my db, there is a view :
xvw_CheckVoucherReport

I cant see it is there any way i can find out it exsist and if so what database on my server it is in?

View 4 Replies View Related

Finding A Column ??????

May 2, 2008

Hi,
How to find if a column exists in Database Table ?

thanx in advance

View 6 Replies View Related

Finding Tables Having A Particular Column

May 19, 2008

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

View 6 Replies View Related

Finding A Column In A Database

Jul 23, 2005

Hi all,How do I find all tables containing a column (say a column includingthe string 'value')?ThanksBruno

View 2 Replies View Related

Finding All References To A Column

Jul 20, 2005

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

Error Finding Identity Column

Jan 13, 2008

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

View 3 Replies View Related

Finding Max Date And Populate New Column

Jan 7, 2014

Aim – 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

View 3 Replies View Related

Finding Dashes/hyphens In A Column.

Jul 23, 2005

Hi,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 Related

Data Access :: Finding Min Value Corresponding To Other Column

Aug 12, 2015

Query :-

 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.

View 3 Replies View Related

Finding The Difference Between Two Column From Two Different Datasets

Nov 21, 2007

Hi

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?

View 1 Replies View Related

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

View 1 Replies View Related

Finding UNIQUE Values In Only One Column

Jan 28, 2008

I 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

View 4 Replies View Related

Finding Matching Data In One Column That Does Not Match In Another

Nov 18, 2005

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

View 2 Replies View Related

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

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

View 3 Replies View Related

T-SQL (SS2K8) :: Finding Rows From CSV Column With CSV Parameter

Nov 2, 2015

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

View 9 Replies View Related

Finding Stored Procs That Ref/Update A Column

Jul 23, 2005

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

Finding Logins That Have GRANT CONTROL And GRANT VIEW DEFINITION

Jul 21, 2015

Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?

GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda

View 5 Replies View Related

Create Multi Column View From Single Column Data

Jan 9, 2008

I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.

data_table
product_code month value
350 1 10
350 2 20
350 3 30

product_table
product_code profit_center
350 4520

result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 20 30

My current query gives the following result
result_view

product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30

Any direction toward a solution would be appreciated. Am using SS2005.

View 5 Replies View Related

Finding The Highest Or Largest Number In A Column Problem

Dec 19, 2006

Example 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?

View 3 Replies View Related

TSQL Script For Finding Column Names In Stored Procedures

Feb 23, 1999

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

Finding Value Of One Column Associated With The Max Value Of Another Column

Feb 4, 2008

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

View 9 Replies View Related

SQL Search :: Query For Finding Two Columns Data In Third Column And Getting Data From It?

Jul 6, 2015

getting 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

View 4 Replies View Related

How To Add An Identity PK Column To A View

Nov 16, 2007

For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK. 
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList. 
 I am wondering how can I add an identity PK to my view?
TIA,Jeffrey

View 1 Replies View Related

Compute Column In View...

Mar 11, 2006

I have a SQL table that consists of columns A, B and C.  I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column.  This computed column has the following logic:
If B is blank or null then  NewColumn = A + ' - ' C
else
 NewColumn = A + ' - ' + B
I am just wondering how the SQL statement should look like....
 

View 2 Replies View Related

View Changes Data In A Column

Sep 17, 2001

Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.

TIA
Jennifer

View 2 Replies View Related

Computed Column Of A View

Jun 11, 2003

I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:

View
====
Col1: Unit = get_number_of_units()
Col2: MV = get_number_of_units() * get_unit_value()

I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?

Is get_unit_value() being called if I do Select Col1 from View?

Thanks.

View 1 Replies View Related

MS SQL View The Column Entries

Oct 25, 2006

I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.

View 2 Replies View Related

How To Add A Calculated Column In A View

Apr 21, 2008

*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns

create view vw1
as
select tab.col1,tab.col2 from
from tab

and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false

thanx

View 2 Replies View Related

Creating A View With A New Column

Jan 31, 2014

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders

View 4 Replies View Related

Can Reference A Column In A View

Aug 14, 2014

how can i reference a column in a view.

My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.

View 2 Replies View Related

Temp Column In VIEW

Sep 27, 2005

Not sure if I am approaching this correctly. I can get name, address, zip code from a table via a VIEW. But the problem is I need to create another column called "status" in the same view that is based on "customer_end_date" if end_date> GETDATE() then 'P'(pick-up) and end_date< GETDATE () then 'I'(install). Using a stored procedure this is easy...but how would I do this in a VIEW?

View 6 Replies View Related

Add Column To A View - RESOLVED

May 3, 2006

I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?

View 2 Replies View Related







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