Hello,

I'd like to compare the structure of two tables in two dbs that apparently should have the same. Let's say I have two dbs (database001 and database002), in both dbs there is a table named 'invoice'. This table has dozens of columns and considering that database002 is newer than database001 probably has some more columns than database001. Moreover maybe one or more columns with the same name have different length or datatype.

How can I check for differences (more or less columns, length or datatype) ?

Please note that I don't want to compare data but the structure of the tables. Thank you in advance for any help in this matter.

Regards

MM

You could copy the CREATE code of both tables in a DIFF viewer. For example BeyondCompare.

I had this idea several times already, but with a diff command line fired by HeidiSQL. I just created a feature ticket for that with some screenshots, see issue #2010.

Thank you for tip ! I copied to Notepad++ and then compared !

Regards MM

To compare the structure of the invoice table in two databases (database001 and database002) in SQL Server, you can use the INFORMATION_SCHEMA.COLUMNS view to retrieve metadata about the columns in both tables and then compare them.

Here’s a step-by-step approach:

1. Query to Retrieve Column Information You can run the following queries in each database to retrieve the column details for the invoice table:

-- For database001 USE database001; SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'invoice' ORDER BY COLUMN_NAME;

-- For database002 USE database002; SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'invoice' ORDER BY COLUMN_NAME; 2. Compare the Results You can compare the results manually by copying the results to a spreadsheet or use SQL to automate the comparison. Below is a SQL script that can help automate this process.

3. Automated Comparison Script Here’s a script that compares the structure of the invoice table between database001 and database002.

-- Ensure you are in the context of one of the databases (e.g., database001) USE database001;

-- Create a temporary table to store structure info for both tables CREATE TABLE #TableStructureComparison ( DatabaseName NVARCHAR(50), ColumnName NVARCHAR(128), DataType NVARCHAR(128), CharacterMaximumLength INT );

-- Insert structure from database001 INSERT INTO #TableStructureComparison SELECT 'database001' AS DatabaseName, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM database001.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'invoice';

-- Insert structure from database002 INSERT INTO #TableStructureComparison SELECT 'database002' AS DatabaseName, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM database002.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'invoice';

-- Now let's compare SELECT ISNULL(a.DatabaseName, b.DatabaseName) AS DatabaseName, ISNULL(a.ColumnName, b.ColumnName) AS ColumnName, a.DataType AS DataType_in_database001, b.DataType AS DataType_in_database002, a.CharacterMaximumLength AS MaxLength_in_database001, b.CharacterMaximumLength AS MaxLength_in_database002 FROM (SELECT FROM #TableStructureComparison WHERE DatabaseName = 'database001') a FULL OUTER JOIN (SELECT FROM #TableStructureComparison WHERE DatabaseName = 'database002') b ON a.ColumnName = b.ColumnName ORDER BY ISNULL(a.ColumnName, b.ColumnName);

-- Clean up DROP TABLE #TableStructureComparison; Explanation:

TableStructureComparison: Temporary table used to store the schema details for both tables.

FULL OUTER JOIN: This join allows us to catch columns that exist in one table but not in the other. ISNULL: Handles NULL values to ensure columns or data types are displayed even if they are missing from one table. 4. Interpretation: The resulting query will show all columns present in either of the invoice tables, along with their data types and maximum lengths. Columns that exist in one database but not the other will appear with NULL values in the respective columns for the missing database. Differences in data types or lengths will be evident in the DataType_in_database001, DataType_in_database002, MaxLength_in_database001, and MaxLength_in_database002 columns. This method will give you a clear view of any discrepancies in the structure of the invoice table between the two databases.

mssqlskill.blogspot.com

Thank you I'll also try your version

MM