There is an SQL standard, but many databases have their own little extra bits or do things slightly differently. I'm having a similar problem with MySQL -> PostgreSQL. I decided it would be useful to be able to dump the structure schema to XML so I could essentialy convert it from XML to any other database format.
I have just put together a script which will take a MySQL database structure schema dump from phpMyadmin and produce an XML file from it. You could then use the XML file to create the appropriate MS SQL dump file. Having no knowledge of the MS SQL structure it's not something I could do right now, but working with an XML file should be easier than working with a raw dump. Anyway let me know if you think it would help. There probably are other scripts/utilities that will do the same thing already out there.
I haven't really decided on the DTD as yet but as an example it will take this.....
Code: Select all
# --------------------------------------------------------
#
# Table structure for table 'phpbb_forum_prune'
#
CREATE TABLE phpbb_forum_prune (
prune_id mediumint(8) UNSIGNED NOT NULL auto_increment,
forum_id smallint(5) UNSIGNED NOT NULL,
prune_days smallint(5) UNSIGNED NOT NULL,
prune_freq smallint(5) UNSIGNED NOT NULL,
PRIMARY KEY(prune_id),
KEY forum_id (forum_id)
);
# --------------------------------------------------------
#
# Table structure for table 'phpbb_forums'
#
CREATE TABLE phpbb_forums (
forum_id smallint(5) UNSIGNED NOT NULL,
cat_id mediumint(8) UNSIGNED NOT NULL,
forum_name varchar(150),
forum_desc text,
forum_status tinyint(4) DEFAULT '0' NOT NULL,
forum_order mediumint(8) UNSIGNED DEFAULT '1' NOT NULL,
forum_posts mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
forum_topics mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
forum_last_post_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
prune_next int(11),
prune_enable tinyint(1) DEFAULT '0' NOT NULL,
auth_view tinyint(2) DEFAULT '0' NOT NULL,
auth_read tinyint(2) DEFAULT '0' NOT NULL,
auth_post tinyint(2) DEFAULT '0' NOT NULL,
auth_reply tinyint(2) DEFAULT '0' NOT NULL,
auth_edit tinyint(2) DEFAULT '0' NOT NULL,
auth_delete tinyint(2) DEFAULT '0' NOT NULL,
auth_sticky tinyint(2) DEFAULT '0' NOT NULL,
auth_announce tinyint(2) DEFAULT '0' NOT NULL,
auth_vote tinyint(2) DEFAULT '0' NOT NULL,
auth_pollcreate tinyint(2) DEFAULT '0' NOT NULL,
auth_attachments tinyint(2) DEFAULT '0' NOT NULL,
PRIMARY KEY (forum_id),
KEY forums_order (forum_order),
KEY cat_id (cat_id),
KEY forum_last_post_id (forum_last_post_id)
);
# --------------------------------------------------------
#
# Table structure for table 'phpbb_posts'
#
CREATE TABLE phpbb_posts (
post_id mediumint(8) UNSIGNED NOT NULL auto_increment,
topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
forum_id smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
poster_id mediumint(8) DEFAULT '0' NOT NULL,
post_time int(11) DEFAULT '0' NOT NULL,
poster_ip char(8) NOT NULL,
post_username varchar(25),
enable_bbcode tinyint(1) DEFAULT '1' NOT NULL,
enable_html tinyint(1) DEFAULT '0' NOT NULL,
enable_smilies tinyint(1) DEFAULT '1' NOT NULL,
enable_sig tinyint(1) DEFAULT '1' NOT NULL,
post_edit_time int(11),
post_edit_count smallint(5) UNSIGNED DEFAULT '0' NOT NULL,
PRIMARY KEY (post_id),
KEY forum_id (forum_id),
KEY topic_id (topic_id),
KEY poster_id (poster_id),
KEY post_time (post_time)
);
# --------------------------------------------------------
#
# Table structure for table 'phpbb_sessions'
#
# Note that if you're running 3.23.x you may want to make
# this table a type HEAP. This type of table is stored
# within system memory and therefore for big busy boards
# is likely to be noticeably faster than continually
# writing to disk ...
#
CREATE TABLE phpbb_sessions (
session_id char(32) DEFAULT '' NOT NULL,
session_user_id mediumint(8) DEFAULT '0' NOT NULL,
session_start int(11) DEFAULT '0' NOT NULL,
session_time int(11) DEFAULT '0' NOT NULL,
session_ip char(8) DEFAULT '0' NOT NULL,
session_page int(11) DEFAULT '0' NOT NULL,
session_logged_in tinyint(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (session_id),
KEY session_user_id (session_user_id),
KEY session_id_ip_user_id (session_id, session_ip, session_user_id)
);
And produce this.....
Code: Select all
<?xml version="1.0" encoding="ISO-8859-1"?>
<database type="MySQL">
<dbtable name="phpbb_forum_prune" type="MyISAM">
<col type="mediumint" unsigned not_null auto_increment>
<name>prune_id</name>
<length>8</length>
</col>
<col type="smallint" unsigned not_null>
<name>forum_id</name>
<length>5</length>
</col>
<col type="smallint" unsigned not_null>
<name>prune_days</name>
<length>5</length>
</col>
<col type="smallint" unsigned not_null>
<name>prune_freq</name>
<length>5</length>
</col>
<key type="primary">
<col>prune_id</col>
</key>
<key name="forum_id">
<col>forum_id</col>
</key>
</dbtable>
<dbtable name="phpbb_forums" type="MyISAM">
<col type="smallint" unsigned not_null>
<name>forum_id</name>
<length>5</length>
</col>
<col type="mediumint" unsigned not_null>
<name>cat_id</name>
<length>8</length>
</col>
<col type="varchar">
<name>forum_name</name>
<length>150</length>
</col>
<col type="text">
<name>forum_desc</name>
</col>
<col type="tinyint">
<name>forum_status</name>
<length>4</length>
<default>0</default>
</col>
<col type="mediumint" unsigned not_null>
<name>forum_order</name>
<length>8</length>
<default>1</default>
</col>
<col type="mediumint" unsigned not_null>
<name>forum_posts</name>
<length>8</length>
<default>0</default>
</col>
<col type="mediumint" unsigned not_null>
<name>forum_topics</name>
<length>8</length>
<default>0</default>
</col>
<col type="mediumint" unsigned not_null>
<name>forum_last_post_id</name>
<length>8</length>
<default>0</default>
</col>
<col type="int">
<name>prune_next</name>
<length>11</length>
</col>
<col type="tinyint">
<name>prune_enable</name>
<length>1</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_view</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_read</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_post</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_reply</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_edit</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_delete</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_sticky</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_announce</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_vote</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_pollcreate</name>
<length>2</length>
<default>0</default>
</col>
<col type="tinyint">
<name>auth_attachments</name>
<length>2</length>
<default>0</default>
</col>
<key type="primary">
<col>forum_id</col>
</key>
<key name="forums_order">
<col>forum_order</col>
</key>
<key name="cat_id">
<col>cat_id</col>
</key>
<key name="forum_last_post_id">
<col>forum_last_post_id</col>
</key>
</dbtable>
<dbtable name="phpbb_posts" type="MyISAM">
<col type="mediumint" unsigned not_null auto_increment>
<name>post_id</name>
<length>8</length>
</col>
<col type="mediumint" unsigned not_null>
<name>topic_id</name>
<length>8</length>
<default>0</default>
</col>
<col type="smallint" unsigned not_null>
<name>forum_id</name>
<length>5</length>
<default>0</default>
</col>
<col type="mediumint">
<name>poster_id</name>
<length>8</length>
<default>0</default>
</col>
<col type="int">
<name>post_time</name>
<length>11</length>
<default>0</default>
</col>
<col type="char">
<name>poster_ip</name>
<length>8</length>
</col>
<col type="varchar">
<name>post_username</name>
<length>25</length>
</col>
<col type="tinyint">
<name>enable_bbcode</name>
<length>1</length>
<default>1</default>
</col>
<col type="tinyint">
<name>enable_html</name>
<length>1</length>
<default>0</default>
</col>
<col type="tinyint">
<name>enable_smilies</name>
<length>1</length>
<default>1</default>
</col>
<col type="tinyint">
<name>enable_sig</name>
<length>1</length>
<default>1</default>
</col>
<col type="int">
<name>post_edit_time</name>
<length>11</length>
</col>
<col type="smallint" unsigned not_null>
<name>post_edit_count</name>
<length>5</length>
<default>0</default>
</col>
<key type="primary">
<col>post_id</col>
</key>
<key name="forum_id">
<col>forum_id</col>
</key>
<key name="topic_id">
<col>topic_id</col>
</key>
<key name="poster_id">
<col>poster_id</col>
</key>
<key name="post_time">
<col>post_time</col>
</key>
</dbtable>
<dbtable name="phpbb_sessions" type="MyISAM">
<col type="char">
<name>session_id</name>
<length>32</length>
<default></default>
</col>
<col type="mediumint">
<name>session_user_id</name>
<length>8</length>
<default>0</default>
</col>
<col type="int">
<name>session_start</name>
<length>11</length>
<default>0</default>
</col>
<col type="int">
<name>session_time</name>
<length>11</length>
<default>0</default>
</col>
<col type="char">
<name>session_ip</name>
<length>8</length>
<default>0</default>
</col>
<col type="int">
<name>session_page</name>
<length>11</length>
<default>0</default>
</col>
<col type="tinyint">
<name>session_logged_in</name>
<length>1</length>
<default>0</default>
</col>
<key type="primary">
<col>session_id</col>
</key>
<key name="session_user_id">
<col>session_user_id</col>
</key>
<key name="session_id_ip_user_id">
<col>session_id</col>
<col>session_ip</col>
<col>session_user_id</col>
</key>
</dbtable>
</database>
</xml>