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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SimonCommented:
select * from vw_customers_order where 1=2

Will get you just the headers without any data.
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.