May 27, 2008 at 4:43 pm
Hello Everyone,
I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.
For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.
In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.
(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)
Aliases are easy enough to obtain:
select object_id, column_id, name
from sys.columns
where object_id = object_id('SomeViewsName');
As are the 'physical' columns:
SELECT
t.object_id as TABLE_OID,
c.column_id as COLUMN_OID,
c.name
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
AND c.column_id = d.referenced_minor_id
WHERE
d.class < 2 AND
v.name = 'SomeViewsName';
As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.
Ultimately I'd like to get this result:
Alias used in View, physical table's object id, physical column's id
Am I missing something?
Thank you very much in advance for your help.
May 27, 2008 at 6:02 pm
I do not believe the information on mapping a view column to the dependent table columns is available within SQL Server. There are many situations where a single view column is derived from multiple columns in a complex manner or using a UDF. How should the information be reported ? You could use one of the SQL Parsers to derive this information from the view source. See http://www.sqlparser.com/index.php?ref=sqlpp
Two examples of complex derivation using the demo AdventureWorksDB are below:
SELECTContactID
,COALESCE(Title + ' ' , '')
+CASE NameStyle
WHEN 0 THEN FirstName + COALESCE( ' ' + MiddleName ,'') + LastName
WHEN 1 THEN LastName + ' ' + FirstName + COALESCE( ' ' + MiddleName ,'')
ELSE 'UNKNOWN NAME FORMAT'
END
+COALESCE( ' ' + Suffix ,'')
AS ContactFullName
,EmailAddress
,Phone
FROMPerson.Contact
WHERE(Phone Like '332%'
ORPhone like '1 (11)%'
)
select sum(( SalesPersonQuotaHistory.SalesQuota - SalesPersonQuotaStats.SalesPersonQuotaAvg )
* square( SalesPersonQuotaHistory.SalesQuota - SalesPersonQuotaStats.SalesPersonQuotaAvg )
/ ( SalesPersonQuotaStats.SalesPersonQuotaCount * SalesPersonQuotaStats.SalesPersonQuotaSigma
* square( SalesPersonQuotaStats.SalesPersonQuotaSigma)
)
) as SalesPersonQuotaSkew
, sum( square ( square ( SalesPersonQuotaHistory.SalesQuota - SalesPersonQuotaStats.SalesPersonQuotaAvg ) )
/( SalesPersonQuotaStats.SalesPersonQuotaCount * square( square( SalesPersonQuotaStats.SalesPersonQuotaSigma ))))
- 3 as SalesPersonQuotaExcessKurtosis
fromSales.SalesPersonQuotaHistory
CROSS JOIN
(select avg(Sales.SalesPersonQuotaHistory.SalesQuota) as SalesPersonQuotaAvg
,stdev(SalesQuota) as SalesPersonQuotaSigma
,count(SalesQuota) as SalesPersonQuotaCount
fromSales.SalesPersonQuotaHistory
) ASSalesPersonQuotaStats
SQL = Scarcely Qualifies as a Language
May 27, 2008 at 8:38 pm
The only way that you could get this information would be to parse the View source code yourself. The source code is available (in syscomments and other places).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 7:22 am
Thank you very much for your reply! Unfortunately we've defined that views will only be projections of columns, with the goal of encapsulating some physical design decisions for users and analysts.
I feared that parsing the select part of the view definition would be the only solution. I'll look into that sqlparser. But as I just need to parse the select statement and get the column name / alias pairs a simple lexer to give me tokens would do. Anybody know of any good lexer generators for C#?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply