Retrieve Columns

Do you need this for building queries? Then in QA, open the database, go to the User Tables, open the table in question, then click-and-drag the "Columns" icon onto you query window. This will print a comma-separated list of column names.

If you need the column names in your client app, you can run through the Fields collection of your recordset object and retrieve the Name property for each field. Also check if by any chance your control has a property to show/hide the column names.

One final option in QA: change the query output to Text, and run this:
SELECT * FROM TEST WHERE id IS NULL

quote:Originally posted by Madhivanan

Adriaan, I dont find User Tables option in QA

Which version of SQL Server are you working with? In QA for SQL 2000, in the Object Browser, expand the database. This expands to a couple of folders, and the first folder should be User Tables. Expand that folder, and you get the list of the user tables. Again, expand the user table that you need, and this again expands to a couple of folders, and the first folder should be Columns. Click-and-drag the Columns folder onto the query window.

quote:

quote:One final option in QA: change the query output to Text, and run this:
SELECT * FROM TEST WHERE id IS NULL

Yes this is one of the options. But my expected outcode is
col1 col2 col3
id name spec

The best I can come up with is:

DECLARE @Test VARCHAR(8000)

SET @Test = ''

SELECT
@Test = @Test
+ CASE WHEN LEN(@Test) = 0 THEN 'SELECT TOP 1 ' ELSE ', ' END
+ CHAR(39) + name + CHAR(39) + ' Col' + CAST(colorder AS VARCHAR(10))
from syscolumns
WHERE id = object_id('test')
order by colorder

SET @Test = @Test + ' FROM syscolumns'

EXEC (@Test)
You may run into problems when the query statement exceeds 8000 characters.