Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
herman404
Starting Member
10 Posts |
 Posted - 2006-05-02 : 07:48:10
|
| Hi, I was wondering if it was possible to get all the field names in a table and store it in a stored procedure as a varchar array or some other applicable data structure. Thanks! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-05-02 : 07:59:52
|
| sql server already stores this information. One way to view it is through the view: Information_schema.columns.Be One with the OptimizerTG |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-02 : 08:07:03
|
| you can use table datatypeIf Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-02 : 09:15:05
|
quote:
Originally posted by herman404
Hi, I was wondering if it was possible to get all the field names in a table and store it in a stored procedure as a varchar array or some other applicable data structure. Thanks!
What is the purpose of doing this?Probably you want to use Dynamic SQLMadhivananFailing to plan is Planning to fail |
|
|
|
herman404
Starting Member
10 Posts |
Posted - 2006-05-02 : 09:56:21
|
| Hi everyone,I guess I should state my end goal first. I'm writing a stored procedure that takes a table name as a parameter. It then creates a new table (the history table which records any changes to the original table) which is a copy of the original table with four new fields. The procedure should also create triggers on the original table that causes records to be written into the history table for every insert, update, or delete on the original table. I would need the field names to script the creation of these triggers, but if someone knows the best way to do this, I'd be grateful!Thanks! |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-05-02 : 11:22:52
|
| The following script will give you a comma separated list of the column names for a table (just change the table_name) to a variable. (declare @columns varchar(4000)select @columns = ''select @columns = @columns + column_name + ', ' from Information_schema.columnswhere table_name = 'Authors'select @columns = left(@columns,len(@columns)-1)select @columnsHope it helps,DaltonBlessings aren't so much a matter of "if they come" but "are you noticing them." |
|
|
|
|
|
|
Getting field names as array