USE [master]
GO
IF DB_ID('TestMain') IS NULL EXEC('CREATE DATABASE [TestMain]')
IF DB_ID('TestSecondary') IS NULL EXEC('CREATE DATABASE [TestSecondary]')
GO
IF (FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1)
BEGIN
  EXEC [TestMain].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO


USE [TestMain]
GO
CREATE FULLTEXT CATALOG [MyCatalog] WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT STOPLIST [MyStopList];
ALTER FULLTEXT STOPLIST [MyStopList] ADD '$' LANGUAGE 'Neutral';
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
GO
CREATE TABLE [dbo].[TestTable](
	[DateCreated] [datetime] NULL,
	[ParentID] [uniqueidentifier] NULL,
	[SomeText] [varchar](2000) NULL,
	[Status] [tinyint] NOT NULL DEFAULT (0),
	[TestID] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED ([TestID])
	)
GO
CREATE CLUSTERED INDEX [CIX_TestTable_DateCreated] ON [dbo].[TestTable] ([DateCreated])
GO
CREATE STATISTICS [ST_Test] ON [dbo].[TestTable]([Status])
GO

ALTER TABLE [dbo].[TestTable]  WITH CHECK ADD  CONSTRAINT [FK_SelfReference] FOREIGN KEY([ParentID]) REFERENCES [dbo].[TestTable] ([TestID])
GO
ALTER TABLE [dbo].[TestTable]  WITH CHECK ADD  CONSTRAINT [CK_Test] CHECK  ([Status]<20)
GO

CREATE FULLTEXT INDEX ON [dbo].[TestTable] (SomeText) KEY INDEX PK_TestTable ON MyCatalog WITH CHANGE_TRACKING OFF, STOPLIST = MyStopList
GO

USE [TestSecondary]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
GO
CREATE TABLE [dbo].[TestSecondaryTable](
	[DateCreated] [datetime] NOT NULL,
	[Status] [tinyint] NULL,
	[TestSecondaryID] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_TestSecondaryTable] PRIMARY KEY NONCLUSTERED ([TestSecondaryID])
	)
GO
CREATE CLUSTERED INDEX CIX_DateCreated ON TestSecondaryTable (DateCreated)
GO