How to compare two query results

[sql-lover]

Hi all,

 

Found this fantastic forum via Google.

 

I'm a newbie in PHP, still learning the basics, although not new into programming or T-SQL

 

I need help with a simple code I'm writing to  compare the results of this query, after being executed on two different MS-SQL instances:

 

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE = 'products'

 

Above T-SQL query basically returns the structure of a table.

 

So the 1st thing I need to do (please remember, I'm just learning PHP) I need to run the same query against two difference SQL instances that reside on same Domain.

 

2nd, on the webpage, display the result of each one so user can see the table structure.

 

And 3rd and maybe most important goal, after comparing the result set, highlight of display what's different.

 

I'm totally lost! Of course I was able to install MS-SQL, PHP and Apache on web server. They all work like a charm. But I have no idea how to display the results and later, compare those.

 

My main objective or the reason for this PHP simple app is give the user the ability to compare two tables schemas, let's say PRO vs Dev environment so the developer can validate if the tables are different or no. Variables or parameters could be MS-SQL instance of course, database and target table.

 

Can someone provide some hints or basic code where I can start with?

 

Thanks in advance,

[sql-lover]

I managed to write this code, but of course is not formatted , does not display the null columns or let the user know about it and still I don't know how to do the same with other table and compare results to highlight differences ...

 

<?php
/* Connect to the local server using Windows Authentication and
specify the AdventureWorks database as the database in use. */
$serverName = "WIN1\I01";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
     echo "Could not connect.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Set up and execute the query. */
$tsql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='ProductPhoto'";
$stmt = sqlsrv_query( $conn, $tsql);
if( $stmt === false)
{
     echo "Error in query preparation/execution.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Retrieve each row as an associative array and display the results.*/

echo "[Column Name] [Position] [Column's Default] [Data Type] [Lenght]";
echo '<br>';
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{
      echo $row['COLUMN_NAME'].", ".$row['ORDINAL_POSITION'].", ".$row['COLUMN_DEFAULT'].
   $row['DATA_TYPE'].", ".$row['CHARACTER_MAXIMUM_LENGTH'].

"\n";
      echo '<br>';	
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);
?>

 

Please help ....

[yonision]

Hi

there's a third party product out there that does just that:

 

http://bit.ly/AAIsam

 

[sql-lover]

Hi

there's a third party product out there that does just that:

 

http://bit.ly/AAIsam

 

Thanks,

 

I am aware there are several paid products available. Not looking into that. I want to develop a solution without incurring on additional costs of paid software.

 

[sql-lover]

Found a way to do it ;-) ... solution posted on PHP coding forum ...