SGBD SQL Server

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKSEARCH' AND XTYPE='U')

BEGIN

CREATE TABLE [dbo].[TBTRACKSEARCH](

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

 [IDUSER] [int] NULL,

 [INSERTDATE] [datetime] NOT NULL,

 [FINISHDATE] [datetime] NULL,

 [DOCUMENTKEY1] [bigint] NOT NULL,

 [DOCUMENTKEY2] [bigint] NOT NULL,

 [DOCUMENTKEY3] [int] NOT NULL,

 [XMLREQUESTREPLY] [varchar](max) NULL,

 [SEARCHSTATUS] [tinyint] NOT NULL,

 [OPENSTATUS] [bit] NOT NULL,

 [COLDIDSEARCHED] [bigint] NULL,

 [DONTSHOW] [tinyint] NULL,

 [OBSERVATION] [varchar](max) NULL,

CONSTRAINT [PK_TBTRACKSEARCH] PRIMARY KEY CLUSTERED

  (

 [SEARCHID] ASC

  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

  ) ON [PRIMARY];

  SET ANSI_PADDING OFF;

END

GO

/****** UPDATE DA TABELA TBTRACKSEARCH PARA ADICIONAR COLUNA OBSERVATION ******/

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKSEARCH' AND XTYPE='U')

BEGIN

                 IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'OBSERVATION')

                 BEGIN

                         ALTER TABLE [dbo].[TBTRACKSEARCH] ADD [OBSERVATION] [varchar] NULL

         END

                 ALTER TABLE [dbo].[TBTRACKSEARCH] ALTER COLUMN [IDUSER] [int] NULL

END

