select header from view in sql

Is there a way to get column headers from a view?

select top (1) * from vw_customers_order

id      1
name    John
address  123 Beach st.
etc
VBdotnet2005Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
hmm, To get the headers you can use SET FMTONLY ON, e.g.

USE AdventureWorks2012;
GO

SET FMTONLY ON;

SELECT  *
FROM    HumanResources.vEmployee VE;

SET FMTONLY OFF;
GO

Open in new window


But your sample looks like you want to unpivot your view, e.g.

USE AdventureWorks2012;
GO

WITH    Casted ( BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType, EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode, CountryRegionName )
          AS ( SELECT   VE.BusinessEntityID ,
                        CAST(VE.Title AS NVARCHAR(255)) ,
                        CAST(VE.FirstName AS NVARCHAR(255)) ,
                        CAST(VE.MiddleName AS NVARCHAR(255)) ,
                        CAST(VE.LastName AS NVARCHAR(255)) ,
                        CAST(VE.Suffix AS NVARCHAR(255)) ,
                        CAST(VE.JobTitle AS NVARCHAR(255)) ,
                        CAST(VE.PhoneNumber AS NVARCHAR(255)) ,
                        CAST(VE.PhoneNumberType AS NVARCHAR(255)) ,
                        CAST(VE.EmailAddress AS NVARCHAR(255)) ,
                        CAST(VE.EmailPromotion AS NVARCHAR(255)) ,
                        CAST(VE.AddressLine1 AS NVARCHAR(255)) ,
                        CAST(VE.AddressLine2 AS NVARCHAR(255)) ,
                        CAST(VE.City AS NVARCHAR(255)) ,
                        CAST(VE.StateProvinceName AS NVARCHAR(255)) ,
                        CAST(VE.PostalCode AS NVARCHAR(255)) ,
                        CAST(VE.CountryRegionName AS NVARCHAR(255))
               FROM     HumanResources.vEmployee VE
             )
    SELECT  *
    FROM    Casted C UNPIVOT ( ColumnValue FOR ColumnName IN ( Title, FirstName, MiddleName, LastName, Suffix, JobTitle, PhoneNumber, PhoneNumberType,
                                                               EmailAddress, EmailPromotion, AddressLine1, AddressLine2, City, StateProvinceName, PostalCode,
                                                               CountryRegionName ) ) U;
GO

Open in new window

0
 
SimonCommented:
select * from vw_customers_order where 1=2

Will get you just the headers without any data.
0
 
JR2003Commented:
SET NOCOUNT ON
DECLARE @TABLE_NAME nvarchar(200)
SET @TABLE_NAME = 'VendorsView'
DECLARE @COLUMN_NAME nvarchar(200)
DECLARE @SQL as nvarchar(MAX)
SET @SQL = ''
DECLARE myCursor cursor
   FOR SELECT C.COLUMN_NAME
         FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.TABLE_NAME = @TABLE_NAME
        ORDER BY C.ORDINAL_POSITION

       
OPEN myCursor
FETCH NEXT FROM myCursor INTO @COLUMN_NAME
SET @SQL = 'SELECT '
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @SQL = @SQL + '''' + @COLUMN_NAME + ': '' + CONVERT(nvarchar(500),' + QUOTENAME(@COLUMN_NAME) + '),'
    FETCH NEXT FROM myCursor INTO @COLUMN_NAME
END

CLOSE myCursor
DEALLOCATE myCursor
SET @SQL = LEFT(@SQL, LEN(@SQL) -1) + ' FROM ' + QUOTENAME(@TABLE_NAME)
EXEC (@SQL)
PRINT @SQL
0
 
PortletPaulfreelancerCommented:
I would suggest doing that type of pivoting in "the presentation layer" rather than SQL

To me this is presentation:

id             1                         2
name       John                  Jill
address  123 Beach st.  Ocean Grove.
you might want multiple persons on the same screen in this layout, and/or pagination through a set of records which will need to be in a predetermined order. SQL can furnish the ordered rows of data and your ASP code can do the 'flip' presentation (I am assuming ASP topic is relevant)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
VBdotnet2005, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.