Need help to get the "Real" column / table names from the fields selected withing a Sql_View
-
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
HEre is the current output of these queries:
There is the desired output I am looking for:
find below the link with all the queries in, and screen dumps of the outputs / expected outputs
-
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (2/25/2015)
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.This is news to me! (Don't get me wrong, I learn new stuff every day, so I'm not disagreeing with you, just a follow up question).
So why would SQL Server have difficulty creating a plan using nested views as opposed to say derived tables in subqueries? Obviously I could think of special cases, like "VIEW_METADATA", "SCHEMABINDING" or other options, but I'm interested in how views that are essentially just saved queries could hide information from the query optimizer.
-
patrickmcginnis59 10839 (2/25/2015)
Sean Lange (2/25/2015)
Hi and welcome to the forums. I see what you are trying to do here but I would suggest that you should avoid nesting views like the plague. The challenge you are running into here is the same challenge the sql engine runs into when generating execution plans. In other words, when you have a view calling a view you are highly likely to get poor execution plans.This is news to me! (Don't get me wrong, I learn new stuff every day, so I'm not disagreeing with you, just a follow up question).
So why would SQL Server have difficulty creating a plan using nested views as opposed to say derived tables in subqueries? Obviously I could think of special cases, like "VIEW_METADATA", "SCHEMABINDING" or other options, but I'm interested in how views that are essentially just saved queries could hide information from the query optimizer.
Here is one article from Grant about this. http://www.scarydba.com/tag/nested-view/[/url]
This one has links to several other articles discussing what happens performance wise with nested views. http://stackoverflow.com/questions/5913995/sql-server-2008-nesting-views
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
It is an existing system with +- 1000 tables and +-250 complex views. To change the views to not link in other views is not worth the effort. The reason why I need the "REAL" table / column names of the selection in the views is so that I can apply "other business logic" that is linked to the "actual database field" in our database objects. I know it is not the best practice to link views inside views, but in our situation that is something we can't change.
I know that the execution path of these views is not perfect, but we cannot change that at the moment.
We have field level security that is applied on table column level, and if it is used in a view it must be applied there as well. If it is a concatenation of fields we can return NULL for that field or the AliasName so that we can apply security manually for that Alias name in the VIEW. And maybe an extra column that indicate that this alias is NotLinkedToTableColumn so that I know how to treat the security
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply