I need the "Real" Table / Table Column names from a sql view

Advertisement

Firstly, we have a system with ~1000 tables and ~250 views.  We have field level security on the table columns (in our code)
Now we have a lot of views which select data from the tables, but I need to get the "Table Column Name" that is linked in the view. 
I know there are view columns that is not linked to a specific table column, (or concatenation of columns) so those columns can just return NULL.
From the sample you will see there is a view selecting data from another view.  I know it is not the best sql performance (execution path) but that is another topic on its own.
I went through a lot of the sys.* stored procs or sys.* views to try and figure out if there is a view that we can use to get the expected results.
here is the sql code:
if
Exists (select
* from
sys.all_objects
where name =
'AliasView2')
drop view dbo.AliasView2
if
Exists (select
* from
sys.all_objects
where name =
'AliasView1')
drop view dbo.AliasView1
if
Exists (select
* from
sys.all_objects
where name =
'Table4')
BEGIN
alter table dbo.Table4
DROP CONSTRAINT [FK_T4_T3]
alter table dbo.Table4
DROP CONSTRAINT [PK_T4_Constraint]
drop table dbo.Table4
END
if
Exists (select
* from
sys.all_objects
where name =
'Table3')
BEGIN
alter table dbo.Table3
DROP CONSTRAINT [FK_T3_T2]
alter table dbo.Table3
DROP CONSTRAINT [PK_T3_Constraint]
drop table dbo.Table3
END
if
Exists (select
* from
sys.all_objects
where name =
'Table2')
BEGIN
alter table dbo.Table2
DROP CONSTRAINT [FK_T2_T1]
alter table dbo.Table2
DROP CONSTRAINT [PK_T2_Constraint]
drop table dbo.Table2
END
if
Exists (select
* from
sys.all_objects
where name =
'Table1')
BEGIN
alter table dbo.Table1
DROP CONSTRAINT [PK_T1_Constraint]
drop table dbo.Table1
END
create
Table dbo.Table1
T1_PK        int
NOT NULL
Identity(1, 1)
CONSTRAINT [PK_T1_Constraint]
PRIMARY KEY (T1_PK),
T1_Field1    varchar
NULL,
T1_Field2    varchar
NULL,
create
Table dbo.Table2
T2_PK        int
NOT NULL
Identity(1, 1)
CONSTRAINT [PK_T2_Constraint]
PRIMARY KEY (T2_PK),
T2_Field1    varchar
NULL,
T2_Field2    varchar
NULL,
T2_FK        int
NOT NULL
CONSTRAINT [FK_T2_T1]
FOREIGN KEY (T2_FK)
REFERENCES dbo.Table1
(T1_PK)
create
Table dbo.Table3
T3_PK        int
NOT NULL
Identity(1, 1)
CONSTRAINT [PK_T3_Constraint]
PRIMARY KEY (T3_PK),
T3_Field1    varchar
NULL,
T3_Field2    varchar
NULL,
T3_FK        int
NOT NULL
CONSTRAINT [FK_T3_T2]
FOREIGN KEY (T3_FK)
REFERENCES dbo.Table2
(T2_PK)
create
Table dbo.Table4
T4_PK        int
NOT NULL
Identity(1, 1)
CONSTRAINT [PK_T4_Constraint]
PRIMARY KEY (T4_PK),
T4_Field1    varchar
NULL,
T4_Field2    varchar
NULL,
T4_FK        int
NOT NULL
CONSTRAINT [FK_T4_T3]
FOREIGN KEY (T4_FK)
REFERENCES dbo.Table3
(T3_PK)
GO
--Create a basic view to select some data
CREATE
VIEW dbo.AliasView1
AS
select
t2.T2_FK as Table2_ForeignKey,
t1.T1_Field1 as Table1_FieldOne,
t2.T2_Field1 as Table2_FieldOne
FROM Table1 t1
Left outer
join Table2 t2 on t2.T2_FK
= t1.T1_PK;
GO
--Create another view that select basic data, and also selecting data from view 1
CREATE
VIEW dbo.AliasView2
AS
select
v1.Table1_FieldOne
as Table1_FieldOne,     
v1.Table2_FieldOne
as Table2_FieldOne,  
t3.T3_Field1 as Table3_FieldOne,
t3.T3_Field2 
FROM Table3 t3
Left outer
join AliasView1 v1 on v1.Table2_ForeignKey
= t3.T3_PK;
GO
--My attempt to get the desired output, but no luck
SELECT 
col.COLUMN_NAME as AliasColumnName, col.DATA_TYPE, col.CHARACTER_MAXIMUM_LENGTH
as max_length, colu.*
FROM
information_schema.COLUMNS col
left
outer join
(SELECT 
VIEW_SCHEMA, VIEW_NAME, COLUMN_NAME,
min(TABLE_NAME)
as TABLE_NAME
FROM information_schema.VIEW_COLUMN_USAGE colu
WHERE VIEW_NAME =
'AliasView2'
Group by VIEW_SCHEMA, VIEW_NAME, COLUMN_NAME
) COLU ON colU.VIEW_NAME
= col.TABLE_NAME
and colu.COLUMN_NAME
= col.COLUMN_NAME
left
outer join
(select a.name
as TableName, c.name
as FieldName
from sys.foreign_key_columns fk
join sys.all_objects a
on a.object_id
= fk.parent_object_id
join sys.all_columns c
on c.object_id
= a.object_id
and c.column_id
= fk.parent_column_id
join sys.all_objects ar
on ar.object_id
= fk.referenced_object_id
join sys.all_columns cr
on cr.object_id
= ar.object_id
and cr.column_id
= fk.referenced_column_id
join sys.schemas scr
on scr.schema_id
= ar.schema_id
) fks on fks.TableName
= colu.TABLE_NAME
and fks.FieldName
= colu.COLUMN_NAME
WHERE COL.TABLE_NAME
= 'AliasView2'
order
by col.ORDINAL_POSITION
This is the results being returned: (That is not 100% what I am looking for)
AliasColumnName
DATA_TYPE
max_length
VIEW_SCHEMA
VIEW_NAME
COLUMN_NAME
TABLE_NAME
Table1_FieldOne
varchar
1
dbo
AliasView2
Table1_FieldOne
AliasView1
Table2_FieldOne
varchar
1
dbo
AliasView2
Table2_FieldOne
AliasView1
Table3_FieldOne
varchar
1
NULL
NULL
NULL
NULL
T3_Field2
varchar
1
dbo
AliasView2
T3_Field2
Table3
The desired results must be like the following:
AliasColumnName
DATA_TYPE
max_length
VIEW_SCHEMA
VIEW_NAME
COLUMN_NAME
TABLE_NAME
Table1_FieldOne
varchar
1
dbo
AliasView2
T1_Field1
Table1
Table2_FieldOne
varchar
1
dbo
AliasView2
T2_Field1
Table2
Table3_FieldOne
varchar
1
dbo
AliasView2
T3_Field1
Table3
T3_Field2
varchar
1
dbo
AliasView2
T3_Field2
Table3
NOTE:  the COLUMN_NAME and TABLE_NAME must the REAL field of the TABLE it belongs to and not only ONE LEVEL Higher’s ALIAS View Name

