MSSQL Example Table with test script

/****** Object:  Table [dbo].[ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

IF  EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[ExampleTable]') AND type in (N'U'))

DROP TABLE [dbo].[ExampleTable]

GO

/****** Object:  Table [dbo].[ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[ExampleTable](

[ExampleTableID] [bigint] IDENTITY(1,1) NOT NULL,

[DESCRIP] [varchar](30) NULL,

[myvarbinary] [varbinary](1) NULL,

[mybinary] [binary] NULL,

[myimage] [image] NULL,

[myvarchar] [varchar](1) NULL,

[mymaxvarchar] [varchar](max) NULL,

[mychar] [char](1) NULL,

[mynvarchar] [nvarchar](1) NULL,

[mynchar] [nchar](1) NULL,

[mytext] [text] NULL,

[myntext] [ntext] NULL,

[myuniqueidentifier] [uniqueidentifier] NULL,

[myrowversion] [timestamp] NOT NULL,

[mybit] [bit] NULL,

[mytinyint] [tinyint] NULL,

[mysmallint] [smallint] NULL,

[myint] [int] NULL,

[mybigint] [bigint] NULL,

[mysmallmoney] [smallmoney] NULL,

[mymoney] [money] NULL,

[mynumeric] [numeric](18, 0) NULL,

[mydecimal] [decimal](18, 0) NOT NULL,

[myreal] [real] NULL,

[myfloat] [float] NULL,

[mysmalldatetime] [smalldatetime] NULL,

[mydatetime] [datetime] NULL ,

[myCalculatedColumn]  AS (case when [mydatetime]=getdate() then 'Valid' else 'EXPIRED' end),

[mysql_variant] [sql_variant] NULL ,

[myxml] [xml] NULL ,

[INSERTDT] [datetime] NULL ,

[UPDATEDDT] [datetime] NULL ,

PRIMARY KEY CLUSTERED 

(

[ExampleTableID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED 

(

[DESCRIP] 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]

/*

<doc> A template table use for copy paste </doc>

<version>0.2.6.20100502</version>

*/

GO

/****** Object:  Index [IX_ANOTHERExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

CREATE NONCLUSTERED INDEX [IX_ANOTHERExampleTable] ON [dbo].[ExampleTable] 

(

[mytinyint] ASC,

[myvarchar] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 

IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/****** Object:  Index [IX_ExampleTable]    Script Date: 05/02/2010 21:13:48 ******/

CREATE NONCLUSTERED INDEX [IX_ExampleTable] ON [dbo].[ExampleTable] 

(

[mydatetime] ASC,

[myvarchar] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, 

IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,

ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

--EXEC sys.sp_bindrule @rulename=N'[dbo].[range_rule]', @objname=N'[dbo].[ExampleTable].[mymoney]' , @futureonly='futureonly'

--GO

ALTER TABLE [dbo].[ExampleTable]  WITH CHECK ADD FOREIGN KEY([myint])

REFERENCES [dbo].[ExampleTableREF] ([ExampleTableREFID])

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ((42)) FOR [mybinary]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ('Y') FOR [mychar]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT ((0)) FOR [mydecimal]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT (getdate()) FOR [INSERTDT]

GO

ALTER TABLE [dbo].[ExampleTable] ADD  DEFAULT (getdate()) FOR [UPDATEDDT]

GO

GO

--============================================================ TEST START

declare @ScriptFileName varchar(2000)

-- get the script file name from the cmd shell 

SELECT @ScriptFileName = '$(ScriptFileName)'

SELECT @ScriptFileName + ' --- TEST START =========================================' 

declare @TableName varchar(200)

select @TableName = 'ExampleTable'

SELECT 'SELECT name from sys.tables where name =''MetaTable'''

SELECT name from sys.tables 

where name = @TableName

DECLARE @TableCount INT 

SELECT @TableCount  = COUNT(name ) from sys.tables 

where name =@TableName

if @TableCount=1

SELECT ' TEST PASSED. The Table ' + @TableName + ' EXISTS ' 

ELSE 

SELECT ' TEST FAILED. The Table ' + @TableName + ' DOES NOT EXIST ' 

SELECT @ScriptFileName + ' --- TEST END =========================================' 

--============================================================ TEST END

GO