How do I create MS SQL tables for a fresh WordPress installation?

1 Views

I am working on a new WordPress site to host an online bookstore for my publishing company. As I am a .NET developer I would rather use MS SQL in combination with WP DB Abstraction (https://wordpress.org/plugins/wordpress-database-abstraction/) plugin. I did a fresh install of Brandoo WordPress (http://www.microsoft.com/web/gallery/brandoowordpressmssql.aspx) which worked fine. When I wanted to create copies of the database so I could create another instances of WordPress I ran into problems as not all of the details relating to tables structure, keys, constraints and default values were not copied over.

What are the CREATE TABLE statements I need to create all the WordPress related Tables in MS SQL Server?

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. For SQL Server 2012, the following SQL script creates all the needed tables for WordPress 3.8.1.

    USE [WordPressDb]
GO

/****** Object:  Table [dbo].[wp_commentmeta]    Script Date: 4/6/2014 5:35:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_commentmeta](
    [meta_id] [bigint] IDENTITY(1,1) NOT NULL,
    [comment_id] [bigint] NOT NULL,
    [meta_key] [nvarchar](255) NULL,
    [meta_value] [nvarchar](max) NULL,
 CONSTRAINT [wp_commentmeta_meta_id] PRIMARY KEY CLUSTERED 
(
    [meta_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_commentmeta] ADD  DEFAULT ('0') FOR [comment_id]
GO

ALTER TABLE [dbo].[wp_commentmeta] ADD  DEFAULT (NULL) FOR [meta_key]
GO

/****** Object:  Table [dbo].[wp_comments]    Script Date: 4/6/2014 5:37:04 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_comments](
    [comment_ID] [bigint] IDENTITY(1,1) NOT NULL,
    [comment_post_ID] [bigint] NOT NULL,
    [comment_author] [nvarchar](max) NOT NULL,
    [comment_author_email] [nvarchar](100) NOT NULL,
    [comment_author_url] [nvarchar](200) NOT NULL,
    [comment_author_IP] [nvarchar](100) NOT NULL,
    [comment_date] [datetime] NOT NULL,
    [comment_date_gmt] [datetime] NOT NULL,
    [comment_content] [nvarchar](max) NOT NULL,
    [comment_karma] [int] NOT NULL,
    [comment_approved] [nvarchar](20) NOT NULL,
    [comment_agent] [nvarchar](255) NOT NULL,
    [comment_type] [nvarchar](20) NOT NULL,
    [comment_parent] [bigint] NOT NULL,
    [user_id] [bigint] NOT NULL,
 CONSTRAINT [wp_comments_comment_ID] PRIMARY KEY CLUSTERED 
(
    [comment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('0') FOR [comment_post_ID]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('') FOR [comment_author_email]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('') FOR [comment_author_url]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('') FOR [comment_author_IP]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT (getdate()) FOR [comment_date]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT (getdate()) FOR [comment_date_gmt]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('0') FOR [comment_karma]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('1') FOR [comment_approved]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('') FOR [comment_agent]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('') FOR [comment_type]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('0') FOR [comment_parent]
GO

ALTER TABLE [dbo].[wp_comments] ADD  DEFAULT ('0') FOR [user_id]
GO

/****** Object:  Table [dbo].[wp_links]    Script Date: 4/6/2014 5:37:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_links](
    [link_id] [bigint] IDENTITY(1,1) NOT NULL,
    [link_url] [nvarchar](255) NOT NULL,
    [link_name] [nvarchar](255) NOT NULL,
    [link_image] [nvarchar](255) NOT NULL,
    [link_target] [nvarchar](25) NOT NULL,
    [link_description] [nvarchar](255) NOT NULL,
    [link_visible] [nvarchar](20) NOT NULL,
    [link_owner] [bigint] NOT NULL,
    [link_rating] [int] NOT NULL,
    [link_updated] [datetime] NOT NULL,
    [link_rel] [nvarchar](255) NOT NULL,
    [link_notes] [nvarchar](max) NOT NULL,
    [link_rss] [nvarchar](255) NOT NULL,
 CONSTRAINT [wp_links_link_id] PRIMARY KEY CLUSTERED 
(
    [link_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_url]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_name]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_image]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_target]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_description]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('Y') FOR [link_visible]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('1') FOR [link_owner]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('0') FOR [link_rating]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT (getdate()) FOR [link_updated]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_rel]
GO

ALTER TABLE [dbo].[wp_links] ADD  DEFAULT ('') FOR [link_rss]
GO

/****** Object:  Table [dbo].[wp_options]    Script Date: 4/6/2014 5:38:02 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_options](
    [option_id] [bigint] IDENTITY(1,1) NOT NULL,
    [option_name] [nvarchar](64) NOT NULL,
    [option_value] [nvarchar](max) NOT NULL,
    [autoload] [nvarchar](20) NOT NULL,
 CONSTRAINT [wp_options_option_id] PRIMARY KEY CLUSTERED 
(
    [option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [wp_options_option_name] UNIQUE NONCLUSTERED 
(
    [option_name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_options] ADD  DEFAULT ('') FOR [option_name]
GO

ALTER TABLE [dbo].[wp_options] ADD  DEFAULT ('yes') FOR [autoload]
GO


/****** Object:  Table [dbo].[wp_postmeta]    Script Date: 4/6/2014 5:38:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_postmeta](
    [meta_id] [bigint] IDENTITY(1,1) NOT NULL,
    [post_id] [bigint] NOT NULL,
    [meta_key] [nvarchar](255) NULL,
    [meta_value] [nvarchar](max) NULL,
 CONSTRAINT [wp_postmeta_meta_id] PRIMARY KEY CLUSTERED 
(
    [meta_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_postmeta] ADD  DEFAULT ('0') FOR [post_id]
GO

ALTER TABLE [dbo].[wp_postmeta] ADD  DEFAULT (NULL) FOR [meta_key]
GO


/****** Object:  Table [dbo].[wp_posts]    Script Date: 4/6/2014 5:38:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_posts](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [post_author] [bigint] NOT NULL,
    [post_date] [datetime] NOT NULL,
    [post_date_gmt] [datetime] NOT NULL,
    [post_content] [nvarchar](max) NOT NULL,
    [post_title] [nvarchar](max) NOT NULL,
    [post_excerpt] [nvarchar](max) NOT NULL,
    [post_status] [nvarchar](20) NOT NULL,
    [comment_status] [nvarchar](20) NOT NULL,
    [ping_status] [nvarchar](20) NOT NULL,
    [post_password] [nvarchar](20) NOT NULL,
    [post_name] [nvarchar](200) NOT NULL,
    [to_ping] [nvarchar](max) NOT NULL,
    [pinged] [nvarchar](max) NOT NULL,
    [post_modified] [datetime] NOT NULL,
    [post_modified_gmt] [datetime] NOT NULL,
    [post_content_filtered] [nvarchar](max) NOT NULL,
    [post_parent] [bigint] NOT NULL,
    [guid] [nvarchar](255) NOT NULL,
    [menu_order] [int] NOT NULL,
    [post_type] [nvarchar](20) NOT NULL,
    [post_mime_type] [nvarchar](100) NOT NULL,
    [comment_count] [bigint] NOT NULL,
 CONSTRAINT [wp_posts_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('0') FOR [post_author]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT (getdate()) FOR [post_date]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT (getdate()) FOR [post_date_gmt]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('publish') FOR [post_status]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('open') FOR [comment_status]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('open') FOR [ping_status]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('') FOR [post_password]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('') FOR [post_name]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT (getdate()) FOR [post_modified]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT (getdate()) FOR [post_modified_gmt]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('0') FOR [post_parent]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('') FOR [guid]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('0') FOR [menu_order]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('post') FOR [post_type]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('') FOR [post_mime_type]
GO

ALTER TABLE [dbo].[wp_posts] ADD  DEFAULT ('0') FOR [comment_count]
GO

/****** Object:  Table [dbo].[wp_term_relationships]    Script Date: 4/6/2014 5:38:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_term_relationships](
    [object_id] [bigint] NOT NULL,
    [term_taxonomy_id] [bigint] NOT NULL,
    [term_order] [int] NOT NULL,
 CONSTRAINT [wp_term_relationships_object_id_term_taxonomy_id] PRIMARY KEY CLUSTERED 
(
    [object_id] ASC,
    [term_taxonomy_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_term_relationships] ADD  DEFAULT ((0)) FOR [object_id]
GO

ALTER TABLE [dbo].[wp_term_relationships] ADD  DEFAULT ((0)) FOR [term_taxonomy_id]
GO

ALTER TABLE [dbo].[wp_term_relationships] ADD  DEFAULT ((0)) FOR [term_order]
GO


/****** Object:  Table [dbo].[wp_term_taxonomy]    Script Date: 4/6/2014 5:39:09 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_term_taxonomy](
    [term_taxonomy_id] [bigint] IDENTITY(1,1) NOT NULL,
    [term_id] [bigint] NOT NULL,
    [taxonomy] [nvarchar](32) NOT NULL,
    [description] [nvarchar](max) NOT NULL,
    [parent] [bigint] NOT NULL,
    [count] [bigint] NOT NULL,
 CONSTRAINT [wp_term_taxonomy_term_taxonomy_id] PRIMARY KEY CLUSTERED 
(
    [term_taxonomy_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_term_taxonomy] ADD  DEFAULT ((0)) FOR [term_id]
GO

ALTER TABLE [dbo].[wp_term_taxonomy] ADD  DEFAULT ('') FOR [taxonomy]
GO

ALTER TABLE [dbo].[wp_term_taxonomy] ADD  DEFAULT ((0)) FOR [parent]
GO

ALTER TABLE [dbo].[wp_term_taxonomy] ADD  DEFAULT ((0)) FOR [count]
GO


/****** Object:  Table [dbo].[wp_terms]    Script Date: 4/6/2014 5:39:27 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_terms](
    [term_id] [bigint] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](200) NOT NULL,
    [slug] [nvarchar](200) NOT NULL,
    [term_group] [bigint] NOT NULL,
 CONSTRAINT [wp_terms_term_id] PRIMARY KEY CLUSTERED 
(
    [term_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_terms] ADD  DEFAULT ('') FOR [name]
GO

ALTER TABLE [dbo].[wp_terms] ADD  DEFAULT ('') FOR [slug]
GO

ALTER TABLE [dbo].[wp_terms] ADD  DEFAULT ((0)) FOR [term_group]
GO


/****** Object:  Table [dbo].[wp_usermeta]    Script Date: 4/6/2014 5:39:41 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_usermeta](
    [umeta_id] [bigint] IDENTITY(1,1) NOT NULL,
    [user_id] [bigint] NOT NULL,
    [meta_key] [nvarchar](255) NULL,
    [meta_value] [nvarchar](max) NULL,
 CONSTRAINT [wp_usermeta_umeta_id] PRIMARY KEY CLUSTERED 
(
    [umeta_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_usermeta] ADD  DEFAULT ('0') FOR [user_id]
GO

ALTER TABLE [dbo].[wp_usermeta] ADD  DEFAULT (NULL) FOR [meta_key]
GO


/****** Object:  Table [dbo].[wp_users]    Script Date: 4/6/2014 5:39:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[wp_users](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [user_login] [nvarchar](60) NOT NULL,
    [user_pass] [nvarchar](64) NOT NULL,
    [user_nicename] [nvarchar](50) NOT NULL,
    [user_email] [nvarchar](100) NOT NULL,
    [user_url] [nvarchar](100) NOT NULL,
    [user_registered] [datetime] NOT NULL,
    [user_activation_key] [nvarchar](60) NOT NULL,
    [user_status] [int] NOT NULL,
    [display_name] [nvarchar](250) NOT NULL,
 CONSTRAINT [wp_users_ID] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_login]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_pass]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_nicename]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_email]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_url]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT (getdate()) FOR [user_registered]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [user_activation_key]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('0') FOR [user_status]
GO

ALTER TABLE [dbo].[wp_users] ADD  DEFAULT ('') FOR [display_name]
GO