Post reply

INFORMATION_SCHEMA.VIEW_COLUMN_USAGE alias column name

  • September 15, 2005 at 3:33 am

    #165951

    hi

    i am using follwoing query

    SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE

    VIEW_NAME = 'invoices' ORDER BY 1

    to get all the tables and there columns that invoices(sql server -northwind database) is dependant on.

    now the problem is that for shippers its giving column as "CompanyName" for which alias of shippername is used

    is there a way to get both the acual columns and alias, actually we need to find how a view is dependant on tables and on which columns.

    any ideas??

    amrita

  • September 15, 2005 at 4:04 am

    #590053

    I've had a quick look, but can't find where this alias is appearing - please direct me to it!

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • September 15, 2005 at 4:16 am

    #590058

    this is the actual view used

    create view Invoices AS

    SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,

    Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,

    Customers.Region, Customers.PostalCode, Customers.Country,

    (FirstName + ' ' + LastName) AS Salesperson,

    Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,

    "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,

    "Order Details".Discount,

    (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight

    FROM Shippers INNER JOIN

    (Products INNER JOIN

    (

    (Employees INNER JOIN

    (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)

    ON Employees.EmployeeID = Orders.EmployeeID)

    INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)

    ON Products.ProductID = "Order Details".ProductID)

    ON Shippers.ShipperID = Orders.ShipVia

    so wen we use

    SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE

    VIEW_NAME = 'invoices' ORDER BY 1 on it

    it gives table name shippers and column name as companyname

    we need both the shippername and companyname

    ny suggesstions?

    amrita

  • June 7, 2007 at 2:54 am

    #711343

    Hello,

    Did anyone find a answer of that question?

     

    Regards Markus

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply