sql server create table script samples
*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