Advertisement

Now we have a lot of views which select data from the tables, but I need to get the "Table Column Name" that is linked in the view.
If you are using SQL Server 2012/2014, then you can use
sys.dm_exec_describe_first_result_set (Transact-SQL) to gte the informations.
Olaf Helper
[ Blog] [ Xing] [ MVP]

October 11, 2015

I need the "Real" Table / Table Column names from a sql view

Firstly, we have a system with ~1000 tables and ~250 views.  We have field level security on the table columns (in our code) Now we have a lot of views which select data from the tables, but I need to get the "Table Column Name" that is linked i

How to view all tables and column names from a remote database

Hey Guys, I have a database in a remote server. I have the db link and the schema name.. Now i want to view all the tables in the remote database with column names.. I dont have access to that database using sql developer.. But i can view some tables

What is the use of Position Column Name in USER_CONS_COLUMNS Table

Please let me know the purpose of POSITION Column Name in USER_CONS_COLUMNS Table. Example : Created this below Table having two constraints 1.Primary Key 2.Check Constraint CREATE TABLE A(ID NUMBER PRIMARY KEY,NAME VARCHAR2(30),SAL NUMBER CHECK (SAL

Migrating from Sql Server tables with column name starting with integer

hi, i'm trying to migrate a database from sqlserver but there are a lot of tables with column names starting with integer ex: *8420_SubsStatusPolicy* i want to make an offline migration so when i create the scripts these column are created with the s

Do I need to create indexes on column name?

Following queries have been shown as bad sql :           select * from t1 where name = 'FUNCTION' Count=     7     Avg Time=13     Min Time=0     Max Time=47 select * from t2 where name = 'USER_TYPE'       Count=2     Avg Time=85     Min Time=0     M

Query the column names from Public database links

Greetings, I would like to retrieve the list of all the column names from a public database link. A regular ALL_TAB_COLUMNS doesn't seem to be working. Thanks John9569Hi, I think your DBA needed to create a synonym for you to have access of that remo

Getting column names from cursor

Hi all I have procedure which produces list of applied for jobs web page(using htp. package) I got result in cursor, but have to provide dinamic sort, based on colimn pressed procedure my_application_list (pi_resume_num     in varchar2 default null ,

Referencing table created column names

probably a really simple solution but I'm having trouble, when you create a new column in an SQL query: example - ISNULL(row1, 99999) AS ideal how do I then reference that column within the same query? (example - (ideal + 10) AS newideal) When i refe

Column names from another table

Hi All, I have a scenario where i need to get names of a column from another table for eg, Table EMP EmpNo EmpName EmpContact EmpPhone 1 xyz [email protected] 345      2 abc [email protected] 897 3 ttp [email protected] 345 The column names of this t

Parse SQL: How to extract column names, table names from a SQL query

Hi all, I have a requirement wherein a SQL query will be given in a text file. The text file has to be read (probably using text_io package), the SQL query needs to be parsed and the column names, table names and where clauses have to be extracted an

Dynamic Column Names from one table and its corresponding values from another table

I have 2 tables. First tables gives the specification if a column is required or not. we have the 2nd table with the same column name where we provide the actual values. I want to select all the required columns from the 1st table and retrieve the va

Accessing  table column names from WAD

Hi  All,    I w ould  like  to  access  the  column names (the  Infoobject name) of a  table . Was  playing  arnd with  the API..SAPBWGetDataProviderDimensions but it  gives only characters  column name.. WAS wondering if  any of  you  has any  idea 

How to bind a column name in PL/SQL

i have this code in a transaction report. DECLARE q VARCHAR2 (1000); t VARCHAR2 (15); BEGIN t := '11110000'; q := q || 'select STARTDATE, PHONESOURCE, PHONETO, DURATION '; q := q || 'from TRANSACTION '; q := q || 'where TRANS_ID = ' ||t|| 'and STARTD

How to use column name from subquery result set.

I have these 2 tables SQL> select * from temp   2  ; TABLE_NAME                     COLUMN_NAME                    TYPE lst                            name                           STATUS lst                            val SQL> select * from lst; T

Strange behavior in BI Publisher with long column names from BI Answers

Hello all! I have created a BI Answers request with few calculated columns in form of case when <condition> then <value if true> else <value if false> end Then in BI Publisher Desktop designed a report ttemplate based on saved BI Answers

How to retrieve column name from Excel using POI HSSF eventusermodel?

Hi , I am simply reading the excel sheet and writing in a txt file. I have done the following in the switch construct. It works fine but I want to retrieve only the column header. But this code gives me all available strings in the excel sheet. Any p

Column values in table equal column names in other table (unpivot? cross apply?)

2 tables: CREATE TABLE Requirement  reqID int IDENTITY(1,1) PRIMARY KEY,  req_result_id numeric(10,0) NOT NULL,  description varchar(200),  heading varchar(100),  GO INSERT INTO Requirement VALUES (5296,'Cold pack','HH19_5'); INSERT INTO Requirement

XLA tables and column names while creating accounting in AP.

Dear All, Could anybody please tell me what are the columns of the XLE tables gets hitted when i am creating accounting for an AP invoice? Also Please let me know from which tables and which columns, i can get all eligible invoices for payment? any c

How to get constraint name and table and column names

Hi Team, I am a junior DBA. I want to check how many columns are under PRIMARY KEY constraint. I used dba_constraints view. Please find below details. SQL> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints 2 where TABLE_NAM

How to find out Column names from JDBC?

Hello, How to get Oracle's Table Meta Data without firing a "SELECT" statement and using ResultSet meta data ? Thanks, -rajThis works for most jdbc drivers: Connection con = ???; DatabaseMetaData dbmd = con.getMetaData(); ResultSet rs = dbmd.get