Find the Real Column Name of an Alias Used in a View

Find the real column name of an alias used in a view?

Given this view:

CREATE VIEW viewTest
AS
SELECT
    books.id,
    books.author,
    Books.title AS Name
FROM
    Books

What I can see you can get the columns used and the tables used by doing this:

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS UsedColumns 
WHERE UsedColumns.VIEW_NAME='viewTest'

SELECT * 
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE AS UsedTables 
WHERE UsedTables.VIEW_NAME='viewTest'

This is for sql server 2005+. See reference here

Edit

Give the same view. Try this query:

SELECT
    c.name AS columnName,
    columnTypes.name as dataType,
    aliases.name as alias
FROM 
sys.views v 
JOIN sys.sql_dependencies d 
    ON d.object_id = v.object_id
JOIN .sys.objects t 
    ON t.object_id = d.referenced_major_id
JOIN sys.columns c 
    ON c.object_id = d.referenced_major_id 
JOIN sys.types AS columnTypes 
    ON c.user_type_id=columnTypes.user_type_id
    AND c.column_id = d.referenced_minor_id
JOIN sys.columns AS aliases
    on c.column_id=aliases.column_id
    AND aliases.object_id = object_id('viewTest')
WHERE
    v.name = 'viewTest';

It returns this for me:

columnName  dataType  alias

id          int       id
author      varchar   author
title       varchar   Name

This is also tested in sql 2005+

Get column or alias name from a provided query in Oracle sql

There is a way to get all the column names of a query, using dbms_sql.describe_columns2. But, it has to be done in PL/SQL.

For example,

I want to get the list of all columns of the following SQL:

SELECT emp.empno, emp.ename, dept.deptno 
FROM emp 
INNER JOIN dept 
ON emp.deptno = dept.deptno

The following anonymous block would list down all the column names in the exact order they are in the select list:

SQL> set serveroutput on
SQL> DECLARE
  2    l_cursor NUMBER := dbms_sql.open_cursor;
  3    l_ignore NUMBER;
  4    l_desc dbms_sql.desc_tab2;
  5    l_cnt NUMBER;
  6  BEGIN
  7    dbms_sql.parse( l_cursor, 'SELECT emp.empno, emp.ename, dept.deptno
  8  FROM emp
  9  INNER JOIN dept
 10  ON emp.deptno = dept.deptno', dbms_sql.native );
 11    dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
 12    FOR i IN 1 .. l_cnt
 13    LOOP
 14      dbms_output.put_line( 'Column ' || i || ' is "' || l_desc(i).col_name || '"' );
 15    END LOOP;
 16    dbms_sql.close_cursor( l_cursor );
 17  END;
 18  /
Column 1 is "EMPNO"
Column 2 is "ENAME"
Column 3 is "DEPTNO"

PL/SQL procedure successfully completed.

SQL>

It would also give you the ALIASES for the column names as well:

SQL> DECLARE
  2    l_cursor NUMBER := dbms_sql.open_cursor;
  3    l_ignore NUMBER;
  4    l_desc dbms_sql.desc_tab2;
  5    l_cnt NUMBER;
  6  BEGIN
  7    dbms_sql.parse( l_cursor, 'SELECT emp.empno employee_id, emp.ename employee_name, dept.deptno department_no
  8  FROM emp
  9  INNER JOIN dept
 10  ON emp.deptno = dept.deptno', dbms_sql.native );
 11    dbms_sql.describe_columns2( l_cursor, l_cnt, l_desc );
 12    FOR i IN 1 .. l_cnt
 13    LOOP
 14      dbms_output.put_line( 'Column ' || i || ' is "' || l_desc(i).col_name || '"' );
 15    END LOOP;
 16    dbms_sql.close_cursor( l_cursor );
 17  END;
 18  /
Column 1 is "EMPLOYEE_ID"
Column 2 is "EMPLOYEE_NAME"
Column 3 is "DEPARTMENT_NO"

PL/SQL procedure successfully completed.

SQL>

Since you are using SELECT *`, you could also list down the column names from [DBA|ALL|USER]_TAB_COLUMNS:

SQL> SELECT column_name FROM user_tab_columns WHERE table_name IN ('EMP','DEPT');

COLUMN_NAME
--------------------------------------------------------------------------------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
DEPTNO
DNAME
LOC

11 rows selected.

This is only valid since you are using SELECT *, else you need to use the anonymous block as I have shown above.

Alias for column name on a SELECT * join

It looks like you have to alias you'r column names with aliases.

SELECT client.column1 as col1, client.column2 as col2, person.column1 as colp1 FROM client INNER JOIN person ON client.personID = person.personID

Of course, replace the column names into the real column names as use more appealing aliases

Let us know if it helps

UPDATE #1

I tried creating 2 tables with sqlfiddle in mySQL 5.5 and 5.6

see link : http://sqlfiddle.com/#!9/e70ab/1

It works as expected.

Maybe you could share you tables schema.

Here's the example code :

CREATE TABLE Person
(
personID int,
  name varchar(255)
);

CREATE TABLE Client
(
ID int,
  name varchar(255),
personID int
);

insert into Person values(1, 'person1');
insert into Person values(2, 'person2');
insert into Person values(3, 'person3');

insert into Client values(1, 'client1', 1);
insert into Client values(2, 'client2', 1);
insert into Client values(3, 'client1', 1);

SELECT * FROM client 
INNER JOIN person 
ON client.personID = person.personID;

Get alias column name of procedure in SQL

sp_depends stored procedure is used to

Displays information about database object dependencies, such as the views and procedures that depend on a table or view, and the tables and views that are depended on by the view or procedure.

So it correctly identifies all the columns that your stored procedure depends on. E_ACTIVE is an alias, it's not a database object, so there cannot be a dependency on it.

---

You can also check this question and its answer: Retrieve column definition for stored procedure result set to see how you can get the metadata about a stored proc.

---

Related Topics