Location: PHPKode > scripts > PHP MSSQL Dump > php-mssql-dump/phpmssqldump.class.php
<?
/*
phpMSSQLDump v1.0

Author: Jonathan Hilgeman
Date: 10/23/2009

Description:
Creates a SQL dump of one or more tables in MSSQL database. The dump is in 
MSSQL-friendly format.

EASY-TO-READ DISCLAIMER:
This is a very basic tool. It should be able to dump a table's schema in a 
CREATE TABLE format, with proper constraints (e.g. primary keys), proper 
handling of identity columns, and proper ownership. It should also be able
to dump table data, including identity inserts and binary data.

I do not have access to a wide variety of MSSQL databases that make use of
different character sets, nor do I have access to many different server
versions, so this has only undergone limited testing. It seems to work well
with the databases that I have, which contain data using mostly US English
characters, and residing on MSSQL 2000 Enterprise and 2005 Express servers.

This was inspired by some original coding by Mr. NAZEEM.M.J. at WisdomBay,
who created a stored procedure called SQL_DUMP. The procedure had a lot of
critical problems and missing features, but it made a good starting point.

You can find the original stored procedure here:
http://www.wisdombay.com/downs/sqldump.php 

It also makes use of a great multi-dimensional-array-sorting function that
I came across a while back. All I know is that it was coded by Ichier2003,
and it's called array_csort().


Please feel free to give me any feedback using the contact form on my site
at http://www.sitecreative.com.



USAGE EXAMPLE #1:

// Large databases can take a while to process
set_time_limit(0);

// Connect to the database
$db = mssql_connect("dbhost","username","password");

// Dump all tables in the database called myDatabase
$MSSQLDump = new phpMSSQLDump($db,"myDatabase");
$MSSQLDump->dumpSQL();

// Write the resulting SQL to a file
file_put_contents("mssqldump.sql",$MSSQLDump->resultsSQL);




USAGE EXAMPLE #2:

// Large databases can take a while to process
set_time_limit(0);

// Connect to the database
$db = mssql_connect("dbhost","username","password");

// Dump all tables in the database called myDatabase
$MSSQLDump = new phpMSSQLDump($db,"myDatabase");

// Set some additional options
$MSSQLDump->tableName = array("myTable","anotherTable","andOneMore"); // Only dump these three tables
$MSSQLDump->stripComments = true; // Leave off the comments in the output
$MSSQLDump->stripTabs = true; // Don't indent the output
$MSSQLDump->dumpData = false; // Don't dump the data (usually much faster!)
$MSSQLDump->dumpSchema = true; // This is the default, but just so you know it's there...
$MSSQLDump->debugMode = false; // Again, the default.

// Go!
$MSSQLDump->dumpSQL();

// Write the resulting SQL to a file
file_put_contents("mssqldump.sql",$MSSQLDump->resultsSQL);


*/

class phpMSSQLDump
{
	public $tableName = "ALL"; // ALL is a keyword that pull all of the tables in the database
	public $dumpData = true; // Whether to dump data or not
	public $dumpSchema = true; // Whether to dump the schema or not
	public $dumpDataLimit = "NO LIMIT"; // Set this to a # to only dump the first # of data records per table
	public $stripComments = false; // False = add the comments in 
	public $stripTabs = false; // False = use tabs to indent the code
	public $debugMode = false; // This generates a LOT of debug data - don't use it on production servers
	public $resultsSQL = ""; // Contains the results of the dump

	
	// Normally you don't touch these
	public $dbName = ""; // Name of the database (usually set when you create a new instance of the class)
	public $dbLink = ""; // Just a placeholder for the database link
	public $tables = array(); // Holds the parsed table structures
	public $lineBreak = "\r\n"; // Pretty tandard
	public $queryEnd = ";";
	public $tabChar = "\t";



	// Construct with a Database Connection
	public function __construct($dbLink,$dbName = "")
	{
		if(is_resource($dbLink))
		{
			$this->dbLink = $dbLink;
			if($dbName)
			{
				if(!mssql_select_db($dbName,$dbLink))
				{
					return false;
				}
			}
			return true;
		}
		else
		{
			return false;
		}
	}





