Posted by Erland Sommarskog on 10/15/05 00:51

(csomberg@dwr.com) writes:
> SQL Server 2000
>
> I will to compare a normal table with a replicated audit table having
> identical columns. I wish to report on the differences between the
> data.
>
> How can I loop though a "column list" in TSQL rather than explicitly
> naming each column to compare ?

You can get the columns for a table with

SELECT name FROM syscolumns WHERE id = object_name('tbl')

I would suggest that it is best to generate the SQL statement from client
code, since client languages are better apt for string manipulation. You
can build SQL strings in T-SQL as well, and exeute them with EXEC() or
sp_executesql, but this is bulkier.

If you want to be static, one option is to use a client-language to
generate a stored procedure.

Keep in mind that you cannot just say:

SELECT ...
FROM tbl a
JOIN audit_tbl b ON a.keycol = b.keycol
WHERE a.col1 <> b.col1
AND a.col2 <> b.col2
...

Correct is:

WHERE (a.col1 <> b.col1 OR
a.col1 IS NULL AND b.col1 IS NOT NULL OR
a.col1 IS NOT NULL AND b.col1 IS NULL)
AND


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]