0

I need to create a stored procedure for comparing two tables. The input parameters are table names (containing schema). Knowing the names of the tables to be compared, I go to INFORMATION_SCHEMA.COLUMNS to get the list of columns for each table.

The problem is that in the database there are tables having columns populated automatically, e.g. 'created_by' and 'created_date'. When comparing the tables I should not pay attention to the differences in these columns if the rest of the data is the same.

Is there a common approach for finding such columns?

nismotri
  • 87
  • 2
  • 12
  • `columns populated automatically` you mean with default constraint ? check for `WHERE COLUMN_DEFAULT IS NULL` – Squirrel Dec 12 '19 at 13:09
  • 1
    If I were you I would use [`sys.columns` rather than `INFORMATION_SCHEMA.COLUMNS`](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views), then you would probably just want to include columns where `is_computed = 0 AND is_identity = 0 AND default_object_id = 0`, – GarethD Dec 12 '19 at 13:24
  • 1
    Plus one to the comment from @GarethD . Note, also, the remark in [COLUMNS (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql?view=sql-server-ver15): *" ** **Important** ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view."* (It's so important that made it **bold** and surrounded it with ** characters.) – Thom A Dec 12 '19 at 13:42
  • No, there is no common approach. For one, you assume that a default constraint is always used. It isn't and can easily (and frequently is) overridden during insert by providing a value for the associated column. And default constraints are irrelevant when a row is updated (generally speaking). You might want to ignore columns that have particular names (which also assumes your db designers have been using consistent names) like "created_date". – SMor Dec 12 '19 at 15:03
  • Thanks for the useful comments, guys! Now I know that sys.columns is better to use comparing to INFORMATION_SCHEMA.COLUMNS. Also it's clear that it's better to filter out is_computed AND is_identity columns, might help in some of the cases. – nismotri Dec 12 '19 at 15:18

1 Answers1

0

I've found this answer, it may help you: SQL exclude a column using SELECT * [except columnA] FROM tableA?

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

In that way you're removing the columns you don't want to compare, so you have that "TempTable" for the comparison in your SP

Alejo Millo
  • 63
  • 4