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.
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 )