You can also download the code from here:
http://www.fcs.uga.edu/~john/SQL_ColInfo.zip
-John
Code: Select all
<?php
/*
SQL_ColInfo.php
-John Taylor
Oct-17-2006
The program returns column information about each table of each database
from the sql server passed in from the command-line. It excludes the
the sql system tables. You can exclude other databases by modifying
the $db_excludes variable. You can pass an filename as an optional 2nd
command line argument.
Output
------
the number of columns, in curly braces
data type of each column
if NULL is allowed
precision & length, length (in square brackets) refers to space on disk
the default value (if there is one)
It was tested with php 4.4.2 on Windows XP (SP2) on a SQL Server 2005 (SP1)
server. When viewing the output in a text editor, make sure that tabs are
set to 8 spaces, otherwise the formatting will look awkward.
See also:
http://msdn2.microsoft.com/en-us/library/ms176077.aspx
*/
set_time_limit(0);
$db_server = "";
$db_excludes = array( "master", "tempdb", "model", "msdb", "pubs", "Northwind" );
$db = 0;
$output_file = "";
$using_output_file = 0;
$fp = 0;
$current_col_count = 0;
$one_gig_a = pow(1024,3) - 2;
$one_gig_z = pow(1024,3) + 2;
$two_gig_a = $one_gig_a * 2;
$two_gig_z = 2147483647;
$line = "=============================================================================";
$hdr = "Column Name\t\tData Type, Nullable, Precision [Length], def: Default";
// display program command-line options
function Usage() {
global $_SERVER;
print "\r\n";
print "Usage: " . $_SERVER["argv"][0] . " [ sql-server ] [ output file ]\r\n";
print " [ output file ] is optional\r\n\r\n";
print "\r\n";
print "The program returns column information about each table of each database\r\n";
print "from the sql server passed in from the command-line. It excludes the\r\n";
print "the sql system tables.\r\n";
}
// adapted from Klaus's comments, http://www.php.net/manual/en/function.m ... essage.php
function get_sql_error() {
global $db;
$sql = "select @@ERROR as code";
$result = @mssql_query($sql, $db);
if( FALSE == $result ) {
return "[???] Unknown error.";
}
$row = mssql_fetch_array($result);
$code = $row["code"]; // error code
// to get the correct msglangid, pick a value with this select stmt:
// select name,msglangid from master.dbo.syslanguages order by 1,2
// here are a few values...
// us_english=1033, britsh=1033, deutsch=1031, espanol=3082, francais=1036, italiano=1040, nederlands=1043, polski=1045
$sql = "select cast (description as varchar(255)) as errtxt from master.dbo.sysmessages where error = $code and msglangid = 1033";
$result = @mssql_query($sql, $db);
if( FALSE == $result ) {
return "[???] Unknown error.";
}
$row = mssql_fetch_array($result);
if($row) {
$text = $row["errtxt"]; // error text (with placeholders)
} else {
$text = "[???] Unknown error.";
}
mssql_free_result($result);
return "[$code] $text";
}
// filenames can contain these 2 macros, useful if you periodically run this script from a Scheduled Task
// replace __NOW__ with the current date & time in
// this format: [year][month][day]_[hour][min][sec]
// replace __SERVER__ with $_SERVER["argv"][1]
function ExpandMacros($fname) {
global $_SERVER;
$now = strftime("%Y%m%d_%H%M%S");
$result = ereg_replace("__NOW__", $now, $fname);
return ereg_replace("__SERVER__", $_SERVER["argv"][1], $result);
}
// given a handle to a database, return an array of database names
function GetDatabaseNames() {
global $db, $db_excludes;
$query = "select name from master.dbo.sysdatabases";
$result = mssql_query( $query, $db );
do {
while ($row = mssql_fetch_row($result)) {
$databases[] = $row[0];
}
} while ( mssql_next_result($result));
mssql_free_result($result);
return array_diff( $databases, $db_excludes);
}
// given a database name, return an array including filename and filesize
// this will work only if you run the script directly from the database server itself
function GetDatabaseMetaData($name) {
global $db;
$fsize = -1;
$query = "select FILENAME from MASTER.DBO.SYSDATABASES where NAME=\"$name\"";
$result = mssql_query( $query, $db );
do {
while ($row = mssql_fetch_row($result)) {
$fname = $row[0];
if(file_exists($fname)) {
$fsobj = new COM("Scripting.FileSystemObject");
$data = $fsobj->GetFile($fname);
$fsize = ($data->Size) + 1 - 1;
$data=0;
}
}
} while ( mssql_next_result($result));
mssql_free_result($result);
return array($fname, $fsize);
}
// given a handle to a database, return an array of table names within that database
function GetTableNames() {
global $db;
$query = "select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME";
$result = mssql_query( $query, $db );
do {
while ($row = mssql_fetch_row($result)) {
$tables[] = $row[0];
}
} while ( mssql_next_result($result));
mssql_free_result($result);
return $tables;
}
// given an individual table, return the column information
function ColNames($tbl, $db_name) {
global $db, $current_col_count;
global $one_gig_a, $one_gig_z, $two_gig_a, $two_gig_z;
$col_names = "";
$current_col_count = 0;
$query = "sp_columns $tbl";
$result = @mssql_query( $query, $db );
if( FALSE == $result ) {
$err_msg = get_sql_error();
print "\r\n";
print "In database: " . $db_name . "\r\n";
print "Can not query table: " . $tbl . "\r\n";
print $err_msg . "\r\n";
return -1;
}
do {
while ($row = mssql_fetch_row($result)) {
$name = trim($row[3]);
$dtype = trim($row[5]);
$precision = intval(trim($row[6]));
if( $precision >= $one_gig_a && $precision <= $one_gig_z ) {
$precision = "1 Gig";
}
if( $precision >= $two_gig_a && $precision <= $two_gig_z ) {
$precision = "2 Gigs";
}
if( $precision > $one_gig_a ) {
$precision = number_format($precision);
}
$length = trim($row[7]);
if( $length >= $one_gig_a && $length <= $one_gig_z ) {
$length = "1 Gig";
}
if( $length >= $two_gig_a && $length <= $two_gig_z ) {
$length = "2 Gigs";
}
if( $length > $one_gig_a ) {
$length = number_format($length);
}
//$nullable = trim($row[10]);
$column_def = trim($row[12]);
$is_nullable = trim($row[17]);
if( 0 == strlen($is_nullable) ) {
$is_nullable = "UNKNOWN";
}
$current_col_count += 1;
if( strlen($name) <= 7 ) {
$tab = "\t\t\t\t\t";
} elseif( strlen($name) <= 15 ) {
$tab = "\t\t\t\t";
} elseif( strlen($name) <= 23 ) {
$tab = "\t\t\t";
} elseif( strlen($name) <= 31 ) {
$tab = "\t\t";
} else {
$tab = "\t";
}
$last_comma = (strlen($column_def)) ? ", def: " : "";
$col_names = sprintf("%s\r\n%s%s%s, %s, %s [%s]%s %s", $col_names, $name, $tab, $dtype, $is_nullable, $precision, $length, $last_comma, $column_def);
}
} while ( mssql_next_result($result));
mssql_free_result($result);
$col_names = substr($col_names,strlen("\r\n")) . "\r\n\r\n";
return $col_names;
}
// given a list of tables, return the length of the longest table name
// this is used to format the output & make it look nice
function GetMaxTableNameLength($arr) {
$max = -1;
foreach($arr as $a) {
$l = strlen($a);
if( $l > $max ) {
$max = $l;
}
}
return $max;
}
// output that displays pertinent info about database table definitions
function PrintRowCount($now, $db_name, $tables, $max_len) {
global $db_server, $line, $output_file, $using_output_file, $fp;
global $db_file_size, $line, $hdr, $current_col_count;
$db_file_size = 0;
$metadata = GetDatabaseMetaData($db_name);
$fname = $metadata[0];
$db_file_size = $metadata[1];
fwrite( $fp, "\r\n");
fwrite( $fp, "=============================================================================\r\n" );
fwrite( $fp, "date : " . $now . "\r\n");
fwrite( $fp, "server : " . $db_server . "\r\n");
fwrite( $fp, "database : " . $db_name . "\r\n");
fwrite( $fp, "# of tables : " . sizeof($tables) . "\r\n");
fwrite( $fp, "=============================================================================\r\n" );
if( $db_file_size >= 0 ) {
fwrite( $fp, "file path : " . $fname . "\r\n");
fwrite( $fp, "file size : " . number_format($db_file_size) . "\r\n");
}
fwrite( $fp, "\r\n");
fwrite( $fp, $hdr . "\r\n\r\n");
$total_cols=0;
foreach( $tables as $t ) {
$count = ColNames($t, $db_name);
$line_tbl_name = substr("-------------------------------------------------------------------------------",0,strlen($db_name)+1+strlen($t)+5+strlen($current_col_count));
fwrite( $fp, $line_tbl_name . "\r\n");
fwrite( $fp, $db_name . "." . $t . " { " . $current_col_count . " }\r\n");
fwrite( $fp, $line_tbl_name . "\r\n");
fwrite( $fp, "$count\r\n" );
$total_cols += intval($current_col_count);
}
fwrite( $fp, "\r\n");
fflush($fp);
return $total_cols;
}
// program exectuion starts here
function main() {
global $_SERVER;
global $db, $db_server, $output_file, $using_output_file, $fp;
global $db_file_size, $line;
$dbg = 0; // default is 0, set to 1 to turn on debugging
if( $_SERVER["argc"] != 2 && $_SERVER["argc"] != 3 ) return Usage();
$db_server = $_SERVER["argv"][1];
if( 3 == $_SERVER["argc"] ) {
$using_output_file = 1;
$output_file = $_SERVER["argv"][2];
$output_file = ExpandMacros($output_file);
$fp = fopen( $output_file, "w");
} else {
$fp = fopen("php://output", "w");
}
$db_total_cols = 0;
$db_total_tables = 0;
$db_total_file_size = 0;
$now = strftime("%Y-%m-%d %H:%M:%S");
// connect to server and select the database
// could also use something like: $db = mssql_connect( "\\\\$db_server\\pipe\\sql\\query", "ExampleReports", "" );
$db = @mssql_connect( $db_server );
if( FALSE == $db ) {
print "\r\n";
print "Unable to make database connection to host: " . $db_server . "\r\n\r\n";
fclose($fp);
return;
}
$db_list = GetDatabaseNames();
if( 1 == $dbg) {
print "\r\nDatabases:\r\n";
print "==========";
print "\r\n";
var_dump($db_list);
print "\r\n\r\n";
}
foreach( $db_list as $current_db ) {
$valid = mssql_select_db($current_db, $db);
$tables = GetTableNames();
if( 1 == $dbg) {
print "\r\n($current_db) tables:\r\n";
print "================================================";
print "\r\n";
var_dump($db_list);
print "\r\n\r\n";
}
$db_total_tables += sizeof($tables);
$max_tbl_len = GetMaxTableNameLength($tables);
$db_total_cols += PrintRowCount($now, $current_db, $tables, $max_tbl_len);
$db_total_file_size += $db_file_size;
}
mssql_close($db);
fwrite( $fp, $line . "\r\n" );
fwrite( $fp, "Total number of databases : " . sizeof($db_list) . "\r\n");
fwrite( $fp, "Grand total number of tables : " . number_format($db_total_tables) . "\r\n");
fwrite( $fp, "Grand total number of columns : " . number_format($db_total_cols) . "\r\n");
fwrite( $fp, "\r\n");
return;
if( 1 == $using_output_file) {
fflush($fp);
fclose($fp);
}
}
main();
?>