Get column alias from view? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get column alias from view?

Author

Topic

mgernhardt
Starting Member

2 Posts

Posted - 2006-01-16 : 10:49:36

I need to get the column alias' or column descriptions from the underlying tables in a view. I have tried ::fn_listextendedproperty which successfully returns the MS_Description of table columns:


select  objname, name,value
from ::fn_listextendedproperty(default,'USER','dbo','table','TABLENAME','column',default);

But changing it to a view:


select  objname, name,value
from ::fn_listextendedproperty(default,'USER','dbo','view','SPECIFICVIEW',default,default);

does not return anything, but


select  objname, name,value
from ::fn_listextendedproperty(default,'USER','dbo','view',default,default,default);

returns two rows like this:


1: v103_AssociateInfo MS_DiagramPane1	[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
  Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0      NumPanes = 4           
Configuration = "(H (1[40] 4[20] 2[20] 3) )" End  Begin PaneConfigurati.....
2. v103_AssociateInfo	MS_DiagramPaneCount	1

Does anyone have and UDF or something that will return the column alias from a view??? I'm trying to create a dynamic report writer (in PHP) and need the alias or column description to present to the user.

pomela
Starting Member

15 Posts

Posted - 2006-01-16 : 11:00:17

Column alias and column description are two different things.
Try this:
SELECT * FROM syscolumns WHERE id=object_id('YourView')
OR:
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE VIEW_NAME='YourView'

mgernhardt
Starting Member

2 Posts

Posted - 2006-01-16 : 11:41:34

This one worked the best:


SELECT * FROM syscolumns WHERE id=object_id('YourView')

where the 'name' column returns the the Alias if it exists or the table col name otherwise.

On a side note, do you know why:


SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE      
WHERE VIEW_NAME='MyView'

does not return all the tables used in the view, even if columns from said tables are selected as output in the view? It does not include the 'main' table which all the other tables join to as well as some other tables.

Thanks for your help!

pomela
Starting Member

15 Posts

Posted - 2006-01-16 : 12:08:30

INFORMATION_SCHEMA.VIEW_TABLE_USAGE holds the basic tables columns involved in your view definitions.
I mentioned this one because I didn't know if you need the basic tables columns or the view columns.
anyway, you can use INFORMATION_SCHEMA.COLUMNS instead of syscolumns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources