sql server create table script samples

2008-04-30


*BEGIN SQL QUERY

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Actions') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Actions

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Authentications') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Authentications

GO

*BEGIN SQL QUERY

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Actions') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Actions

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Authentications') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Authentications

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Groups') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Groups

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_CustomCommands') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_CustomCommands

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_EventRules') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_EventRules

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_ProtocolCommands') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_ProtocolCommands

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_ResultCodes') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_ResultCodes

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_SocketConnections') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_SocketConnections

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_Transactions') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_Transactions

GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tbl_ClientOperations') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table dbo.tbl_ClientOperations

GO

CREATE TABLE dbo.tbl_Transactions (

TransactionID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Transactions PRIMARY KEY  CLUSTERED ,

ParentTransactionID numeric(18, 0) NULL REFERENCES tbl_Transactions(TransactionID),

TransactionObject varchar (50)  NOT NULL

)

GO

CREATE TABLE dbo.tbl_EventRules (

EventID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_EventRules PRIMARY KEY CLUSTERED,

Time_stamp datetime NOT NULL ,

SiteName varchar (50)  NULL ,

EventName varchar (50)  NULL ,

EventType varchar (50)  NULL ,

ConditionValues varchar (1000)  NULL ,

TransactionID numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_Actions (

ActionID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Actions PRIMARY KEY  CLUSTERED,

Time_stamp datetime NOT NULL ,

SiteName varchar (50)  NULL ,

EventName varchar (50)  NULL ,

ActionType varchar (50)  NULL ,

Parameters varchar (1000)  NULL ,

IsFailedAction bit NULL ,

ResultID numeric(18, 0) NOT NULL ,

EventID numeric(18, 0) NOT NULL REFERENCES tbl_EventRules(EventID),

TransactionID numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID)  ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_Authentications (

AuthenticationID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Authentications PRIMARY KEY  CLUSTERED,

Time_stamp datetime NOT NULL ,

RemoteIP varchar (15)  NOT NULL ,

RemotePort numeric(18, 0) NULL ,

LocalIP varchar (15)  NOT NULL ,

LocalPort numeric(18, 0) NULL ,

Protocol varchar (50)  NULL ,

SiteName varchar (50)  NULL ,

UserName varchar (50)  NULL ,

PasswordHash varchar (500)  NULL ,

SettingsLevels varchar (500)  NULL ,

ResultID numeric(18, 0) NOT NULL ,

TransactionID numeric(18, 0) NOT NULL References tbl_Transactions(TransactionID) ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_Groups (

GroupID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_Groups PRIMARY KEY  CLUSTERED,

GroupName varchar (50)  NULL ,

AuthenticationID numeric(18, 0) NOT NULL REFERENCES tbl_Authentications(AuthenticationID)  ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_CustomCommands (

CustomCommandID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_CustomCommands PRIMARY KEY CLUSTERED,

Time_stamp datetime NOT NULL ,

SiteName varchar (50)  NULL ,

Command varchar (50)  NULL ,

CommandParameters varchar (1000)  NULL ,

ExecutionTime numeric(18, 0) NULL ,

ResultID numeric(18, 0) NOT NULL ,

TransactionID numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_ProtocolCommands (

ProtocolCommandID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_ProtocolCommands PRIMARY KEY CLUSTERED,

Time_stamp datetime NOT NULL ,

RemoteIP varchar (15)  NULL ,

RemotePort numeric (18,0)  NULL ,

LocalIP varchar (15)  NULL ,

LocalPort numeric (18,0)  NULL ,

Protocol varchar (50)  NULL ,

SiteName varchar (50)  NULL ,

Command varchar (10)  NULL ,

CommandParameters varchar (1000)  NULL ,

FileName varchar (500)  NULL ,

VirtualFolderName varchar (500)  NULL ,

PhysicalFolderName varchar (500)  NULL ,

IsInternal numeric(18, 0) NULL ,

FileSize numeric(18, 0) NULL ,

TransferTime numeric(18, 0) NULL,

BytesTransferred numeric(18, 0) NULL ,

ResultID numeric(18, 0) NOT NULL ,

TransactionID numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_ResultCodes (

ResultID numeric(18, 0) NOT NULL CONSTRAINT PK_tbl_ResultCodes PRIMARY KEY CLUSTERED,

Description varchar (100)  NULL ,

Category varchar (10)  NULL

)

GO

CREATE TABLE dbo.tbl_SocketConnections (

SocketID numeric(18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_SocketConnections PRIMARY KEY CLUSTERED,

Time_stamp datetime NOT NULL ,

RemoteIP varchar (15)  NULL ,

RemotePort numeric (18,0)  NULL ,

LocalIP varchar (15)  NULL ,

LocalPort numeric(18, 0) NULL ,

SiteName varchar (50)  NULL ,

ResultID numeric(18, 0) NOT NULL ,

TransactionID numeric(18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE

)

GO

CREATE TABLE dbo.tbl_ClientOperations (

ClientOperationID numeric (18, 0) IDENTITY (1, 1) NOT NULL CONSTRAINT PK_tbl_ClientOperations PRIMARY KEY  CLUSTERED ,

Time_stamp datetime NOT NULL ,

Protocol varchar (50) NULL ,

RemoteAddress varchar (50) NULL ,

RemotePort numeric (18, 0) NULL ,

Username varchar (50) NULL ,

RemotePath varchar (500) NULL ,

LocalPath varchar (500) NULL ,

Operation varchar (50) NULL ,

BytesTransferred numeric (18, 0) NULL ,

TransferTime numeric (18, 0) NULL ,

ResultID numeric (18, 0) NOT NULL ,

TransactionID numeric (18, 0) NOT NULL REFERENCES tbl_Transactions(TransactionID) ON DELETE CASCADE

)

*END SQL QUERY