Skip to content

Tests.SchemaIncludeTablesOnly.verified.md

Latest commit

 

History

History
223 lines (187 loc) · 4.51 KB

Tests.SchemaIncludeTablesOnly.verified.md

File metadata and controls

223 lines (187 loc) · 4.51 KB

Tables

ChildTable

CREATE TABLE [dbo].[ChildTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentId] [int] NOT NULL,
	[Value] [int] NOT NULL,
 CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ColumnTypes

CREATE TABLE [dbo].[ColumnTypes](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Description] [nvarchar](max) NULL,
	[Code] [varchar](20) NULL,
	[Amount] [decimal](18, 4) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[Created] [datetime2](7) NOT NULL,
	[RowVersion] [timestamp] NOT NULL,
	[Price] [money] NULL,
	[Ratio] [float] NULL,
	[SmallNum] [smallint] NULL,
	[TinyNum] [tinyint] NULL,
	[BigNum] [bigint] NULL,
	[UniqueCode] [uniqueidentifier] NULL,
	[Notes] [text] NULL,
	[BinaryData] [varbinary](500) NULL,
	[MaxBinary] [varbinary](max) NULL,
	[FixedChar] [char](10) NULL,
	[FixedBinary] [binary](16) NULL,
	[TimeOnly] [time](7) NULL,
	[DateOnly] [date] NULL,
	[NText] [ntext] NULL,
	[Xml] [xml] NULL,
 CONSTRAINT [PK_ColumnTypes] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CompositeKeyTable

CREATE TABLE [dbo].[CompositeKeyTable](
	[Key1] [int] NOT NULL,
	[Key2] [int] NOT NULL,
	[Data] [nvarchar](200) NULL,
 CONSTRAINT [PK_CompositeKeyTable] PRIMARY KEY CLUSTERED 
(
	[Key1] ASC,
	[Key2] ASC
) ON [PRIMARY]
) ON [PRIMARY]

MultiIndexTable

CREATE TABLE [dbo].[MultiIndexTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Category] [nvarchar](50) NOT NULL,
	[Status] [int] NOT NULL,
 CONSTRAINT [PK_MultiIndexTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE UNIQUE NONCLUSTERED INDEX [IX_MultiIndexTable_Category_Name] ON [dbo].[MultiIndexTable]
(
	[Category] ASC,
	[Name] ASC
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_MultiIndexTable_Name] ON [dbo].[MultiIndexTable]
(
	[Name] ASC
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_MultiIndexTable_Status_Desc] ON [dbo].[MultiIndexTable]
(
	[Status] DESC,
	[Name] ASC
) ON [PRIMARY]

MultiTriggerTable

CREATE TABLE [dbo].[MultiTriggerTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NULL,
 CONSTRAINT [PK_MultiTriggerTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]


CREATE TRIGGER TR_MultiTrigger_Delete
ON MultiTriggerTable
AFTER DELETE
AS RAISERROR ('Delete detected', 16, 10);

ALTER TABLE [dbo].[MultiTriggerTable] ENABLE TRIGGER [TR_MultiTrigger_Delete]

CREATE TRIGGER TR_MultiTrigger_Insert
ON MultiTriggerTable
AFTER INSERT
AS RAISERROR ('Insert detected', 16, 10);

ALTER TABLE [dbo].[MultiTriggerTable] ENABLE TRIGGER [TR_MultiTrigger_Insert]

CREATE TRIGGER TR_MultiTrigger_Update
ON MultiTriggerTable
AFTER UPDATE
AS RAISERROR ('Update detected', 16, 10);

ALTER TABLE [dbo].[MultiTriggerTable] ENABLE TRIGGER [TR_MultiTrigger_Update]

MyOtherTable

CREATE TABLE [dbo].[MyOtherTable](
	[Value] [int] NULL
) ON [PRIMARY]

MyTable

CREATE TABLE [dbo].[MyTable](
	[Value] [int] NULL
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [MyIndex] ON [dbo].[MyTable]
(
	[Value] ASC
) ON [PRIMARY]

CREATE TRIGGER MyTrigger
ON MyTable
AFTER UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);

ALTER TABLE [dbo].[MyTable] ENABLE TRIGGER [MyTrigger]

ParentTable

CREATE TABLE [dbo].[ParentTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY],
 CONSTRAINT [UQ_ParentTable_Name] UNIQUE NONCLUSTERED 
(
	[Name] ASC
) ON [PRIMARY]
) ON [PRIMARY]

WithComputed

CREATE TABLE [dbo].[WithComputed](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
	[FullName]  AS (([FirstName]+' ')+[LastName]),
	[Quantity] [int] NOT NULL,
	[UnitPrice] [decimal](10, 2) NOT NULL,
	[TotalPrice]  AS ([Quantity]*[UnitPrice]) PERSISTED,
 CONSTRAINT [PK_WithComputed] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

WithDefaults

CREATE TABLE [dbo].[WithDefaults](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Status] [varchar](20) NOT NULL,
	[Created] [datetime2](7) NOT NULL,
	[Score] [int] NOT NULL,
 CONSTRAINT [PK_WithDefaults] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

SchemaTable

CREATE TABLE [TestSchema].[SchemaTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_SchemaTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
) ON [PRIMARY]
) ON [PRIMARY]