Search code examples
viewsql-viewinformation-schema

Detect Sql-view structure

I create a view like this

SELECT        dbo.BaharInOut.BCode, dbo.Bahar.BName
FROM            dbo.Bahar INNER JOIN
                     dbo.BaharInOut ON dbo.Bahar.BCode = dbo.BaharInOut.BCode

And tried to get information from msSQL

1- way 1

SELECT        COLUMN_NAME, TABLE_NAME
FROM            INFORMATION_SCHEMA.VIEW_COLUMN_USAGE AS VCU
WHERE        (VIEW_NAME = 'AAA')

the answer is

COLUMN_NAME  TABLE_NAME
------------ -------------
BCode        Bahar
BName        Bahar
BCode        BaharInOut

as you see , The BCode is repeated

2- Way 2

SELECT Name
        FROM    sys.dm_exec_describe_first_result_set (N'SELECT *   from  AaA ', null, 1)

And result more became strange

Name
--------
BCode
BName
BCode
Serial
MSeq

Any idea?

Solution

  • In Way2 just add simple "where" by using "is_hidden" column

    SELECT Name,source_column,source_table FROM sys.dm_exec_describe_first_result_set (N'SELECT * from AAA', null, 1) WHERE is_hidden=0