	protected function debugMsg($msg)
	{
		if($this->debugMode)
		{
			print $msg . "<br>\n";
			flush();
		}
	}





	protected function array_csort() {  //coded by Ichier2003
	   $args = func_get_args();
	   $marray = array_shift($args);
	
	   $msortline = "return(array_multisort(";
		 $i = 0;
	   foreach ($args as $arg) {
	       $i++;
	       if (is_string($arg)) {
	           foreach ($marray as $row) {
	               $a = strtoupper($row[$arg]);
	               $sortarr[$i][] = $a;
	           }
	       } else {
	           $sortarr[$i] = $arg;
	       }
	       $msortline .= "\$sortarr[".$i."],";
	   }
	   $msortline .= "\$marray));";
	   eval($msortline);
	   return $marray;
	}





	protected function dbQuery($query)
	{
		$this->debugMsg("Query: " . $query . "\n");
		
		if(function_exists("ob_start"))
		{
			ob_start();
			$results = mssql_query($query,$this->dbLink);
	
			// Get error message		
				$buffer=explode("(severity",ob_get_contents()); 
				$buffermsgpieces = explode("message:",$buffer[0]);
				$message = (IsSet($buffermsgpieces[1]) ? $buffermsgpieces[1] : "");
				$sql_error = $message; 
				ob_end_clean();	
		
			// Failed query?		
			if($results === false)
			{
				$this->debugMsg("Query: $query\n	Failure Message: $sql_error\n");
				return array("msg" => $sql_error,"query" => $query);
			}
			else
			{
				return $results;
			}
		}
		else
		{
			// No output buffering functions, so run it and hope for the best!
			$results = mssql_query($query,$this->dbLink);
			if($results === false)
			{
				$this->debugMsg("Query: $query\n	Failure Message: $sql_error\n");
				return array("msg" => mssql_get_last_message(),"query" => $query);
			}
			else
			{
				return $results;
			}
		}
	}