/****** Object:  Table [dbo].[TBTRACKINSTANCE]    Script Date: 09/23/2013 15:16:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKINSTANCE' AND XTYPE='U')

 BEGIN

CREATE TABLE [dbo].[TBTRACKINSTANCE](

 [INSTANCEID] [smallint] IDENTITY(1,1) NOT NULL,

 [SERVERID] [smallint] NULL,

 [LASTUPDATE] [datetime] NULL,

CONSTRAINT [PK_TBTRACKINSTANCE] PRIMARY KEY CLUSTERED

    (

 [INSTANCEID] ASC

    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    ) ON [PRIMARY];

 END

/****** Object:  Table [dbo].[TBTRACKSEARCHWORK]    Script Date: 09/23/2013 15:16:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKSEARCHWORK' AND XTYPE='U')

BEGIN

CREATE TABLE [dbo].[TBTRACKSEARCHWORK](

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

 [SEARCHID] [bigint] NOT NULL,

 [SERVERID] [smallint] NULL,

 [INSTANCEID] [smallint] NULL,

CONSTRAINT [PK_TBTRACKSEARCHWORK] PRIMARY KEY CLUSTERED

(

 [IDSearchWork] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY];

END

/****** Object:  Table [dbo].[TBTRACKEVENTS]    Script Date: 09/23/2013 15:16:04 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = 'TBTRACKEVENTS')

   BEGIN

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'ISSUERIDENTITYNUMBER' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [IssuerIdentityNumber] [varchar](20) NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'ISSUERNAME' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [IssuerName] [varchar](150) NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'EMISSIONDATE' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [EmissionDate] [DATETIME] NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'TOTALVALUE' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [TotalValue] [numeric](15,2) NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'RECEIVERIDENTITYNUMBER' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [ReceiverIdentityNumber] [varchar](20) NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'SECTOR' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [Sector] [tinyint] NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'RECEIVERNAME' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [ReceiverName] [varchar](150) NULL

    IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WITH(NOLOCK) WHERE UPPER(COLUMN_NAME) = 'USERIDENTIFICATION' AND UPPER(TABLE_NAME) = UPPER('TBTRACKEVENTS'))

           ALTER TABLE [dbo].[TBTRACKEVENTS] ADD [userIdentification] [varchar](100) NULL

   END

ELSE

   BEGIN

CREATE TABLE [dbo].[TBTRACKEVENTS](

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

 [SearchID] [bigint] NOT NULL,

 [EventType] [tinyint] NOT NULL,

 [EventDate] [datetime] NOT NULL,

 [EventMessage] [varchar](300) NULL,

 [IssuerIdentityNumber] [varchar](20) NULL,

 [IssuerName] [varchar](150) NULL,

 [EmissionDate] [datetime] NULL,

 [TotalValue] [numeric](15, 2) NULL,

 [ReceiverIdentityNumber] [varchar](20) NULL,

 [Sector] [tinyint] NULL,

 [ReceiverName] [varchar](150) NULL,

 [userIdentification] [varchar](100) NULL,

CONSTRAINT [PK_TBTRACKEVENTS] PRIMARY KEY CLUSTERED

(

 [EventID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[TBTRACKEVENTS]  WITH CHECK ADD  CONSTRAINT [fk_SEARCHID_EVENTS] FOREIGN KEY([SearchID])

REFERENCES [dbo].[TBTRACKSEARCH] ([SEARCHID])

GO

ALTER TABLE [dbo].[TBTRACKEVENTS] CHECK CONSTRAINT [fk_SEARCHID_EVENTS]

GO

ALTER TABLE [dbo].[TBTRACKEVENTS]  WITH CHECK ADD  CONSTRAINT [FK3AAA90CEC7973878] FOREIGN KEY([SearchID])

REFERENCES [dbo].[TBTRACKSEARCH] ([SEARCHID])

GO

ALTER TABLE [dbo].[TBTRACKEVENTS] CHECK CONSTRAINT [FK3AAA90CEC7973878]

GO

ALTER TABLE [dbo].[TBTRACKEVENTS]  WITH CHECK ADD  CONSTRAINT [FK8A533740D6731C79] FOREIGN KEY([SearchID])

REFERENCES [dbo].[TBTRACKSEARCH] ([SEARCHID])

GO

ALTER TABLE [dbo].[TBTRACKEVENTS] CHECK CONSTRAINT [FK8A533740D6731C79]

END

/****** Object:  Table [dbo].[TBTRACKRECAP]    Script Date: 02/08/2016 08:30:04 ******/        

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TBTRACKRECAP')

 BEGIN

         CREATE TABLE [dbo].[TBTRACKRECAP](

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

                 [DOCUMENTKEY1] [bigint] NOT NULL,

                 [DOCUMENTKEY2] [bigint] NOT NULL,

                 [DOCUMENTKEY3] [int] NOT NULL,

                 [JOBKEY] [varchar](60) NOT NULL,

                 [STATUS] [smallint] NULL,

                 [KIND] [smallint] NULL,

                 [INSERTEDDATE] [datetime] NOT NULL,

                 [SEARCHID] [bigint] NOT NULL,

                 [SERVERID] [smallint] NULL,

          CONSTRAINT [PK_TBTRACKRECAP_ID] PRIMARY KEY CLUSTERED

         (

                 [RECAPID] ASC

         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

         ) ON [PRIMARY]

END

/****** Object:  Table [dbo].[TBDOCSEARCHPROCESS]    Script Date: 02/08/2016 08:30:04 ******/        

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBDOCSEARCHPROCESS]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[TBDOCSEARCHPROCESS](

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

 [DOCKEY1] [bigint] NOT NULL,

 [DOCKEY2] [bigint] NOT NULL,

 [DOCKEY3] [bigint] NOT NULL,

 [PROTOCOL] [varchar](max) NOT NULL,

CONSTRAINT [PK_TBDOCSEARCHPROCESS] PRIMARY KEY CLUSTERED

(

 [PROCESSID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[TBTRACKLIST]    Script Date: 02/08/2016 08:30:04 ******/        

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKLIST' AND XTYPE='U')

BEGIN

         CREATE TABLE [dbo].[TBTRACKLIST](

                 [ID] [int] IDENTITY(1,1) NOT NULL,

                 [DOCUMENTNUMBER] [int] NOT NULL,

                 [EMISSIONDATE] [datetime] NOT NULL,

                 [DOCUMENTKEY1] [bigint] NOT NULL,

                 [DOCUMENTKEY2] [bigint] NOT NULL,

                 [DOCUMENTKEY3] [int] NOT NULL,

                 [COMPANYEMITTER] [nvarchar](200) NOT NULL,

                 [TOTALVALUE] [decimal](18, 2) NULL,

                 [STATUS] [tinyint] NULL,

                 [USERID] [int] NULL,

          CONSTRAINT [PK_TBTRACKLIST] PRIMARY KEY NONCLUSTERED

         (

                 [ID] ASC

         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

         ) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[TBTRACKSECTOR]    Script Date: 02/08/2016 08:30:04 ******/

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TBTRACKSECTOR')

 BEGIN

         CREATE TABLE [dbo].[TBTRACKSECTOR](

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

                 [SECTOR] [int] NULL,

                 [SECTORNAME] [varchar](150) NOT NULL,

                 [CNPJ] [bigint] NOT NULL,

                 [OWNERID] [varchar](36) NOT NULL,

         PRIMARY KEY CLUSTERED

         (

                 [ID] ASC

         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

         ) ON [PRIMARY]

 END

GO

/****** Object:  Table [dbo].[TBTRACKENTERPRISE]    Script Date: 02/08/2016 08:30:04 ******/

IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'TBTRACKENTERPRISE')

 BEGIN

         CREATE TABLE [dbo].[TBTRACKENTERPRISE](

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

                 [IDENTITYNUMBER] [bigint] NOT NULL,

                 [OWNERID] [varchar](36) NOT NULL,

          CONSTRAINT [PK_TBTRACKENTERPRISE] PRIMARY KEY CLUSTERED

         (

                 [ID] ASC

         )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

         ) ON [PRIMARY]

 END

GO

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME 'TBTRACKCOLDCONTROL' AND XTYPE='U')

BEGIN

                      CREATE TABLE [dbo].[TBTRACKCOLDCONTROL](

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

                                   [COLDID] [bigint] NULL,

                                   [LAST_ID] [bigint] NULL

                      ) ON [PRIMARY]

END

IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='TBTRACKCOLDCONTROL' AND XTYPE='U')

 BEGIN

         CREATE TABLE [dbo].[TBTRACKCOLDCONTROL](

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

                 [COLDID] [bigint] NULL,

                 [LAST_ID] [bigint] NULL

         ) ON [PRIMARY]

END

Atualização

CREATE TABLE [dbo].[TBTRACKCUSTOMFIELDS](

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

 [EventId] [bigint] NOT NULL,

 [Type] [int] NOT NULL,

 [Value] [varchar](max) NULL,

CONSTRAINT [PK_TBTRACKCUSTOMFIELDS] PRIMARY KEY CLUSTERED

(

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

GO

ALTER TABLE [dbo].[TBTRACKCUSTOMFIELDS]  WITH CHECK ADD  CONSTRAINT [FK_TBTRACKCUSTOMFIELDS_TBTRACKEVENTS] FOREIGN KEY([EventId])

REFERENCES [dbo].[TBTRACKEVENTS] ([EventID])

GO

ALTER TABLE [dbo].[TBTRACKCUSTOMFIELDS] CHECK CONSTRAINT [FK_TBTRACKCUSTOMFIELDS_TBTRACKEVENTS]

GO