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 |
|
myrbs
Starting Member
10 Posts |
 Posted - 2010-05-13 : 23:44:47
|
| I would like to list all columns of my table, I could do that via sp_columnsbut the problem is i want to concatenate each column into one field for exampletable name | columnstbl_test | id|user|pass|datewhat approach should i do tnx |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-14 : 00:13:57
|
[code]select TABLE_NAME, COLUMN_NAME = stuff((select ',' + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS x where x.TABLE_NAME = c.TABLE_NAME order by ORDINAL_POSITION for xml path('')), 1, 1, '')from INFORMATION_SCHEMA.COLUMNS cwhere TABLE_NAME = 'tbl_test'group by TABLE_NAME[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
myrbs
Starting Member
10 Posts |
Posted - 2010-05-14 : 01:29:53
|
| tnx khtan, now i need to recall all the values of those columns. almost similar to the 1st oneexample table name | column valuestbl_test | u0001,myrbs,password1111,12/12/2010 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-14 : 02:09:46
|
quote:
Originally posted by myrbs
tnx khtan, now i need to recall all the values of those columns. almost similar to the 1st oneexample table name | column valuestbl_test | u0001,myrbs,password1111,12/12/2010
column values ? If you have 1 million rows you want all these rows to concatenate into 1 column ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
myrbs
Starting Member
10 Posts |
Posted - 2010-05-14 : 02:14:51
|
| for example i have test_table with this setupuid | username | password | dateu0001| myrbs| password111| 12/12/2001I only have one row result always since i use triggers i get values that are inserted on the tablethe result should showtable name | column valuestbl_test | u0001,myrbs,password1111,12/12/2001note: I dont need to define the columns since I will reuse the same script on other triggers with different column names |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-14 : 02:23:38
|
quote:
I only have one row result always since i use triggers i get values that are inserted on the table
not necessary true. The INSERTED table may contains more than 1 records. You must be doing something like select @var = colfrom inserted if you are doing this, you will be missing out records.What is your purpose of doing this ? Audit trail ? Typically for audit trail, you will just create a new table with the columns & data type identical to the table you want to audit plus several new columns to store the datetime, user & action (INSERT, UPDATE, DELETE). And the audit trail table will be inserted via the table triggers.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
myrbs
Starting Member
10 Posts |
Posted - 2010-05-14 : 02:31:42
|
quote:
Originally posted by khtan
quote:
I only have one row result always since i use triggers i get values that are inserted on the table
not necessary true. The INSERTED table may contains more than 1 records. You must be doing something like select @var = colfrom inserted if you are doing this, you will be missing out records.What is your purpose of doing this ? Audit trail ? Typically for audit trail, you will just create a new table with the columns & data type identical to the table you want to audit plus several new columns to store the datetime, user & action (INSERT, UPDATE, DELETE). And the audit trail table will be inserted via the table triggers.
KH[spoiler]Time is always against us[/spoiler]
yup this is for audit trail, but we make sure during insert,update,delete only one record/row is being manipulated.So we have this audit_table with structurepk | old val | new val | table_name | date_maintexample entry upon addition is01 | |test,testt,u0001,12/12/2001 | test_table | 05/14/2010 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-05-14 : 02:42:34
|
It will definitely be easier if your audit trail table has the same structure as the original table. Yes. you will need one audit trail table for each of the table to be audited.insert into audit_trail_table(action, datetime, user, col1, col2, col3, . . . )select 'I', getdate(), system_user, col1, col2, col3, . . .from inserted But if you ever need to inspect or access the audit trail, you don't have to do any string parsing at all
Anyway, it is your call. In your current audit_table, for the value you will need to convert it to string and concatenate together. Do take note of to take care of NULL value (and how do you know the original value is it a NULL or space ?)Exampleinsert into audit_trail ( . . . .)select isnull(strcol, '') + ',' + isnull(convert(varchar(10), intcol), '') + ',' + isnull(convert(varchar(20), datetimecol, 112), '')from . . . .
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
query problem help