Field List Returned as Data

[NeoPa]

I have a table with a large number of fields associated. I'd like to run a query to return the names of all these fields as data, so I can use this later in other data manipulation queries.

I'm very rusty on SQL Server, so I expect a nudge in the right direction would be enough to get me going.

[NeoPa]

I'm using MS SQL Server Management Studio Express (2008) and after playing around for a while I found something that would have been much more obvious if I had done more work in this area recently.

Right click on the table of interest. Select Script Table as / SELECT to / New Query Editor Window. When the query is created it will list all the fields in the SELECT statement.

[ck9663]

If you want to retrieve the columns in a result set, you can use the INFORMATION_SCH EMA view. Try this, change the parameters as necessary:

select * from information_schema.columns
where table_catalog = '<dbname>' and
table_schema = '<ownername>' and table_name = '<tablename>'

Happy Coding!!!

~~ CK

[NeoPa]

Thanks CK.

It's a work problem so I'll check it out tomorrow. It seems more flexible overall, so may be a better solution. I was chuffed to find that the automatic scripting does so much for you though. Definitely a bonus :)