Click here to Skip to main content
15,884,425 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Selection on the table, but we want to know the names of the columns in that table
Assuming that returns a row selection you how to read column value of the selection

select * from tbl_1

print ?????? <first column="" value="">
Posted
Comments
Tomas Takac 8-Nov-14 9:35am    
Not clear. You do "select *" hence you get all the columns. What is your problem?
OriginalGriff 8-Nov-14 9:50am    
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Use the "Improve question" widget to edit your question and provide better information.

It sounds like you want to use a DataReader and call GetSchemaTable.
http://msdn.microsoft.com/en-us/library/system.data.idatareader.getschematable(v=vs.110).aspx[^]
 
Share this answer
 
Comments
Manas Bhardwaj 8-Nov-14 10:16am    
This would work if the OP wants to get it done in managed code (which I am not sure of). +5 for GetSchemaTable
PIEBALDconsult 8-Nov-14 10:35am    
Well, it _should_ be done in managed code.
Something like this:

SQL
SELECT *
FROM Northwind.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'


Look further at MSDN:

http://msdn.microsoft.com/en-us/library/aa933204%28SQL.80%29.aspx[^]
 
Share this answer
 
Assuming you want a query that returns the names of the columns along with the data -- suitable for print/output/display somewhere -- you can do something like the following (which could be made into a stored procedure if you like).
But, it's not really a good idea, in part because you have to convert everything to strings in the database and then send it all (across the network) to the application.
The application UI or a reporting system (like Crystal) is better suited to preparing the data for output. If you simply fill a DataTable via a DataAdapter you will have the names of the columns anyway.

Just because you can doesn't mean you should.

SQL
DECLARE @sch SYSNAME = 'dbo'
DECLARE @tbl SYSNAME = 'JunkName'
DECLARE @col SYSNAME
DECLARE @cl0 NVARCHAR(MAX) = ''
DECLARE @cl1 NVARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX)

DECLARE crs CURSOR FOR
SELECT C.name
FROM sys.schemas A
INNER JOIN sys.objects B
ON A.schema_id=B.schema_id
INNER JOIN sys.columns C
ON B.object_id=C.object_id
WHERE A.name=@sch
AND B.name=@tbl
ORDER BY column_id

OPEN crs

FETCH NEXT FROM crs INTO @col

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cl0 = @cl0 + ',CAST(''' + @col + ''' AS NVARCHAR(MAX)) [' + @col + ']'
    SET @cl1 = @cl1 + ',CAST(' + @col + ' AS NVARCHAR(MAX)) [' + @col + ']'

    FETCH NEXT FROM crs INTO @col
END 
CLOSE crs;
DEALLOCATE crs;

SET @cl0 = STUFF(@cl0,1,1,' ')
SET @cl1 = STUFF(@cl1,1,1,' ')

PRINT @cl0
PRINT @cl1

SET @sql = 'SELECT' + @cl0 + ' UNION ALL SELECT' + @cl1 + ' FROM [' + @sch + '].[' + @tbl + ']'

PRINT @sql 

EXECUTE (@sql )
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900