All Columns Not Showing Up In information_schema.view_column_usage For A View

• jasonnance77

  SSC Veteran

  Points: 226

  More actions

  February 1, 2011 at 12:19 pm

  #233035

  While trying to build a dynamic process to gather all the active views, source table names, and columns in Sql Server 2008 Enterprise - I ran into a bit of a interesting issue.

  I created this query that works fine for about 75% of all our Sql Server views. It lists all the columns correctly.

  SELECT * FROM sys.columns c, sys.views v, information_schema.view_column_usage

  WHERE c.object_id = v.object_id

  AND information_schema.view_column_usage.COLUMN_NAME = c.name

  AND v.name = 'v_QualityMeasures_HF_Data'

  and VIEW_NAME = 'v_QualityMeasures_HF_Data'

  The problem for about 25% of the views is that no columns or only a few columns show up when this statement is executed. For example, the v_QualityMeasures_HF_Data view has around 65 columns. However, this statement only gives me 4 columns back. But the statement runs correctly for other views.

  I ensured that the sa user name, our sysadmin user name, has dbowner rights to the underlying tables in the azQuality database -- which is one of the databases that we are missing the table and column structure from.

  I am out of ideas. I don't understand why I am not getting back all the columns for this view.

  Do you have any suggestions?

  Jason Nance, MHA

  Moses Cone Health System