Multiple columns and row values

Hi. I am trying to write a single stored procedure which would trace the
changes made by a user on a table. I would like this implemented on multiple
tables having the most efficient code possible. Is it possible to browse to
a
table and extract all its columns (column_name from information_schema) and
get the row value for these columns having only a record id. I was able to
get the column_name but unable to make a sql statement retrieving the values
of the column_name.
Any help is appreciated.Can you post more detail? What do you mean "columns having only a record
id"? If you post actual DDL, sample data, and sample output, that would be
best...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> Hi. I am trying to write a single stored procedure which would trace the
> changes made by a user on a table. I would like this implemented on
multiple
> tables having the most efficient code possible. Is it possible to browse
to a
> table and extract all its columns (column_name from information_schema)
and
> get the row value for these columns having only a record id. I was able to
> get the column_name but unable to make a sql statement retrieving the
values
> of the column_name.
> Any help is appreciated.
>|||OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is in
t
and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
COL1 as primary key and an int. I want to write a stored procedure to extrac
t
a record from TABLE1 or TABLE2 (table_name being passed as parameter) with a
record id (COL1) and loops over the columns and its row values. So I can use
this stored procedure in these two tables or in any other table as long as
the primary key is int. better if the int (primary key) is eliminated as a
constraint as well..
so a query from information_schema would give me the column names (given the
table as a parameter) but how do i extract the row value of those columns if
i know the record primary key value (COL1).
Thanks.
"Adam Machanic" wrote:

> Can you post more detail? What do you mean "columns having only a record
> id"? If you post actual DDL, sample data, and sample output, that would b
e
> best...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:871EF4F4-B4A4-493D-BBC1-6787783C1BFF@.microsoft.com...
> multiple
> to a
> and
> values
>
>|||"[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> OK. Sorry about that.. I have TABLE1 with COL1, COL2, COL3, COL4. COL1 is
int
> and the primary key. also I have TABLE2 with COL1, COL2, COL3, etc.. with
> COL1 as primary key and an int. I want to write a stored procedure to
extract
> a record from TABLE1 or TABLE2 (table_name being passed as parameter) with
a
> record id (COL1) and loops over the columns and its row values. So I can
use
> this stored procedure in these two tables or in any other table as long as
> the primary key is int. better if the int (primary key) is eliminated as a
> constraint as well..
Why do you want to do this? You're completely eliminating most of the
benefits of using stored procedures, and DBMSs in general -- keeping the
application out of the data management business! My advice to you is to
very carefully consider your motives for doing this -- I can guarantee that
you will not end up simplifying anything by tightly coupling your
application to your database (which is what this stored procedure will
accomplish). You can take that with however many grains of salt as you
choose, but you may want to search the archives of this group for lots of
threads about these kinds of techniques and the problems they invariably
cause.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||i know.. well. i have written a large application and i everything is almost
in place. I just need this user tracking history. who inserts what and who
updates which record.. and so on.. i can use triggers but that would mean
going to each one of the tables which is 100+. I want a single stored
procedure, called from a class that I can reuse on every page. So i need the
columns and the current values so i can record them in a table where it can
be retrieved in a report. but this has to go on a per column value.. and the
pages are a lot less that the tables..
"Adam Machanic" wrote:

> "[Alan Flores]" <AlanFlores@.discussions.microsoft.com> wrote in message
> news:D66A9835-AAAF-4811-8F6F-1473F095166A@.microsoft.com...
> int
> extract
> a
> use
>
> Why do you want to do this? You're completely eliminating most of the
> benefits of using stored procedures, and DBMSs in general -- keeping the
> application out of the data management business! My advice to you is to
> very carefully consider your motives for doing this -- I can guarantee tha
t
> you will not end up simplifying anything by tightly coupling your
> application to your database (which is what this stored procedure will
> accomplish). You can take that with however many grains of salt as you
> choose, but you may want to search the archives of this group for lots of
> threads about these kinds of techniques and the problems they invariably
> cause.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>