List only the visible columns in a view?

In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get a
row for every column in a view including those that might not be visible but
used only in the WHERE or ORDER BY clauses for example.
Is there an easy way to reduce the list to only show the columns that
actually appear in the SELECT clause of the view definition?
I've tried examining Profiler when expanding the columns node under the View
in Management Studio as it seems to load only one node per visible column
but haven't been able to make much sense of how it does this.
BTW I'm using that view as it promises to only show views and columns "to
which the current user has permissions". Is this reliable or is there a
preferred way of listing visible views, talbe, & columns etc?
cheers,
Paul.Hi, Paul
Look in the INFORMATION_SCHEMA.COLUMNS view.
Razvan|||Use the catalog schema views...
SELECT
name
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.YourViewName');
"Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
> In SQL Server 2005, when using INFORMATION_SCHEMA.VIEW_COLUMN_USAGE I get
> a
> row for every column in a view including those that might not be visible
> but
> used only in the WHERE or ORDER BY clauses for example.
> Is there an easy way to reduce the list to only show the columns that
> actually appear in the SELECT clause of the view definition?
> I've tried examining Profiler when expanding the columns node under the
> View
> in Management Studio as it seems to load only one node per visible column
> but haven't been able to make much sense of how it does this.
> BTW I'm using that view as it promises to only show views and columns "to
> which the current user has permissions". Is this reliable or is there a
> preferred way of listing visible views, talbe, & columns etc?
> cheers,
> Paul.
>|||Thanks Aaron - much appreciated.
Is there an easy way to determine which columns a user might have access to
when using the "sys" tables? That was what the INFORMATION_SCHEMA views
seemed to promise although there was some doubt in newsgroups as to their
accuracy in SQL2000 due to them being derived from the sysdepends table.
I'm betting that this will have changed in 2005.
However moving back to the sys tables/views will mean I have to determine
this permission information in some other way. Any seggestions would be
appreciated tremendously.
cheers,
Paul.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCq14hWBGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Use the catalog schema views...
> SELECT
> name
> FROM
> sys.columns
> WHERE
> object_id = OBJECT_ID('dbo.YourViewName');
>
> "Paul Ritchie" <REMOVEpritchie@.REMOVExtra.REMOVEco.REMOVEnz> wrote in
> message news:%23S90IfVBGHA.4004@.TK2MSFTNGP15.phx.gbl...
>|||> Is there an easy way to determine which columns a user might have access
> to when using the "sys" tables?
Take a look at sys.database_permissions
I believe the join between sys.database_permissions p and sys.comments c
would be:
ON
c.object_id = p.major_id
AND c.column_id = p.minor_id
WHERE
p.class = 1
AND p.grantee_principal_id = SUSER_ID('username');
I am not 100% sure on that, and don't have time today to experiment with
column-level permissions. But hopefully that gets you started.

> accuracy in SQL2000 due to them being derived from the sysdepends table.
Permissions derived from sysdepends? I think that is inaccurate.
A

List control visible

I want my list control only be visible if there is data. So, in my report I
have several list controls, one of the list controls shows alias data for a
customer. If the customer has no alias data then I don't want the alias list
control to show. In the alias list control I have several textboxes.
In the list properties, visibility tab, I set the expression to
"=IIf(Fields!IndAliasID.Value > 0, true, false)" but the list control always
shows, even if the IndAliasID is null or zero.Hello Mike,
Based on my test, I was not able to reproduce the issue. Please note when
the expression is =true, the list item is hidden. When it is =false, it is
shown. You may test to see if it make any difference
=IIf(Fields!IndAliasID.Value = 0 or Fields!IndAliasID.Value is nothing,
true, false)
Also, the visiblity is for the item of list. If there is any item in the
list are >0, the list shall be visible with the items.
If you have any update, please feel free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I figured it out on my own. This post is no longer required.
Thank you for responding.
"Peter Yang [MSFT]" wrote:
> Hello Mike,
> Based on my test, I was not able to reproduce the issue. Please note when
> the expression is =true, the list item is hidden. When it is =false, it is
> shown. You may test to see if it make any difference
> =IIf(Fields!IndAliasID.Value = 0 or Fields!IndAliasID.Value is nothing,
> true, false)
> Also, the visiblity is for the item of list. If there is any item in the
> list are >0, the list shall be visible with the items.
> If you have any update, please feel free to let's know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>