Geek asks and answers

How to list the name of the source column table in VIEW (SQL Server 2005)

Just wondering how to specify the column name and table name in the same query for presentation.

For instance:

viewCCreate a view with the name using tbl1 inner join tbl2, enter the columns a,b,c,d( a,bfrom tbl1and c,dfrom tbl2).

how

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

together?

+5
source share
3 answers

This information is available from the views INFORMATION_SCHEMA:

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 much more complex

+8
source

Try the following:

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

+1
source

I had a view (SQL Server 2012) that read from another database and had UNION. The above requests did not help me, so I decided to use Profiler to check how SSMS would receive information. This is what I came across:

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
0
source

More articles:


All Articles