	protected function getTableStructures($tableName,$tableOwner)
	{
		$this->debugMsg("[$tableOwner].[$tableName] :: Getting columns in table...");

		// Dump Table Structure(s)
		// ==========================================================================================
		// case isnullable when 0 then 'NOT NULL' else 'NULL' end as nullable,
		$query = "select 	b.name as table_name,
											a.name as column_name,
											c.name as type,
											a.length as length,
											a.xprec as precisions,
											a.xscale as scale,
											isnullable,
											c.collation, 
											a.colorder,
											a.status
							from 		syscolumns a,
											sysobjects b,
											systypes c 
							where		b.xtype='U' 
											and a.id=b.id 
											and a.xusertype=c.xtype 
											and b.name='$tableName'
											order by b.name,a.colorder";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				while($row = mssql_fetch_assoc($results))
				{
					// Create SQL Line
					$ColName = $row["column_name"];
					$Type = $row["type"];
					$Length = $row["length"];
					$Precision = $row["precisions"];
					$Scale = $row["scale"];
					// $Nullable = ($row["isnullable"] == 0 ? false : true);
					$row["isnullable"] = ($row["isnullable"] ? true : false);
					$Nullable = ($row["isnullable"] ? "null" : "not null");
					$Collat = $row["collation"];
					$ColOrder = $row["colorder"];
  				$Status = $row["status"];
  				$IsIdentity = ($Status == 128);
  				
  				if($IsIdentity)
  				{
  					$this->tables[$tableName]["HasIdentityColumn"] = true;
  					
  					// Get seed and increment values
						$this->debugMsg("[$tableOwner].[$tableName] :: Getting seed/increment values for identity column...");

  					$query2 = "	select	ident_seed('[$tableOwner].[$tableName]') as seed, 
  															ident_incr('[$tableOwner].[$tableName]') as increment
												from 		information_schema.tables
												where 	objectproperty(object_id('[$tableOwner].[$tableName]'),'TableHasIdentity') = 1
																and   table_type = 'base table' and table_name='$tableName' and table_schema='$tableOwner'";
						$results2 = $this->dbQuery($query2);
						if(!is_array($results2))
						{
							if(mssql_num_rows($results2) > 0)
							{
								$row2 = mssql_fetch_assoc($results2);
								$IdentitySQL = " IDENTITY(" . $row2["seed"] . "," . $row2["increment"] . ")";
								$row["identity"] = $row2["seed"] . "," . $row2["increment"];
							}
							else
							{
								return array("result" => false,"msg" => "Could not find seed/increment values for identity column!","query" => $query2);
							}
						}
						else
						{
							return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
						}
						
  				}
  				else
  				{
  					$IdentitySQL = "";
  				}
  				
					$SQLColDefinition = "[" . $ColName . "]";
					
					if(in_array($Type,array("varchar","char")))
					{
						$SQLColDefinition .= " $Type($Length) collate $Collat $Nullable";
					}
					elseif(in_array($Type,array("numeric","decimal")))
					{
						$SQLColDefinition .= " $Type($Precision,$Scale) $Nullable";
					}
					elseif(in_array($Type,array("datetime","int","image","tinyint","smallint","bigint","bit","money","smallmoney","float","real","smalldatetime","ntext","sysname","uniqueidentifier")))
					{
						$SQLColDefinition .= " $Type" . $IdentitySQL . " $Nullable";
					}
					elseif(in_array($Type,array("nchar","nvarchar","binary","varbinary")))
					{
						$SQLColDefinition .= " $Type($Length) $Nullable";
					}
					elseif($Type == "text")
					{
						$SQLColDefinition .= " $Type collate $Collat $Nullable";
					}
					elseif($Type == "timestamp")
					{
						$SQLColDefinition .= " $Type";
					}
							
					// Store Parsed Definition
					$row["primarykey"] = false; // By default, columns are not part of the primary key
					$this->tables[$tableName]["Cols"][$ColName] = $row;
					$this->tables[$tableName]["ColSQLDefs"][$ColName] = $SQLColDefinition;
				}
				
				$this->tables[$tableName]["Cols"] = $this->array_csort($this->tables[$tableName]["Cols"],"colorder",SORT_ASC);
				$this->tables[$tableName]["TableSQLDef"] = "CREATE TABLE [$tableOwner].[$tableName] (" . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["ColSQLDefs"]) . "$this->lineBreak)";
			}
			else
			{
				return array("result" => false,"msg" => "Could not find any data for selected tables!","query" => $query);
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}
		
		return true;
	}






	protected function getUniqueKeyConstraints($tableName,$tableOwner)
	{
		// Get Unique Key Constraints
		// ==========================================================================================
		$this->debugMsg("[$tableOwner].[$tableName] :: Getting unique keys...");
		$query = "select distinct a.name as table_name,
															b.name as constraint_name,
															b.xtype as constraint_type 
							from 						sysobjects a,
															sysobjects b,
															sysindexes c,
															sysindexkeys d,
															syscolumns e 
							where 					b.xtype='UQ' 
															and b.parent_obj=a.id 
															and c.indid > 1 
															and c.id=b.parent_obj 
															and c.name=b.name 
															and c.id=d.id 
															and c.indid=d.indid 
															and d.id=e.id 
															and d.colid=e.colid 
															and a.name='$tableName'";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				while($row = mssql_fetch_assoc($results))
				{
					// Pull columns involved in the primary key
					$UQConstraintName = $row["constraint_name"];
					$UQColumnNames = array();
					$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in unique key $UQConstraintName...");
					$query2 = "	select 	e.name as column_name 
											from 		sysobjects a,                   
															sysobjects b,                   
															sysindexes c,                   
															sysindexkeys d,                 
															syscolumns e                    
											where 	b.xtype='UQ'                    
															and b.parent_obj=a.id           
															and b.parent_obj=c.id           
															and c.indid > 1 
															and c.name=b.name                  
															and c.id=d.id                   
															and c.indid=d.indid             
															and d.id=e.id                   
															and d.colid=e.colid             
															and b.name='$UQConstraintName'";
					$results2 = $this->dbQuery($query2);
					if(!is_array($results2))
					{
						if(mssql_num_rows($results2) > 0)
						{
							while($row2 = mssql_fetch_assoc($results2))
							{
								$ColName = $row2["column_name"];
								$UQColumnNames[] = "[" . $ColName . "]";
							}
						}
						else
						{
							return array("result" => false,"msg" => "Could not pull the column names for a unique key!","query" => $query2);
						}
					}
					else
					{
						return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
					}

					$SQLConstraintDefinition = "CONSTRAINT " . $UQConstraintName . " UNIQUE(" . join(",",$UQColumnNames) . ")";
					$this->tables[$tableName]["UQConstraintSQLDefs"][$UQConstraintName] = $SQLConstraintDefinition;				
					
 				}
				
				$this->tables[$tableName]["TableUQConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["UQConstraintSQLDefs"]) . "$this->lineBreak";
			}
			else
			{
				// Tables are not required to have constraints - not an error.
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}

		return true;
	}




	protected function getPrimaryKey($tableName,$tableOwner)
	{
		// Get Primary Key Constraints
		// ==========================================================================================
		$this->debugMsg("[$tableOwner].[$tableName] :: Getting primary key...");
		$query = "select distinct a.name as table_name,
															b.name as constraint_name,
															b.xtype as constraint_type 
							from 						sysobjects a,
															sysobjects b,
															sysindexes c,
															sysindexkeys d,
															syscolumns e 
							where 					b.xtype='PK' 
															and b.parent_obj=a.id 
															and b.parent_obj=c.id 
															and c.indid=1 
															and c.id=d.id 
															and c.indid=d.indid 
															and d.id=e.id 
															and d.colid=e.colid 
															and a.status >= 0 
															and a.name='$tableName'";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				while($row = mssql_fetch_assoc($results))
				{
					// Pull columns involved in the primary key
					$PKConstraintName = $row["constraint_name"];
					$PKColumnNames = array();
					$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in primary key $PKConstraintName...");
					$query2 = "	select 	e.name as column_name           
											from 		sysobjects a,                   
															sysobjects b,                   
															sysindexes c,                   
															sysindexkeys d,                 
															syscolumns e                    
											where 	b.xtype='PK'                    
															and b.parent_obj=a.id           
															and b.parent_obj=c.id           
															and c.indid=1                   
															and c.id=d.id                   
															and c.indid=d.indid             
															and d.id=e.id                   
															and d.colid=e.colid             
															and b.name='$PKConstraintName'";
					$results2 = $this->dbQuery($query2);
					if(!is_array($results2))
					{
						if(mssql_num_rows($results2) > 0)
						{
							while($row2 = mssql_fetch_assoc($results2))
							{
								$ColName = $row2["column_name"];
								$PKColumnNames[] = "[" . $ColName . "]";
								$this->tables[$tableName]["Cols"][$ColName]["primarykey"] = true;
							}
						}
						else
						{
							return array("result" => false,"msg" => "Could not pull the column names for the primary key!","query" => $query2);
						}
					}
					else
					{
						return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
					}
 				}
				
				$this->tables[$tableName]["TablePKConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD CONSTRAINT " . $PKConstraintName . " PRIMARY KEY(" . join(",",$PKColumnNames) . ")";
			}
			else
			{
				// Tables are not required to have constraints - not an error.
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}
		
		return true;
	}





	protected function getDefaultValues($tableName,$tableOwner)
	{
		// Get Default Constraints (Column Default Values)
		// ==========================================================================================
		$this->debugMsg("[$tableOwner].[$tableName] :: Getting default values for columns...");
		$query = "select 	b.name as table_name,
											a.name as column_name,
											d.text as default_value,
											e.name as constraint_name
							from 		syscolumns a,
											sysobjects b,
											systypes c,
											syscomments d,
											sysobjects e
							where 	b.xtype='U' 
											and a.id=b.id 
											and a.xusertype=c.xtype 
											and a.cdefault=d.id
											and e.id=d.id 
											and b.name='$tableName'
											order by a.colorder";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				while($row = mssql_fetch_assoc($results))
				{
					// Create SQL Line
					$ColName = $row["column_name"];
					$ConstraintName = $row["constraint_name"];
					// $DefaultValue = substr($row["default_value"],1,strlen($row["default_value"])-2);
					$DefaultValue = $row["default_value"];
  
					$SQLConstraintDefinition = "CONSTRAINT $ConstraintName DEFAULT " . $DefaultValue . " FOR " . $ColName;
							
					// Store Parsed Definition
					$this->tables[$tableName]["Cols"][$ColName]["default"] = $DefaultValue;
					$this->tables[$tableName]["DefaultConstraintsSQLDef"][$ColName] = $SQLConstraintDefinition;
				}
				
				$this->tables[$tableName]["TableDefaultConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["DefaultConstraintsSQLDef"]) . "$this->lineBreak";
			}
			else
			{
				// Tables are not required to have constraints - not an error.
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}
	
		return true;
	}



	protected function getForeignKeyConstraints($tableName,$tableOwner) 
	{
		// Get Foreign Key Constraints
		// ==========================================================================================
		$this->debugMsg("[$tableOwner].[$tableName] :: Getting foreign keys...");
		$query = "select distinct a.name as constraint_name,
															a.xtype as type,
															c.name as table_name,
															d.name as parent_table 
							from 						sysforeignkeys b,
															sysobjects a,
															sysobjects c,
															sysobjects d,
															syscolumns e,
															syscolumns f 
							where 					a.id=b.constid 
															and c.id=b.fkeyid 
															and d.id=b.rkeyid 
															and b.fkeyid=e.id 
															and b.fkey=e.colid 
															and b.rkeyid=f.id 
															and b.rkey=f.colid 
															and c.name='$tableName'";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				while($row = mssql_fetch_assoc($results))
				{
					// Pull columns involved in the primary key
					$ParentTable = $row["parent_table"];
					$FKConstraintName = $row["constraint_name"];
					$FKColumnNames_Foreign = array();
					$FKColumnNames_Reference = array();
					$this->debugMsg("[$tableOwner].[$tableName] :: \tGetting columns in foreign key $FKConstraintName...");
					$query2 = "	select 	e.name as foreign_key_column,
															f.name as reference_column
											from 		sysforeignkeys b,
															sysobjects a,
															sysobjects c,
															sysobjects d,
															syscolumns e,
															syscolumns f 
											where 	a.id=b.constid 
															and c.id=b.fkeyid 
															and d.id=b.rkeyid 
															and b.fkeyid=e.id 
															and b.fkey=e.colid 
															and b.rkeyid=f.id 
															and b.rkey=f.colid 
															and a.name='$FKConstraintName'";
					$results2 = $this->dbQuery($query2);
					if(!is_array($results2))
					{
						if(mssql_num_rows($results2) > 0)
						{
							while($row2 = mssql_fetch_assoc($results2))
							{
								$Foreign_ColName = $row2["foreign_key_column"];
								$Reference_ColName = $row2["foreign_key_column"];
								$FKColumnNames_Foreign[] = "[" . $Foreign_ColName . "]";
								$FKColumnNames_Reference[] = "[" . $Reference_ColName . "]";
							}
						}
						else
						{
							return array("result" => false,"msg" => "Could not pull the column names for a foreign key!","query" => $query2);
						}
					}
					else
					{
						return array("result" => false,"msg" => $results2["msg"],"query" => $results2["query"]);
					}

					$SQLConstraintDefinition = "CONSTRAINT " . $FKConstraintName . " FOREIGN KEY(" . join(",",$FKColumnNames_Foreign) . ") REFERENCES [$ParentTable](" . join(",",$FKColumnNames_Reference) . ")";
					$this->tables[$tableName]["FKConstraintSQLDefs"][$FKConstraintName] = $SQLConstraintDefinition;				
					
 				}
				
				$this->tables[$tableName]["TableFKConstraintsSQLDef"] = "ALTER TABLE [$tableOwner].[$tableName] ADD " . $this->lineBreak . $this->tabChar . join("," . $this->lineBreak . $this->tabChar,$this->tables[$tableName]["FKConstraintSQLDefs"]) . "$this->lineBreak";
			}
			else
			{
				// Tables are not required to have constraints - not an error.
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}

		return true;
	}




	protected function buildTableList()
	{
		// Check Table(s) Existence
		$this->debugMsg("Discovering all tables...");
		$query = "select table_name, table_schema owner from information_schema.tables, sysobjects where table_name = sysobjects.[name] and table_type = 'BASE TABLE' and status >= 0 ";
		if($this->tableName <> "ALL")
		{
			if(is_array($this->tableName))
			{
				$arrTableNames = array();
				foreach($this->tableName as $tmpTableName)
				{
					$arrTableNames[] = "'" . $tmpTableName . "'";
				}
				$query .= "and table_name in (" . join(",",$arrTableNames) . ")";
			}
			else
			{
				$query .= "and table_name='" . $this->tableName . "'";
			}
		}

		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			if(mssql_num_rows($results) > 0)
			{
				$this->tables = array();
				while($row = mssql_fetch_assoc($results))
				{
					$this->tables[$row["table_name"]] = array("owner" => $row["owner"]);
				}
			}
			else
			{
				return array("result" => false,"msg" => "Could not find any tables matching that criteria!","query" => $query);
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}
	}




	protected function generateHeaderComments()
	{
		// Please leave the following line at the top of every SQL dump generated with this tool. 
		// If someone besides you is looking at a SQL dump and has any comments or questions, 
		// they will know how it was generated and where to look for more info.
		
		$SQL  = "-- Created using phpMSSQLDump v" . $this->version . $this->lineBreak;
		$SQL .= "-- by Jonathan Hilgeman of SiteCreative.com" . $this->lineBreak;
		$SQL .= "-- http://www.sitecreative.com" . $this->lineBreak;
		$SQL .= $this->lineBreak;
		$SQL .= "-- NOTE: When importing large dump files, especially those with files stored" . $this->lineBreak;
		$SQL .= "--       in image columns, use the osql.exe command line program (included with" . $this->lineBreak;
		$SQL .= "--       SQL Server) with the -i switch." . $this->lineBreak;
		$SQL .= $this->lineBreak;
		$SQL .= "-- TO DO:" . $this->lineBreak;
		$SQL .= "-- . Add filegroup discovery for proper placement of keys and text fields." . $this->lineBreak;
		$SQL .= "-- . Add clustered discovery for keys." . $this->lineBreak;
		$SQL .= "-- . Add option to include NOCHECK for keys." . $this->lineBreak;
		$SQL .= "-- . Add option to drop tables before adding." . $this->lineBreak;
		$SQL .= "-- . Add trigger dumping." . $this->lineBreak;
		$SQL .= $this->lineBreak;
		$SQL .= "-- Version 1.0" . $this->lineBreak;
		$SQL .= "-- . Adapted queries from sqldump stored procedure." . $this->lineBreak;
		$SQL .= "-- . Used existing column ordering." . $this->lineBreak;
		$SQL .= "-- . Added brackets to column names for non-standard columns." . $this->lineBreak;
		$SQL .= "-- . Added brackets to table names for non-standard tables." . $this->lineBreak;
		$SQL .= "-- . Added usage of the current default constraint names." . $this->lineBreak;
		$SQL .= "-- . Improved the efficiency of checking column types." . $this->lineBreak;
		$SQL .= "-- . Added IDENTITY property with proper seed and increment values." . $this->lineBreak;
		$SQL .= "-- . Rewrote data dump from scratch." . $this->lineBreak;
		$SQL .= "-- . Added option to return the structure in a multi-dimensional PHP array." . $this->lineBreak;
		$SQL .= "-- . Added table owner to table names." . $this->lineBreak;
		$SQL .= "-- . Added proper handling of NULL values." . $this->lineBreak;
		$SQL .= $this->lineBreak;
		$SQL .= "-- Parameters:" . $this->lineBreak;
		$SQL .= "-- Dump Schema: " . ($this->dumpSchema ? "Yes" : "No") . $this->lineBreak;
		$SQL .= "-- Dump Data: " . ($this->dumpData ? "Yes" : "No") . $this->lineBreak;
		$SQL .= "-- Table(s): ";
  	
  	// Generate table list
		if($this->tableName == "ALL")
		{
			$SQL .= "(All Tables)" . $this->lineBreak;
			foreach($this->tables as $tableName => $table)
			{
				$SQL .= "--           " . $tableName . $this->lineBreak;
			}
		}
		elseif(is_array($this->tableName))
		{
			$SQL .= "(Specific Tables)" . $this->lineBreak;
			foreach($this->tableName as $tmpTableName)
			{
				$SQL .= "--           " . $tmpTableName . (!IsSet($this->tables[$tmpTableName]) ? " -- not found!" : "") . $this->lineBreak;
			}
		}
		else
		{
			$SQL .= "(Single Table)" . $this->lineBreak;
			foreach($this->tables as $tableName => $table)
			{
				$SQL .= "--           " . $tableName . $this->lineBreak;
			}
		}
  	
		$SQL .= $this->lineBreak;
		return $SQL;
	}





	protected function generateTableSchema($tableName,$table)
	{
		$this->debugMsg("Dumping schema for $tableName...");

		$SQL = "";
		
		if(!$this->stripComments)
		{
			$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			$SQL .= "-- " . $tableName . " :: Table Creation" . $this->lineBreak;
			$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
		}
		$SQL .= $table["TableSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
		unset($this->tables[$tableName]["TableSQLDef"]);
		unset($this->tables[$tableName]["ColSQLDefs"]);
		
		if(IsSet($table["TableDefaultConstraintsSQLDef"]))
		{
			if(!$this->stripComments)
			{
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
				$SQL .= "-- " . $tableName . " :: Default Values" . $this->lineBreak;
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			}
			$SQL .= $table["TableDefaultConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
			unset($this->tables[$tableName]["TableDefaultConstraintsSQLDef"]);
			unset($this->tables[$tableName]["DefaultConstraintsSQLDef"]);
		}
		
		if(IsSet($table["TablePKConstraintsSQLDef"]))
		{
			if(!$this->stripComments)
			{
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
				$SQL .= "-- " . $tableName . " :: Primary Key" . $this->lineBreak;
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			}
			$SQL .= $table["TablePKConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
			unset($this->tables[$tableName]["TablePKConstraintsSQLDef"]);
		}
		
		if(IsSet($table["TableUQConstraintsSQLDef"]))
		{
			if(!$this->stripComments)
			{
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
				$SQL .= "-- " . $tableName . " :: Unique Keys" . $this->lineBreak;
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			}
			$SQL .= $table["TableUQConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
			unset($this->tables[$tableName]["TableUQConstraintsSQLDef"]);
		}
		
		if(IsSet($table["TableFKConstraintsSQLDef"]))
		{
			if(!$this->stripComments)
			{
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
				$SQL .= "-- " . $tableName . " :: Foreign Key Constraints" . $this->lineBreak;
				$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			}
			$SQL .= $table["TableFKConstraintsSQLDef"] . $this->queryEnd . $this->lineBreak . $this->lineBreak;
			unset($this->tables[$tableName]["TableFKConstraintsSQLDef"]);
		}

		return $SQL;
	}




	protected function genererateTableData($tableName,$table)
	{
		$tableOwner = $table["owner"];

		$SQL = "";
				
		if(!$this->stripComments)
		{
			$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
			$SQL .= "-- " . $tableName . " :: Data Dump" . $this->lineBreak;
			$SQL .= "---------------------------------------------------------------------------" . $this->lineBreak;
		}

		$NumericTypes = array("tinyint","smallint","int","bigint","bit","decimal","float","money","numeric","real","small_money");
		$BinaryTypes = array("binary","image","varbinary");
		
		$tableCols = $table["Cols"];
		$query = "SELECT " . (is_numeric($this->dumpDataLimit) ? "TOP ".$this->dumpDataLimit : "") . " * FROM [" . $table["owner"] . "].[$tableName]";
		$results = $this->dbQuery($query);
		if(!is_array($results))
		{
			$this->debugMsg("Dumping data for $tableName... (" . mssql_num_rows($results) . " rows)");
			if(mssql_num_rows($results) > 0)
			{
				// Optional identity insert ON
				if($table["HasIdentityColumn"]) 
				{
					$tmpQuery = "SET IDENTITY_INSERT [$tableOwner].[$tableName] ON" . $this->queryEnd;
					$SQL .= $tmpQuery . $this->lineBreak;
				}
				
				// Dump the data
				$rowCount = 0;
				$IntervalTS = time();
				while($row = mssql_fetch_assoc($results))
				{
					$rowCount++;

					$InsertQuery = "INSERT INTO [$tableOwner].[$tableName] ";
					$InsertColumns = array();
					$InsertValues = array();
					
					foreach($tableCols as $ColName => $Col)
					{
						$InsertColumns[] = "[" . $ColName . "]";
						
						if(is_null($row[$ColName]))
						{
							$InsertValues[] = "NULL";
						}
						else
						{
							if(in_array($Col["type"],$NumericTypes))
							{
								$InsertValues[] = $row[$ColName];
							}
							elseif(in_array($Col["type"],$BinaryTypes))
							{
								$arrData = unpack("H*hex", $row[$ColName]);
								$InsertValues[] = "0x" . $arrData["hex"];
							}
							else
							{
								$InsertValues[] = "'" . str_replace("'","''",$row[$ColName]) . "'";
							}
						}
					}
					
					$InsertQuery .= "(" . join(",",$InsertColumns) . ") VALUES (" . join(",",$InsertValues) . ")";
					$SQL .= $InsertQuery . $this->queryEnd . $this->lineBreak;

					if( (($rowCount % 1000) == 0) )
					{
						$this->debugMsg("Dumped $rowCount rows so far... (" . (time() - $IntervalTS) . " seconds since last 1000 rows)");
						$IntervalTS = time();
					}
				}

				// Optional identity insert OFF					
				if($table["HasIdentityColumn"]) 
				{
					$tmpQuery = "SET IDENTITY_INSERT [$tableOwner].[$tableName] OFF" . $this->queryEnd;
					$SQL .= $tmpQuery . $this->lineBreak;
				}

				$SQL .= $this->lineBreak;
			}
			else
			{
				if(!$this->stripComments)
				{
					$SQL .= "--  No rows in table!" . $this->lineBreak . $this->lineBreak;
				}
			}
		}
		else
		{
			return array("result" => false,"msg" => $results["msg"],"query" => $results["query"]);
		}

		return $SQL;
	}





	public function dumpSQL()
	{
		// Dump Nothing?
			if(!$this->dumpSchema && !$this->dumpData) return array("result" => false,"msg" => "DumpSchema and DumpData cannot both be false!","query" => "");
		
		// Tab Character
			$this->tabChar = $this->stripTabs ? "" : $this->tabChar;
		
		// Build $this->tables array
			$this->buildTableList();
			
		// We always get the table schema(s) - it will help with creating proper INSERTs, even if we're not dumping schema(s)
			foreach($this->tables as $tableName => $table)
			{
				// Initial Table Values
				$this->tables[$tableName]["HasIdentityColumn"] = false;
				$tableOwner = $table["owner"];
    	
				$results = $this->getTableStructures($tableName,$tableOwner);
				if($results !== true) return $results;
				
				$results = $this->getDefaultValues($tableName,$tableOwner);
				if($results !== true) return $results;
  		
				$results = $this->getPrimaryKey($tableName,$tableOwner);
				if($results !== true) return $results;
  		
				$results = $this->getUniqueKeyConstraints($tableName,$tableOwner);
				if($results !== true) return $results;
  		
				$results = $this->getForeignKeyConstraints($tableName,$tableOwner);
				if($results !== true) return $results;
			}
  	
		// Sort tables
			$this->debugMsg("Sorting tables...");
			ksort($this->tables); // Sort Alphabetically
		
		// Start with the header comments	
			if(!$this->stripComments)
				$this->resultsSQL .= $this->generateHeaderComments();
			
		// Create a big text variable containing the whole dump
			foreach($this->tables as $tableName => $table)
			{
				// Dump the Schema
				if($this->dumpSchema)
					$this->resultsSQL .= $this->generateTableSchema($tableName,$table);
				
				// Dump Data
				if($this->dumpData)
				{
					$results = $this->genererateTableData($tableName,$table);
					if(is_array($results)) return $results;
					$this->resultsSQL .= $results;
				}
			}
  	
		// End of Dump
			$this->resultsSQL .= "-- END" . $this->lineBreak;
		
		// Return values
			$Return = array();
			$Return["result"] = true;
			$Return["SQL"] = $this->resultsSQL;
			$Return["Schema"] = $this->tables;
			return $Return;
	}
}

?>
Return current item: PHP MSSQL Dump