7

Just wondering how to list column name and table name in one query for a view.

For example:

A view named as viewC, create by tbl1 inner join tbl2, contain a,b,c,d columns (a,b from tbl1 and c,d from tbl2).

How to 

Select COLUMN_NAME, DATA_TYPE, column_default, character_maximum_length, sourceTableNAME 
FROM information_schema.columns 
where table_name='viewC'

together?

gotqn
  • 42,737
  • 46
  • 157
  • 243
lpfy
  • 124
  • 1
  • 2
  • 10

3 Answers3

13

This information is available from the INFORMATION_SCHEMA views:

SELECT * 
FROM    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS cu
JOIN    INFORMATION_SCHEMA.COLUMNS AS c
ON      c.TABLE_SCHEMA  = cu.TABLE_SCHEMA
AND     c.TABLE_CATALOG = cu.TABLE_CATALOG
AND     c.TABLE_NAME    = cu.TABLE_NAME
AND     c.COLUMN_NAME   = cu.COLUMN_NAME
WHERE   cu.VIEW_NAME    = '<your view name>'
AND     cu.VIEW_SCHEMA  = '<your view schema>'

If your view includes tables from more than one database, the query will become considerably more complex

Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • 3
    This shows column names and table names yes. But does not sow alias names which is required in my case. – Asif Ashraf Aug 24 '13 at 13:26
  • Was anyone able to figure out the query for seeing view column alias names tied to the underlying table column names? – AS91 May 16 '17 at 21:49
2

I had a view (SQL Server 2012) that read from another database and had a UNION as well. The above queries didn't work for me, so I decided to use the Profiler to check how SSMS would retrieve the information. This is what I ended up with:

SELECT
     clmns.name AS [Name]
    ,usrt.name AS [DataType]
    ,CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length]
    ,CAST(clmns.precision AS int) AS [NumericPrecision]
    ,CAST(clmns.scale AS int) AS [NumericScale]
    ,clmns.column_id AS [ID]
FROM    sys.all_views AS v
INNER JOIN sys.all_columns AS clmns 
    ON clmns.object_id=v.object_id
LEFT OUTER JOIN sys.indexes AS ik 
    ON ik.object_id = clmns.object_id 
    AND 1=ik.is_primary_key
LEFT OUTER JOIN sys.types AS usrt 
    ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset
    ON (baset.user_type_id = clmns.system_type_id AND baset.user_type_id = baset.system_type_id) 
    OR ((baset.system_type_id = clmns.system_type_id) AND (baset.user_type_id = clmns.user_type_id) AND (baset.is_user_defined = 0) AND (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns
    ON xscclmns.xml_collection_id = clmns.xml_collection_id
WHERE v.[type] = 'V'
    AND  v.name ='<your view name>' 
    AND SCHEMA_NAME(v.schema_id)='<your schema name>'
ORDER BY [ID] ASC
Roeland
  • 820
  • 1
  • 9
  • 33
  • I needed to get information about columns in system views, and this is the only answer I could get to work! – Bridge Sep 12 '18 at 10:52
1

Try this:

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS VCU
JOIN INFORMATION_SCHEMA.COLUMNS AS COL
ON  COL.TABLE_SCHEMA  = VCU.TABLE_SCHEMA
AND COL.TABLE_CATALOG = VCU.TABLE_CATALOG
AND COL.TABLE_NAME    = VCU.TABLE_NAME
AND COL.COLUMN_NAME   = VCU.COLUMN_NAME
WHERE VCU.VIEW_NAME   = 'ViewName'

See this SQLFiddle

Himanshu
  • 31,810
  • 31
  • 111
  • 133