May 19, 2009 at 10:42 am
Hi all.
What I am trying to get is the alias assigned to a field when creating a view.
I know I can look at the SQL text of the view definition in information_schema.views but I don't want to parse the view definition so I tryed with other system views and tables.
I see that [font="Courier New"]information_schema.view_column_usage [/font] tells me al the columns and the tables involved in the view, but I could not find where the alias is stored.
Look at this example:
create table test_table( id_test int null)
go
create view test_view as
select test_table.id_test as new_name
from test_table
go
select * from information_schema.view_column_usage c where view_name = 'test_view'
go
The result is:
[font="Courier New"]VIEW_CATALOG|VIEW_SCHEMA|VIEW_NAME|TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME
TEST_DB|dbo|test_view|TEST_DB|dbo|test_table|id_test[/font]
What I would like to get is something similar to:
[font="Courier New"]VIEW_NAME|TABLE_NAME|COLUMN_NAME|ALIAS_NAME
test_view|test_table|id_test|new_name[/font]
Is it possible to get this information ?
Thanks in advance
Giacomo
May 20, 2009 at 3:57 am
hi,
try this
select *,
(select name from syscolumns
where id in (select id from sysobjects
where name = View_name)) ALIAS_NAME
from information_schema.view_column_usage
where view_name = 'test_view'
ARUN SAS
May 21, 2009 at 4:16 am
arun.sas (5/20/2009)
select *,(select name from syscolumns
where id in (select id from sysobjects
where name = View_name)) ALIAS_NAME
from information_schema.view_column_usage
where view_name = 'test_view'
Unfortunately this does not work if the view has more than one field (the select on syscolumns returns more than one row).
Anyway your suggestion put me on the right track: In order to return one single record, I need to filter on column_id. This is not available in view_column_usage view, I need to look in other sys.* tables
Thanks
Giacomo
May 22, 2009 at 6:52 am
What I am trying to get is the alias assigned to a field when creating a view.
You can get this information from by joining sys.views to sys.columns
What I would like to get is something similar to:
[font="Courier New"]VIEW_NAME|TABLE_NAME|COLUMN_NAME|ALIAS_NAME
test_view|test_table|id_test|new_name[/font]
Is it possible to get this information ?
Aside from a table column, a view column can also be an expression, function, or constant so there is no correlation between a view column and a table column. I think your best bet will be to parse VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
May 25, 2009 at 4:25 am
Charles Hearn (5/22/2009)
Aside from a table column, a view column can also be an expression, function, or constant so there is no correlation between a view column and a table column.
In my case I am sure this does not happen, but you are right: in the most general case there's no guarantee that an alias can be connected to a table column. maybe that is the reason why the association view alias/table column is not soter anywhere in sistem tables...
I think your best bet will be to parse VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
That is exactly was I was trying to avoid... 🙁
I start thinking there is no way to do it just querying system tables and views...
Thanks anyway!
Giacomo
May 25, 2009 at 6:19 am
it took a while for this one to soak in. I read the requirement a couple of times, and kept thinking it's gotta be possible by using the stored proc sp_depends, but that proc does not return the alis , just the actual dependancy.
if you sp_help sp_depends from the master database, you can see it is doing a series of joins to get the information...all i did was grab the key SELECT statemetn, join it one more time to syscolumns, and *poof*, the actual dependacy is available witht eh table and column name it depends on....
see if this is the information you are looking for; replace "vw_blah" with your view name, or parameterize it to make it easier.
CREATE TABLE [dbo].[BLAH] (
[BLAHID] INT NULL,
[BLAHTEXT] VARCHAR(30) NULL)
GO
CREATE VIEW VW_BLAH
AS
SELECT
BLAHID AS THEID,
BLAHTEXT AS THETEXT,
BLAHID + 100 AS SOMECLACULATION,
getdate() as THEDATE
FROM BLAH
GO
select
'name' = (s6.name+ '.' + o1.name),
type = substring(v2.name, 5, 16),
updated = substring(u4.name, 1, 7),
selected = substring(w5.name, 1, 8),
'column' = col_name(d3.depid, d3.depnumber) ,
'tblalias' =object_name(sc.id),
'colalias' = sc.name
from sys.objects o1
,master.dbo.spt_values v2
,sysdepends d3
,master.dbo.spt_values u4
,master.dbo.spt_values w5 --11667
,sys.schemas s6
,syscolumns sc
where o1.object_id = d3.depid
and o1.type = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
and u4.type = 'B' and u4.number = d3.resultobj
and w5.type = 'B' and w5.number = d3.readobj|d3.selall
and d3.id = object_id('vw_blah')
and o1.schema_id = s6.schema_id
and deptype < 2
and sc.id = d3.id
and sc.colid = d3.depnumber
--results
name type updated selected column tblalias colalias
--------- ---------------- ------- -------- ---------- ---------- ----------
dbo.BLAH user table no yes blahID VW_BLAH THEID
dbo.BLAH user table no yes BLAHTEXT VW_BLAH THETEXT
Lowell
May 25, 2009 at 9:57 am
Wow! The last part I was missing was using [font="Courier New"]master.dbo.spt_values[/font] ! 🙂
I tested with other views in my DBs and it seems to work fine!
Thank you for your precious help!:-D
Regards
Giacomo
May 25, 2009 at 10:12 am
Sorry to write again about this topic... 🙁
It seems to work but it doesn't work correctly: your solution outputs the list of the aliases but they are not correctly associated with the column:
If I define test_view like this:
[font="Courier New"]CREATE TABLE [dbo].[test_table](
[id_test] [int] NULL,
[f1] [int] NULL,
[f2] [int] NULL
)
ALTER view [dbo].[test_view] as
select f2, test_table.id_test as new_name
from test_table[/font]
If I execute your query this is the output:
[font="Courier New"]name | type | updated | selected | column | tblalias | colalias
dbo.test_table | user table | no | yes | id_test | test_view | f2[/font]
And this is the correct result:
[font="Courier New"]name | type | updated | selected | column | tblalias | colalias
dbo.test_table | user table | no | yes | f2 | test_view | f2
dbo.test_table | user table | no | yes | id_test | test_view | new_name[/font]
Problem seems that it just outputs the column in the same order of the aliases of the view, but there there is not a correct relationship: if you swap the order of the aliases the output is not correct.
Anyway, I am working following your suggestion and investigating in that direction...
Giacomo
March 17, 2010 at 2:50 pm
Hi Guys,
I got the similar requirement but I am struggling to complete it
if you have found the solutions can you please share with me
Thanks in advance
Regards,
~Sri
March 23, 2010 at 7:41 am
srinath.chai (3/17/2010)
I got the similar requirement but I am struggling to complete it
post it it new thread/topic and elaborate your problem
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 23, 2010 at 7:46 am
srinath.chai (3/17/2010)
Hi Guys,I got the similar requirement but I am struggling to complete it
if you have found the solutions can you please share with me
Thanks in advance
Regards,
~Sri
the devil is in the details...what are you doing that is not answered in this thread? what is differnet?
give us specific details.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply