Post reply

Convert a mySQL Database Schema to a MS SQL script - Creating a database

  • March 26, 2015 at 4:57 pm

    #398251

    Hello,

    I have a mySQL Schema that I want to use to create a MS SQL script.

    My problem is that I don't understand everything that is in the schema so it's kind of hard to translate to MS SQL.

    Hopefully there is a kind person here on the forum that can help.

    I attach the schema file.

    Thank you in advance!

    // Anders

  • March 26, 2015 at 11:24 pm

    #1786740

    Quite simple really, here are the two scripts side by side

    😎

    --SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

    --SET time_zone = "+00:00"; SET time_zone = "+00:00";

    CREATE TABLE dbo.artists ( CREATE TABLE `artists` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [name] NVARCHAR(4000) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [realname] NVARCHAR(4000) NOT NULL, `realname` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [profile] NVARCHAR(4000) NOT NULL, `profile` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_aliases ( CREATE TABLE `artists_aliases` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [alias] NVARCHAR(4000) NOT NULL, `alias` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_groups ( CREATE TABLE `artists_groups` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [group] NVARCHAR(4000) NOT NULL, `group` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_images ( CREATE TABLE `artists_images` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [type] NVARCHAR(4000) NOT NULL, `type` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [width] INT NOT NULL, `width` int(11) NOT NULL,

    [height] INT NOT NULL, `height` int(11) NOT NULL,

    [uri] NVARCHAR(4000) NOT NULL, `uri` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [uri150] NVARCHAR(4000) NOT NULL, `uri150` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_members ( CREATE TABLE `artists_members` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [member] NVARCHAR(4000) NOT NULL, `member` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_namevariations ( CREATE TABLE `artists_namevariations` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [namevariation] NVARCHAR(4000) NOT NULL, `namevariation` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.artists_urls ( CREATE TABLE `artists_urls` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    NVARCHAR(4000) NOT NULL, `url` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.genres ( CREATE TABLE `genres` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [genre] NVARCHAR(4000) NOT NULL, `genre` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.labels ( CREATE TABLE `labels` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [name] NVARCHAR(4000) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [contactinfo] NVARCHAR(4000) NOT NULL, `contactinfo` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [profile] NVARCHAR(4000) NOT NULL, `profile` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [parentlabel] NVARCHAR(4000) NOT NULL, `parentlabel` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.labels_images ( CREATE TABLE `labels_images` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [label] INT NOT NULL, `label` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [type] NVARCHAR(4000) NOT NULL, `type` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [width] INT NOT NULL, `width` int(11) NOT NULL,

    [height] INT NOT NULL, `height` int(11) NOT NULL,

    [uri] NVARCHAR(4000) NOT NULL, `uri` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [uri150] NVARCHAR(4000) NOT NULL, `uri150` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.labels_urls ( CREATE TABLE `labels_urls` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [label] INT NOT NULL, `label` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    NVARCHAR(4000) NOT NULL, `url` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.masters ( CREATE TABLE `masters` (

    [id] INT NOT NULL, `id` int(11) NOT NULL,

    [main_release] INT NOT NULL, `main_release` int(11) NOT NULL,

    [title] NVARCHAR(4000) NOT NULL, `title` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [joined_artists] NVARCHAR(4000) NOT NULL, `joined_artists` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [country] NVARCHAR(4000) NOT NULL, `country` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [year] INT NOT NULL, `year` int(11) NOT NULL,

    [notes] NVARCHAR(4000) NOT NULL, `notes` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases ( CREATE TABLE `releases` (

    [id] INT NOT NULL, `id` int(11) NOT NULL,

    [master_id] INT NOT NULL, `master_id` int(11) NOT NULL,

    [status] NVARCHAR(4000) NOT NULL, `status` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [title] NVARCHAR(4000) NOT NULL, `title` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [joined_artists] NVARCHAR(4000) NOT NULL, `joined_artists` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [country] NVARCHAR(4000) NOT NULL, `country` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [releasedate] NVARCHAR(4000) NOT NULL, `releasedate` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [notes] NVARCHAR(4000) NOT NULL, `notes` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_artists ( CREATE TABLE `releases_artists` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [master] INT NOT NULL, `master` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [namevariation] NVARCHAR(4000) NOT NULL, `namevariation` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [join] NVARCHAR(4000) NOT NULL, `join` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_formats ( CREATE TABLE `releases_formats` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [name] NVARCHAR(4000) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [quantity] INT NOT NULL, `quantity` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_formats_descriptions ( CREATE TABLE `releases_formats_descriptions` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release_format] INT NOT NULL, `release_format` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [description] NVARCHAR(4000) NOT NULL, `description` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_genres ( CREATE TABLE `releases_genres` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [master] INT NOT NULL, `master` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [genre] INT NOT NULL, `genre` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_images ( CREATE TABLE `releases_images` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [master] INT NOT NULL, `master` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [type] NVARCHAR(4000) NOT NULL, `type` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [width] INT NOT NULL, `width` int(11) NOT NULL,

    [height] INT NOT NULL, `height` int(11) NOT NULL,

    [uri] NVARCHAR(4000) NOT NULL, `uri` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [uri150] NVARCHAR(4000) NOT NULL, `uri150` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_labels ( CREATE TABLE `releases_labels` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [catno] NVARCHAR(4000) NOT NULL, `catno` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [name] NVARCHAR(4000) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_identifiers ( CREATE TABLE `releases_identifiers` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [type] NVARCHAR(4000) NOT NULL, `type` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [value] NVARCHAR(4000) NOT NULL, `value` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [description] NVARCHAR(4000) NOT NULL, `description` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_styles ( CREATE TABLE `releases_styles` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [master] INT NOT NULL, `master` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [style] INT NOT NULL, `style` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_tracks ( CREATE TABLE `releases_tracks` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [release] INT NOT NULL, `release` int(11) NOT NULL,

    [master] INT NOT NULL, `master` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [position] NVARCHAR(4000) NOT NULL, `position` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [title] NVARCHAR(4000) NOT NULL, `title` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [duration] INT NOT NULL, `duration` int(11) NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_tracks_artists ( CREATE TABLE `releases_tracks_artists` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [track] INT NOT NULL, `track` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [artist] INT NOT NULL, `artist` int(11) NOT NULL,

    [namevariation] NVARCHAR(4000) NOT NULL, `namevariation` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [join] NVARCHAR(4000) NOT NULL, `join` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.releases_tracks_extraartists ( CREATE TABLE `releases_tracks_extraartists` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [track] INT NOT NULL, `track` int(11) NOT NULL,

    [number] INT NOT NULL, `number` int(11) NOT NULL,

    [name] NVARCHAR(4000) NOT NULL, `name` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [namevariation] NVARCHAR(4000) NOT NULL, `namevariation` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    [role] NVARCHAR(4000) NOT NULL, `role` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE dbo.styles ( CREATE TABLE `styles` (

    [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    [style] NVARCHAR(4000) NOT NULL, `style` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    PRIMARY KEY (`id`)

    ) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    --/* Full text search */

    --CREATE TABLE IF NOT EXISTS `releases_fts ( CREATE TABLE IF NOT EXISTS `releases_fts` (

    -- [id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, `id` int(11) NOT NULL AUTO_INCREMENT,

    -- [fts] NVARCHAR(4000) NOT NULL, `fts` mediumtext COLLATE utf8_unicode_ci NOT NULL,

    -- , PRIMARY KEY (`id`),

    -- FULLTEXT KEY `fts] (`fts`) FULLTEXT KEY `fts` (`fts`)

    --) ; ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

  • March 27, 2015 at 1:03 am

    #1786752

    Thank you very much!

    // Anders

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy