· 10 years ago · Sep 27, 2016, 03:58 PM
1USE [master]
2GO
3/****** Object: Database [TrackingSupport] Script Date: 9/27/2016 12:09:29 PM ******/
4CREATE DATABASE [TrackingSupport]
5 CONTAINMENT = NONE
6 ON PRIMARY
7( NAME = N'TrackingSupport', FILENAME = N'C:\DB\TrackingSupport.mdf' , SIZE = 95040KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
8 LOG ON
9( NAME = N'TrackingSupport_log', FILENAME = N'C:\DB\TrackingSupport_log.ldf' , SIZE = 13888KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
10GO
11ALTER DATABASE [TrackingSupport] SET COMPATIBILITY_LEVEL = 110
12GO
13IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
14begin
15EXEC [TrackingSupport].[dbo].[sp_fulltext_database] @action = 'enable'
16end
17GO
18ALTER DATABASE [TrackingSupport] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [TrackingSupport] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [TrackingSupport] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [TrackingSupport] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [TrackingSupport] SET ARITHABORT OFF
27GO
28ALTER DATABASE [TrackingSupport] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [TrackingSupport] SET AUTO_CREATE_STATISTICS ON
31GO
32ALTER DATABASE [TrackingSupport] SET AUTO_SHRINK OFF
33GO
34ALTER DATABASE [TrackingSupport] SET AUTO_UPDATE_STATISTICS ON
35GO
36ALTER DATABASE [TrackingSupport] SET CURSOR_CLOSE_ON_COMMIT OFF
37GO
38ALTER DATABASE [TrackingSupport] SET CURSOR_DEFAULT GLOBAL
39GO
40ALTER DATABASE [TrackingSupport] SET CONCAT_NULL_YIELDS_NULL OFF
41GO
42ALTER DATABASE [TrackingSupport] SET NUMERIC_ROUNDABORT OFF
43GO
44ALTER DATABASE [TrackingSupport] SET QUOTED_IDENTIFIER OFF
45GO
46ALTER DATABASE [TrackingSupport] SET RECURSIVE_TRIGGERS OFF
47GO
48ALTER DATABASE [TrackingSupport] SET DISABLE_BROKER
49GO
50ALTER DATABASE [TrackingSupport] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
51GO
52ALTER DATABASE [TrackingSupport] SET DATE_CORRELATION_OPTIMIZATION OFF
53GO
54ALTER DATABASE [TrackingSupport] SET TRUSTWORTHY OFF
55GO
56ALTER DATABASE [TrackingSupport] SET ALLOW_SNAPSHOT_ISOLATION OFF
57GO
58ALTER DATABASE [TrackingSupport] SET PARAMETERIZATION SIMPLE
59GO
60ALTER DATABASE [TrackingSupport] SET READ_COMMITTED_SNAPSHOT OFF
61GO
62ALTER DATABASE [TrackingSupport] SET HONOR_BROKER_PRIORITY OFF
63GO
64ALTER DATABASE [TrackingSupport] SET RECOVERY SIMPLE
65GO
66ALTER DATABASE [TrackingSupport] SET MULTI_USER
67GO
68ALTER DATABASE [TrackingSupport] SET PAGE_VERIFY CHECKSUM
69GO
70ALTER DATABASE [TrackingSupport] SET DB_CHAINING OFF
71GO
72ALTER DATABASE [TrackingSupport] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
73GO
74ALTER DATABASE [TrackingSupport] SET TARGET_RECOVERY_TIME = 0 SECONDS
75GO
76EXEC sys.sp_db_vardecimal_storage_format N'TrackingSupport', N'ON'
77GO
78USE [TrackingSupport]
79GO
80/****** Object: StoredProcedure [dbo].[ClearOld] Script Date: 9/27/2016 12:09:29 PM ******/
81SET ANSI_NULLS ON
82GO
83SET QUOTED_IDENTIFIER ON
84GO
85-- =============================================
86-- Author: <Author,,Name>
87-- Create date: <Create Date,,>
88-- Description: <Description,,>
89-- =============================================
90CREATE PROCEDURE [dbo].[ClearOld]
91
92AS
93BEGIN
94 -- SET NOCOUNT ON added to prevent extra result sets from
95 -- interfering with SELECT statements.
96 SET NOCOUNT ON;
97
98BEGIN TRAN
99BEGIN TRY
100--удалÑем аргументы и аутпут древнее, чем поÑледние 2 недели, кроме логинов
101delete from dbo.CallArguments where callid in
102(
103SELECT mc.ID FROM dbo.MethodCalls as mc where mc.RecordCreated < DateAdd(day,-14,getdate()) AND mc.MethodID in (SELECT ID FROM dbo.Methods where islogin=0)
104)
105END TRY
106BEGIN CATCH
107SELECT 'DELETE CallArguments failed'
108END CATCH
109COMMIT
110
111
112BEGIN TRAN
113BEGIN TRY
114delete from dbo.[OutPut] where callid in
115(
116SELECT mc.ID FROM dbo.MethodCalls as mc where mc.RecordCreated < DateAdd(day,-14,getdate()) AND mc.MethodID in (SELECT ID FROM dbo.Methods where islogin=0)
117)
118END TRY
119BEGIN CATCH
120SELECT 'DELETE [OutPut] failed'
121END CATCH
122COMMIT
123
124BEGIN TRAN
125BEGIN TRY
126--удалÑем вызовы древнее, чем 2 меÑÑца, кроме логинов
127delete FROM dbo.MethodCalls where RecordCreated < DateAdd(month,-2,getdate()) AND MethodID in (SELECT ID FROM dbo.Methods where islogin=0)
128END TRY
129BEGIN CATCH
130SELECT 'DELETE [MethodCalls] failed'
131END CATCH
132COMMIT
133
134BEGIN TRAN
135BEGIN TRY
136--удалÑем визиты от ÑеÑÑий более поздние, чем 2 недели
137delete from dbo.Visits where SessionID in (SELECT ID FROM dbo.Sessions where RecordCreated < DateAdd(day,-14,getdate()) )
138END TRY
139BEGIN CATCH
140SELECT 'DELETE [Visits] failed'
141END CATCH
142COMMIT
143
144BEGIN TRAN
145BEGIN TRY
146DELETE FROM dbo.CallToUser WHERE CallID not in (SELECT ID FROM dbo.MethodCalls)
147END TRY
148BEGIN CATCH
149SELECT 'DELETE [CallToUser] failed'
150END CATCH
151COMMIT
152
153END
154
155
156
157
158
159GO
160/****** Object: StoredProcedure [dbo].[sp_Check_IP] Script Date: 9/27/2016 12:09:29 PM ******/
161SET ANSI_NULLS ON
162GO
163SET QUOTED_IDENTIFIER ON
164GO
165-- =============================================
166-- Author: <Author,,Name>
167-- Create date: <Create Date,,>
168-- Description: <Description,,>
169-- =============================================
170CREATE PROCEDURE [dbo].[sp_Check_IP]
171 -- Add the parameters for the stored procedure here
172 @IP nvarchar (15)
173AS
174BEGIN
175 -- SET NOCOUNT ON added to prevent extra result sets from
176 -- interfering with SELECT statements.
177 SET NOCOUNT ON;
178
179 -- Insert statements for procedure here
180 SELECT bip.id from dbo.BlockedIP as bip where bip.IP = @IP AND bip.BlockedTill > GETDATE()
181END
182
183
184
185
186
187GO
188/****** Object: StoredProcedure [dbo].[sp_ExpandMethodErrors] Script Date: 9/27/2016 12:09:29 PM ******/
189SET ANSI_NULLS ON
190GO
191SET QUOTED_IDENTIFIER ON
192GO
193-- =============================================
194-- Author: <Author,,Name>
195-- Create date: <Create Date,,>
196-- Description: <Description,,>
197-- =============================================
198CREATE PROCEDURE [dbo].[sp_ExpandMethodErrors]
199 -- Add the parameters for the stored procedure here
200 @Date as Date,
201 @MethodID as int
202AS
203BEGIN
204 -- SET NOCOUNT ON added to prevent extra result sets from
205 -- interfering with SELECT statements.
206 SET NOCOUNT ON;
207
208select mc.SystemUserID, COUNT(mc.Exception) as [Count], mc.ExceptionMessage, mc.Exception,
209(SELECT TOP 1 mc2.id FROM dbo.MethodCalls as mc2
210WHERE mc.Exception = mc2.Exception AND mc.ExceptionMessage = mc2.ExceptionMessage AND @Date = mc2.CallDate AND mc2.MethodID = @MethodID
211) as FirstCallID from dbo.MethodCalls as mc
212--left join dbo.CallToUser as cu
213--on mc.ID = cu.CallID
214where
215mc.MethodID = @MethodID
216AND mc.ExceptionMessage is not null
217AND mc.CallDate = @Date
218group by mc.SystemUserID, mc.Exception , mc.ExceptionMessage
219
220END
221
222
223
224
225
226GO
227/****** Object: StoredProcedure [dbo].[sp_ExpandMethodErrors_API] Script Date: 9/27/2016 12:09:29 PM ******/
228SET ANSI_NULLS ON
229GO
230SET QUOTED_IDENTIFIER ON
231GO
232
233-- =============================================
234-- Author: <Author,,Name>
235-- Create date: <Create Date,,>
236-- Description: <Description,,>
237-- =============================================
238CREATE PROCEDURE [dbo].[sp_ExpandMethodErrors_API]
239 -- Add the parameters for the stored procedure here
240 @Date as Date,
241 @MethodID as int
242AS
243BEGIN
244 -- SET NOCOUNT ON added to prevent extra result sets from
245 -- interfering with SELECT statements.
246 SET NOCOUNT ON;
247
248select CAST (ca.ArgumentValue as nvarchar(200)) , COUNT(mc.Exception) as [Count], mc.ExceptionMessage, mc.Exception,
249(SELECT TOP 1 mc2.id FROM dbo.MethodCalls as mc2
250WHERE mc.Exception = mc2.Exception AND mc.ExceptionMessage = mc2.ExceptionMessage AND @Date = mc2.CallDate AND mc2.MethodID = @MethodID
251) as FirstCallID from dbo.MethodCalls as mc
252
253left join dbo.CallArguments as ca
254on ca.CallID = mc.ID
255where
256mc.MethodID = @MethodID
257AND mc.ExceptionMessage is not null
258AND mc.CallDate = @Date
259AND CAST (ca.ArgumentName as nvarchar(200)) = 'WalletID'
260group by mc.Exception , mc.ExceptionMessage, CAST (ca.ArgumentValue as nvarchar(200))
261
262
263END
264
265
266
267
268
269
270GO
271/****** Object: StoredProcedure [dbo].[sp_GroupErrors] Script Date: 9/27/2016 12:09:29 PM ******/
272SET ANSI_NULLS ON
273GO
274SET QUOTED_IDENTIFIER ON
275GO
276-- =============================================
277-- Author: <Author,,Name>
278-- Create date: <Create Date,,>
279-- Description: <Description,,>
280-- =============================================
281CREATE PROCEDURE [dbo].[sp_GroupErrors]
282 -- Add the parameters for the stored procedure here
283 @DaysCount int
284AS
285BEGIN
286 -- SET NOCOUNT ON added to prevent extra result sets from
287 -- interfering with SELECT statements.
288 SET NOCOUNT ON;
289declare @cutDate as Date;
290set @cutDate = Cast (DATEADD(DAY,-@DaysCount, GETDATE()) as Date);
291 -- Insert statements for procedure here
292 select /*CAST (mc.RecordCreated as Date)*/ mc.CallDate, mc.ExceptionMessage, (SELECT TOP 1 MethodName FROM Methods WHERE ID = mc.MethodID) as MethodName , COUNT(mc.ExceptionMessage) as ErrorsCount from dbo.MethodCalls as mc
293
294where
295--CAST (mc.RecordCreated as Date) >= DATEADD(DAY,-@DaysCount, GETDATE())
296mc.CallDate >= @cutDate
297AND
298mc.ExceptionMessage is not null
299
300group by mc.ExceptionMessage, mc.MethodID, mc.CallDate --CAST (mc.RecordCreated as Date)
301order by mc.CallDate /*CAST (mc.RecordCreated as Date)*/, COUNT(mc.ExceptionMessage) desc
302END
303
304
305
306
307
308GO
309/****** Object: StoredProcedure [dbo].[sp_IpToCaptha] Script Date: 9/27/2016 12:09:29 PM ******/
310SET ANSI_NULLS ON
311GO
312SET QUOTED_IDENTIFIER ON
313GO
314-- =============================================
315-- Author: <Author,,Name>
316-- Create date: <Create Date,,>
317-- Description: <Description,,>
318-- =============================================
319Create PROCEDURE [dbo].[sp_IpToCaptha]
320AS
321BEGIN
322 -- SET NOCOUNT ON added to prevent extra result sets from
323 -- interfering with SELECT statements.
324 SET NOCOUNT ON;
325
326SELECT ClientIP --, COUNT (ClientIP)
327 FROM [TrackingSupport].[dbo].[MethodCalls] as mc
328
329 where mc.MethodID in (357,358)
330 and mc.RecordCreated > DATEADD(HOUR, -2, GETDATE())
331 AND mc.ExceptionMessage = 'User_Not_Found'
332
333 group by mc.ClientIP
334 HAVING COUNT (mc.ClientIP) > 2
335
336END
337
338
339
340
341
342GO
343/****** Object: StoredProcedure [dbo].[SP_MOVE_TABLES] Script Date: 9/27/2016 12:09:29 PM ******/
344SET ANSI_NULLS ON
345GO
346SET QUOTED_IDENTIFIER ON
347GO
348CREATE PROCEDURE [dbo].[SP_MOVE_TABLES]
349@SourceFileGroupID int,
350@TargetFileGroupID int,
351@TableToMove NVARCHAR(128),
352@MovePKAndAllUniqueConstraints bit,
353@MoveAllNonClusteredIndexes bit
354
355AS
356
357SET NOCOUNT ON
358DECLARE @ScriptMsg NVARCHAR(512)
359DECLARE @DatabaseName sysname
360DECLARE @ServerName sysname
361DECLARE @TableHasCI BIT
362DECLARE @TableHasIdent BIT
363DECLARE @TableHasPK BIT
364DECLARE @TableHasUQ BIT
365DECLARE @File1Name NVARCHAR(128)
366DECLARE @File2Name NVARCHAR(128)
367DECLARE @IdentColName NVARCHAR(128)
368DECLARE @ColList NVARCHAR(1024)
369DECLARE @indid NVARCHAR(128)
370DECLARE @Type CHAR(2)
371DECLARE @KeyName NVARCHAR(128)
372DECLARE @AssocFKeyName NVARCHAR(128)
373DECLARE @FKTableName NVARCHAR(128)
374DECLARE @CIName NVARCHAR(128)
375DECLARE @IsPadIndex BIT
376DECLARE @i INT
377DECLARE @J INT
378DECLARE @SQLStr NVARCHAR(4000)
379
380-- This temp table holds the column names of keys/constraints, and such.
381IF OBJECT_ID('tempdb..#tblColTable', 'U') IS NOT NULL
382 DROP TABLE #tblColTable
383
384CREATE TABLE #tblColTable (
385 Idx INT IDENTITY(1, 1),
386 ColName NVARCHAR(128),
387 IdxOrder CHAR(4))
388
389-- This temp table is used to store the key/constraint properties
390-- of the moved table.
391IF OBJECT_ID('tempdb..#tblKeysTable', 'U') IS NOT NULL
392 DROP TABLE #tblKeysTable
393
394CREATE TABLE #tblKeysTable (
395 Idx INT IDENTITY(1, 1),
396 KeyName NVARCHAR(128),
397 indid INT,
398 Type CHAR(2))
399
400-- This temp table holds the foreign keys of the table.
401-- The SQLStmt column is used to build dynamic SQL statements
402-- that are related to these foreign keys.
403IF OBJECT_ID('tempdb..#tblFKTable', 'U') IS NOT NULL
404 DROP TABLE #tblFKTable
405
406CREATE TABLE #tblFKTable (
407 Idx INT IDENTITY(1, 1),
408 ForeignTableName NVARCHAR(128),
409 KeyName NVARCHAR(128),
410 SQLStmt NVARCHAR(1024))
411
412-- This temp table holds the colunms of the foriegn key of the table.
413IF OBJECT_ID('tempdb..#tblFKColTable', 'U') IS NOT NULL
414 DROP TABLE #tblFKColTable
415
416CREATE TABLE #tblFKColTable (
417 Idx INT IDENTITY(1, 1),
418 ColName NVARCHAR(128),
419 FOrP CHAR(1))
420
421
422-- Get server and database names
423SET @ServerName = CAST(ISNULL(SERVERPROPERTY('ServerName'), 'Unknown') AS sysname)
424SET @DatabaseName = db_name()
425
426-- Some basic verifications:
427-- 1. Check that file groups exist, and that the table exists.
428
429SET @File1Name = FILEGROUP_NAME(@SourceFileGroupID)
430
431IF @File1Name IS NULL
432BEGIN
433 IF @SourceFileGroupID IS NULL
434 SET @SourceFileGroupID = 'NULL'
435
436 SET @ScriptMsg = N'The source file group ' + CAST(@SourceFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
437 RAISERROR(@ScriptMsg, 16, 1)
438 RETURN
439END
440
441
442SET @File2Name = FILEGROUP_NAME(@TargetFileGroupID)
443
444IF @File2Name IS NULL
445BEGIN
446 IF @TargetFileGroupID IS NULL
447 SET @TargetFileGroupID = 'NULL'
448
449 SET @ScriptMsg = N'The target file group ' + CAST(@TargetFileGroupID AS VARCHAR(64)) + N' does not exist on the database ' + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid filegroup id.'
450 RAISERROR(@ScriptMsg, 16, 1)
451 RETURN
452END
453
454IF @SourceFileGroupID = @TargetFileGroupID
455BEGIN
456 SET @ScriptMsg = N'The file groups provided are the same. This is not allow in this script.'
457 RAISERROR(@ScriptMsg, 16, 1)
458 RETURN
459END
460
461SET @TableToMove = LTRIM(RTRIM(@TableToMove))
462
463IF RIGHT(@TableToMove, 1) = ']'
464 SET @TableToMove = LEFT(@TableToMove, LEN(@TableToMove) - 1)
465IF LEFT(@TableToMove, 1) = '['
466 SET @TableToMove = RIGHT(@TableToMove, LEN(@TableToMove) - 1)
467
468
469-- Validate the table name and check that it exists in the system catalog.
470IF @TableToMove IS NULL OR @TableToMove = ''
471BEGIN
472 SET @ScriptMsg = N'The table name provided in the script is either null or empty, on server '
473 + @ServerName + N' and database ' + @DatabaseName
474 + N'. Please provide a valid table name.'
475 RAISERROR(@ScriptMsg, 16, 1)
476 RETURN
477END
478
479IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
480 WHERE TABLE_NAME = @TableToMove AND TABLE_TYPE = 'BASE TABLE')
481BEGIN
482 SET @ScriptMsg = N'The table name provided in the script is not found in database '
483 + @DatabaseName + N', on server ' + @ServerName + N'. Please provide a valid table name.'
484 RAISERROR(@ScriptMsg, 16, 1)
485 RETURN
486END
487
488-- Check that the table is indeed defined on the source file group.
489IF (SELECT TOP 1 groupid FROM sysindexes WHERE id = OBJECT_ID(@TableToMove) and indid IN (0, 1)) <> @SourceFileGroupID
490BEGIN
491 SET @ScriptMsg = N'The table ' + @TableToMove + ' is not found on filegroup ' + CAST(@SourceFileGroupID AS VARCHAR(32))
492 + N'. Please provide a valid table name and source file group.'
493 RAISERROR(@ScriptMsg, 16, 1)
494 RETURN
495END
496
497-- 2. Check that the target file group is not read-only.
498
499IF FILEGROUPPROPERTY(FILEGROUP_NAME(@TargetFileGroupID), 'IsReadOnly') = 1
500BEGIN
501 SET @ScriptMsg = N'The taget file group (i.e., with file group id = ' + CAST(@TargetFileGroupID AS VARCHAR(32)) + N') is read-only. Aborting table move.'
502 RAISERROR(@ScriptMsg, 16, 1)
503 RETURN
504END
505
506
507-- 3. If we have gotten this far, then it is ok to move the table to the
508-- requested filegroup.
509
510-- First thing first: Check whether the table has a clustered index.
511SET @TableHasCI = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasClustIndex')
512
513-- If not - check whether the table has an identity column.
514-- If it does - apply the CI with the new filegroup on the identity column.
515-- Once done - remove the CI. If it does not - check whether the table has a primary
516-- key and apply the CI there on the new file group, and then drop the CI.
517-- If the table does not have an identity column, or a primary key,
518-- then a new identity column is created for the table and the CI
519-- is applied on it, and then the CI and the identity column are removed.
520-- This whole shabang is done in order to make the CI creation as fast as possible.
521-- The case where the table does not have a clustered index to begin with implies
522-- bad table design, and should not be common anyhow.
523
524IF @TableHasCI = 0
525BEGIN
526 SET @TableHasIdent = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasIdentity')
527
528 IF @TableHasIdent = 0
529 BEGIN
530 SET @TableHasPK = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasPrimaryKey')
531 SET @TableHasUQ = OBJECTPROPERTY(OBJECT_ID(@TableToMove), 'TableHasUniqueCnst')
532
533 -- Only if the table has no PK/UQ or clustered index, then create an identity
534 -- column on it. This new column will hold the CI.
535 IF @TableHasPK = 0 AND @TableHasUQ = 0
536 BEGIN
537 EXEC(N' ALTER TABLE [' + @TableToMove + N'] ADD
538 [This_Is_My_Ident_Col_Name] BIGINT IDENTITY (1, 1) ')
539
540 SET @IdentColName = 'This_Is_My_Ident_Col_Name'
541
542 -- Apply the CI on the identity column. We don't create the CI
543 -- as unique, since the identity column may be non-unique,
544 -- due to reseeding.
545
546 EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
547 ON [' + @TableToMove + N']([' + @IdentColName + '])
548 ON [' + @File2Name + N']')
549
550 -- The table is now moved -> Remove the CI.
551
552 EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
553
554 -- Finally, remove the added identity column
555
556 EXEC(N' ALTER TABLE [' + @TableToMove + N']
557 DROP COLUMN [This_Is_My_Ident_Col_Name] ')
558 END
559 ELSE
560 BEGIN
561 -- In this case, the table has a PK/UQ, so we might as well
562 -- apply the CI on the column(s) of the PK/UQ.
563 -- First, get the column(s) of the PK/UQ.
564
565 SELECT @KeyName = CONSTRAINT_NAME
566 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
567 WHERE TABLE_NAME = @TableToMove
568 AND CONSTRAINT_TYPE = 'PRIMARY KEY'
569
570 IF @@ROWCOUNT = 0
571 SELECT TOP 1 @KeyName = CONSTRAINT_NAME
572 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WITH (NOLOCK)
573 WHERE TABLE_NAME = @TableToMove
574 AND CONSTRAINT_TYPE = 'UNIQUE'
575
576 -- The varialbe @KeyName now holds the name of the PK/UQ
577 INSERT INTO #tblColTable (ColName, IdxOrder)
578 SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
579 -- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
580 CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
581 INDEXPROPERTY(OBJECT_ID(@TableToMove),
582 @KeyName,
583 'IndexID'),
584 keyno,
585 'IsDescending') = 1
586 THEN 'DESC'
587 ELSE 'ASC'
588 END
589 FROM sysindexkeys
590 WHERE id = OBJECT_ID(@TableToMove)
591 AND indid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IndexID')
592
593 IF @@ROWCOUNT > 0
594 SET @i = 1
595
596 SET @ColList = N''
597
598 WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
599 BEGIN
600 SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
601 FROM #tblColTable
602 WHERE Idx = @i
603
604 SET @i = @i + 1
605 END
606
607 SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
608
609 -- Now, apply the CI on the primary key columns. The CI is not
610 -- created as a unique CI, since if the PK/UQ was added with the NOCHECK
611 -- option, there could be duplicate entries in the PK/UQ.
612
613 EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
614 ON [' + @TableToMove + N'](' + @ColList + ')
615 ON [' + @File2Name + N']')
616
617 -- The last command moved the CI (and thus the table), so we
618 -- can now drop the CI.
619
620 EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
621 END
622 END
623 ELSE
624 BEGIN
625 -- Here, the table originally had an identity. We apply the CI
626 -- on the identity column, and then remove it.
627 SELECT @IdentColName = COLUMN_NAME
628 FROM INFORMATION_SCHEMA.COLUMNS WITH (NOLOCK)
629 WHERE TABLE_NAME = @TableToMove
630 AND COLUMNPROPERTY(OBJECT_ID(@TableToMove), COLUMN_NAME, 'IsIdentity') = 1
631
632 EXEC(N'CREATE CLUSTERED INDEX [This_Is_My_Clsuetered_Index_Name]
633 ON [' + @TableToMove + N']([' + @IdentColName + '])
634 ON [' + @File2Name + N']')
635
636 -- The table is now moved -> Remove the CI.
637
638 EXEC(N'DROP INDEX [' + @TableToMove + N'].[This_Is_My_Clsuetered_Index_Name]')
639 END
640END
641ELSE
642BEGIN
643 -- Now, for the big ELSE. The ELSE applies to the case where the
644 -- table already has a clustered index. Here, we select the name of the
645 -- existing clustered index, then drop it from the table, and recreate
646 -- it on the other filegroup (on the same columns and order as was
647 -- originally defined for the table).
648 -- If the CI is also a PK/UQ/unique index, then we first check all foreign
649 -- keys for the PK/UQ/UI, drop them if they exist, drop the PK/UQ/UI
650 -- then recreate the PK/UQ/UI as CLUSTERED, and then reapply all the
651 -- foreign keys constraints. If the CI is non-unique (thus is not
652 -- associated with a PK/UQ/UI), we just drop and recreate it on the
653 -- target file group.
654
655 SELECT @CIName = [name]
656 FROM sysindexes WITH (NOLOCK)
657 WHERE id = OBJECT_ID(@TableToMove)
658 AND indid = 1
659
660 DELETE FROM #tblColTable
661
662 INSERT INTO #tblColTable (ColName, IdxOrder)
663 SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
664 -- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
665 CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
666 INDEXPROPERTY(OBJECT_ID(@TableToMove),
667 @CIName,
668 'IndexID'),
669 keyno,
670 'IsDescending') = 1
671 THEN 'DESC'
672 ELSE 'ASC'
673 END
674 FROM sysindexkeys WITH (NOLOCK)
675 WHERE id = OBJECT_ID(@TableToMove)
676 AND indid = 1
677 ORDER BY keyno ASC
678
679 SELECT @i = MIN(Idx)
680 FROM #tblColTable
681
682 SET @ColList = N''
683
684 WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
685 BEGIN
686 SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
687 FROM #tblColTable
688 WHERE Idx = @i
689
690 SET @i = @i + 1
691 END
692
693 SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
694
695 -- Check whether the clustered index is also the PK, or a unique constraint (UQ),
696 -- or a unique index (UI) that is neither a PK or a UQ.
697 -- If the CI is either one of the above, we first check whether any foreign keys
698 -- reference this PK/UQ/UI. If so - we drop the FKs, then drop the PK/UQ/UI,
699 -- then recreate the PK/UQ/UI on the target filegroup, and then recreate all
700 -- the foreign keys dropped earlier.
701 -- If the CI is other than the above (i.e., it is a non-unique clustered index)
702 -- then we simply drop it and recreate it on the target filegroup.
703
704 IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsPrimaryKey') = 1
705 OR OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsUniqueCnst') = 1
706 OR INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsUnique') = 1
707 BEGIN
708 -- This case stands for a CI which is a PK/UQ/UI.
709 -- First, we drop all foreign keys associated with the PK/UQ/UI.
710 -- These FK constraints will be reapplied on the PK later,
711 -- (i.e., after the PK/UQ/UI is recreated on the target filegroup).
712
713 DELETE FROM #tblFKTable
714
715 -- Get all the FK constraints associated with the PK/UQ/UI.
716 -- Here, we query sysreferences so we could get our hands on all the
717 -- foreign keys that reference the PK/UQ/UI of the table
718 -- that needs to be moved.
719 INSERT INTO #tblFKTable (ForeignTableName, KeyName)
720 SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(constid)
721 FROM sysreferences WITH (NOLOCK)
722 WHERE rkeyid = OBJECT_ID(@TableToMove)
723 AND rkeyindid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IndexID')
724
725 SELECT @AssocFKeyName = MIN(KeyName)
726 FROM #tblFKTable
727
728 WHILE @AssocFKeyName IS NOT NULL
729 BEGIN
730 -- Get the list of primary and then foreign columns
731 -- for the collected FK constraints. The CASCADE UPDATE,
732 -- CASCADE DELETE, and NOT FOR REPLICATION properties
733 -- of the FK are considered later.
734
735 SELECT @FKTableName = ForeignTableName
736 FROM #tblFKTable
737 WHERE KeyName = @AssocFKeyName
738
739 DELETE FROM #tblFKColTable
740
741 -- First, the tables of the foreign table. The select is ordered by keyno
742 -- so the order of columns in the FK will remain unchanged by the
743 -- drop/recreate operation.
744 INSERT INTO #tblFKColTable (ColName, FOrP)
745 SELECT COL_NAME(fkeyid, fkey), 'F'
746 FROM sysforeignkeys
747 WHERE constid = OBJECT_ID(@AssocFKeyName)
748 ORDER BY keyno
749
750 -- Similarly, for the primary table.
751 INSERT INTO #tblFKColTable (ColName, FOrP)
752 SELECT COL_NAME(rkeyid, rkey), 'P'
753 FROM sysforeignkeys
754 WHERE constid = OBJECT_ID(@AssocFKeyName)
755 ORDER BY keyno
756
757 -- We now build the FK creation statement
758 SELECT @j = MIN(Idx)
759 FROM #tblFKColTable
760
761 SET @SQLStr = N'ALTER TABLE [' + @FKTableName + N'] '
762 + N' WITH NOCHECK ADD CONSTRAINT [' + @AssocFKeyName + N'] '
763 + N' FOREIGN KEY ('
764
765 -- Foreign table columns
766 WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'F')
767 BEGIN
768 SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
769 FROM #tblFKColTable
770 WHERE Idx = @j
771
772 SET @j = @j + 1
773 END
774
775 SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + N') REFERENCES [' + @TableToMove + N']('
776
777 -- Primary table columns
778 WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'P')
779 BEGIN
780 SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
781 FROM #tblFKColTable
782 WHERE Idx = @j
783
784 SET @j = @j + 1
785 END
786
787 SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + ') '
788
789 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsDeleteCascade') = 1
790 SET @SQLStr = @SQLStr + N' ON DELETE CASCADE '
791
792 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsUpdateCascade') = 1
793 SET @SQLStr = @SQLStr + N' ON UPDATE CASCADE '
794
795 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsNotRepl') = 1
796 SET @SQLStr = @SQLStr + N' NOT FOR REPLICATION '
797
798
799 -- Now, store this SQL statement, and drop the FK constraint
800 UPDATE #tblFKTable
801 SET SQLStmt = @SQLStr
802 WHERE KeyName = @AssocFKeyName
803
804 -- Drop the constraint
805
806 EXEC(N'ALTER TABLE [' + @FKTableName + '] DROP CONSTRAINT [' + @AssocFKeyName + N']')
807
808 SELECT @AssocFKeyName = MIN(KeyName)
809 FROM #tblFKTable
810 WHERE @AssocFKeyName < KeyName
811 END
812
813 -- The CREATE statement is different for PK or UQ
814 IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsPrimaryKey') = 1
815 BEGIN
816 EXEC(N' ALTER TABLE [' + @TableToMove + N']
817 DROP CONSTRAINT [' + @CIName + N'] ')
818
819 EXEC(N' ALTER TABLE [' + @TableToMove + N']
820 WITH NOCHECK ADD CONSTRAINT [' + @CIName + N']
821 PRIMARY KEY CLUSTERED (' + @ColList + N')
822 ON [' + @File2Name + N']')
823 END
824 ELSE
825 BEGIN
826 IF OBJECTPROPERTY(OBJECT_ID(@CIName), 'IsUniqueCnst') = 1
827 BEGIN
828 EXEC(N' ALTER TABLE [' + @TableToMove + N']
829 DROP CONSTRAINT [' + @CIName + N'] ')
830
831 EXEC(N' ALTER TABLE [' + @TableToMove + N']
832 WITH NOCHECK ADD CONSTRAINT [' + @CIName + N']
833 UNIQUE CLUSTERED (' + @ColList + N')
834 ON [' + @File2Name + N']')
835 END
836 ELSE -- the CI here is a unique index
837 BEGIN
838 SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsPadIndex')
839
840 EXEC(N'DROP INDEX [' + @TableToMove + N'].[' + @CIName + N']')
841
842 -- Recreate the index on the same columns and column order, as
843 -- they were defined on the original table, and in this
844 -- case, the CI is kept unique.
845
846 IF @IsPadIndex = 1
847
848 EXEC(N'CREATE UNIQUE CLUSTERED INDEX [' + @CIName + N']
849 ON [' + @TableToMove + N'](' + @ColList + N')
850 WITH PAD_INDEX
851 ON [' + @File2Name + N']')
852
853 ELSE
854 EXEC(N'CREATE UNIQUE CLUSTERED INDEX [' + @CIName + N']
855 ON [' + @TableToMove + N'](' + @ColList + N')
856 ON [' + @File2Name + N']')
857 END
858 END
859
860 -- Recreate the FK constraint of the table.
861 SELECT @AssocFKeyName = MIN(KeyName)
862 FROM #tblFKTable
863
864 WHILE @AssocFKeyName IS NOT NULL
865 BEGIN
866 SELECT @SQLStr = SQLStmt
867 FROM #tblFKTable
868 WHERE KeyName = @AssocFKeyName
869
870 EXEC(@SQLStr)
871
872 SELECT @AssocFKeyName = MIN(KeyName)
873 FROM #tblFKTable
874 WHERE @AssocFKeyName < KeyName
875 END
876 END
877 ELSE
878 BEGIN
879 -- Here, the CI is not a PK/UQ/UI, so we drop the CI from
880 -- the current filegroup, and recreate it on the
881 -- target filegroup, as a non-unique index.
882
883 SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @CIName, 'IsPadIndex')
884
885 EXEC(N'DROP INDEX [' + @TableToMove + N'].[' + @CIName + N']')
886
887 -- Recreate the index on the same columns and column order, as
888 -- they were defined on the original table, and in this
889 -- case, the CI is not unique.
890
891 IF @IsPadIndex = 1
892
893 EXEC(N'CREATE CLUSTERED INDEX [' + @CIName + N']
894 ON [' + @TableToMove + N'](' + @ColList + N')
895 WITH PAD_INDEX
896 ON [' + @File2Name + N']')
897 ELSE
898 EXEC(N'CREATE CLUSTERED INDEX [' + @CIName + N']
899 ON [' + @TableToMove + N'](' + @ColList + N')
900 ON [' + @File2Name + N']')
901 END
902END
903
904-- Great. Now the table is on the new file group.
905-- We now check the @MovePKAndAllUniqueConstraints bit,
906-- and if it is 1, we move the PK and all unique constraints
907-- of the table, to the new file group.
908-- Similarly, if the @MoveAllNonClusteredIndexes = 1 then
909-- we move the non-clustered indexes of the table to
910-- the new file group as well. The structure of the code
911-- for the UQ, PK and non-clustered indexes is the same,
912-- so we move them all together (with minor syntax changes where needed).
913-- One comment: The fillfactor and padindex are not carried over,
914-- for the indexes and constraints. A good DBA would set the defaults
915-- on both filegroups the same.
916
917-- Gather all the unique constraints that need to be moved to
918-- the new file group.
919IF @MovePKAndAllUniqueConstraints = 1
920BEGIN
921 -- Get all unique keys (including the PK) for the table.
922 INSERT INTO #tblKeysTable (KeyName, indid, type)
923 SELECT a.[name], a.indid, b.xtype
924 FROM sysindexes a WITH (NOLOCK)
925 INNER JOIN sysobjects b WITH (NOLOCK)
926 ON a.[name] = b.[name]
927 WHERE b.parent_obj = OBJECT_ID(@TableToMove)
928 AND b.xtype IN ('PK', 'UQ')
929 AND a.groupid = @SourceFileGroupID
930 AND a.indid > 0 AND a.indid < 255
931END
932
933-- Gather all the non-clustered indexes that need to be moved to
934-- the new file group.
935IF @MoveAllNonClusteredIndexes = 1
936BEGIN
937 INSERT INTO #tblKeysTable (KeyName, indid, type)
938 SELECT a.[name],
939 a.indid,
940 CASE
941 WHEN INDEXPROPERTY(OBJECT_ID(@TableToMove), a.[name], 'IsUnique') = 1
942 THEN 'UI' -- to denote unique index.
943 ELSE 'I' -- to denote a non-unique index
944 END
945 FROM sysindexes a WITH (NOLOCK)
946 LEFT OUTER JOIN #tblKeysTable b
947 ON a.name = b.KeyName
948 WHERE a.id = OBJECT_ID(@TableToMove)
949 AND INDEXPROPERTY(OBJECT_ID(@TableToMove), a.[name], 'IsStatistics') = 0
950 AND a.groupid = @SourceFileGroupID
951 AND b.KeyName IS NULL -- do not collect the PK and UQs again!
952 AND a.indid > 0 AND a.indid < 255
953END
954
955-- Now, loop through all keys/indexes collected in #tblKeysTable
956-- and move them one by one to the new filegroup, while
957-- maintaining the same column order they were previously defined on.
958WHILE EXISTS(SELECT * FROM #tblKeysTable)
959BEGIN
960 SET @indid = NULL
961 SET @Type = NULL
962 SET @KeyName = NULL
963
964 SELECT TOP 1 @indid = indid,
965 @Type = Type,
966 @KeyName = KeyName
967 FROM #tblKeysTable
968
969 DELETE FROM #tblColTable
970
971 INSERT INTO #tblColTable (ColName, IdxOrder)
972 SELECT COL_NAME(OBJECT_ID(@TableToMove), colid),
973 -- append the DESC/ASC string, based on the ASC/DESC order of the PK columns
974 CASE WHEN INDEXKEY_PROPERTY(OBJECT_ID(@TableToMove),
975 INDEXPROPERTY(OBJECT_ID(@TableToMove),
976 @KeyName,
977 'IndexID'),
978 keyno,
979 'IsDescending') = 1
980 THEN 'DESC'
981 ELSE 'ASC'
982 END
983 FROM sysindexkeys WITH (NOLOCK)
984 WHERE id = OBJECT_ID(@TableToMove)
985 AND indid = @indid
986 ORDER BY keyno ASC
987
988 SELECT @i = MIN(Idx)
989 FROM #tblColTable
990
991 SET @ColList = N''
992
993 WHILE EXISTS(SELECT * FROM #tblColTable WHERE Idx = @i)
994 BEGIN
995 SELECT @ColList = @ColList + N'[' + ColName + N'] ' + IdxOrder + N' ,'
996 FROM #tblColTable
997 WHERE Idx = @i
998
999 SET @i = @i + 1
1000 END
1001
1002 SET @ColList = LEFT(@ColList, LEN(@ColList) - 1)
1003
1004 -- Drop the object, and then recreate it on the new filegroup.
1005 -- Note: If a PK/UQ or a unique index (UI) is on the source file group,
1006 -- then it must be a NONCLUSTERED PK/UQ, since the CLUSTERED PK/UQ/UI was
1007 -- already handled above.
1008 -- Also - we first check whether the PK/UQ/UI are used in any foreign keys, before
1009 -- we drop/recreate them. If they are - then the FKs are first dropped,
1010 -- then the PK/UQ/UI is dropped and recreated, and the FK constraints
1011 -- are then recreated as well.
1012
1013 IF @Type IN ('PK', 'UQ', 'UI')
1014 BEGIN
1015 DELETE FROM #tblFKTable
1016
1017 -- Get all the FK constraints associated with the PK/UQ/UI (UI = unique index).
1018 -- Here, we must query sysreferences since it can identify which
1019 -- foreign keys reference the table that we are moving, as well as
1020 -- each individual unique constraint/index.
1021 INSERT INTO #tblFKTable (ForeignTableName, KeyName)
1022 SELECT OBJECT_NAME(fkeyid), OBJECT_NAME(constid)
1023 FROM sysreferences WITH (NOLOCK)
1024 WHERE rkeyid = OBJECT_ID(@TableToMove)
1025 AND rkeyindid = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IndexID')
1026
1027 SELECT @AssocFKeyName = MIN(KeyName)
1028 FROM #tblFKTable
1029
1030 WHILE @AssocFKeyName IS NOT NULL
1031 BEGIN
1032 -- Get the list of primary and then foreign columns
1033 -- for the collected FK constraints. The CASCADE UPDATE,
1034 -- CASCADE DELETE, and NOT FOR REPLICATION properties
1035 -- of the FK are considered later.
1036
1037 SELECT @FKTableName = ForeignTableName
1038 FROM #tblFKTable
1039 WHERE KeyName = @AssocFKeyName
1040
1041 DELETE FROM #tblFKColTable
1042
1043 -- First, the tables of the foreign table. The select is ordered by keyno
1044 -- so the order of columns in the FK will remain unchanged by the
1045 -- drop/recreate operation.
1046 INSERT INTO #tblFKColTable (ColName, FOrP)
1047 SELECT COL_NAME(fkeyid, fkey), 'F'
1048 FROM sysforeignkeys
1049 WHERE constid = OBJECT_ID(@AssocFKeyName)
1050 ORDER BY keyno
1051
1052 -- Similarly, for the primary table.
1053 INSERT INTO #tblFKColTable (ColName, FOrP)
1054 SELECT COL_NAME(rkeyid, rkey), 'P'
1055 FROM sysforeignkeys
1056 WHERE constid = OBJECT_ID(@AssocFKeyName)
1057 ORDER BY keyno
1058
1059 -- We now build the FK creation statement
1060 SELECT @j = MIN(Idx)
1061 FROM #tblFKColTable
1062
1063 SET @SQLStr = N'ALTER TABLE [' + @FKTableName + N'] '
1064 + N' WITH NOCHECK ADD CONSTRAINT [' + @AssocFKeyName + N'] '
1065 + N' FOREIGN KEY ('
1066
1067 -- Foreign table columns
1068 WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'F')
1069 BEGIN
1070 SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
1071 FROM #tblFKColTable
1072 WHERE Idx = @j
1073
1074 SET @j = @j + 1
1075 END
1076
1077 SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + N') REFERENCES [' + @TableToMove + N']('
1078
1079 -- Primary table columns
1080 WHILE EXISTS(SELECT * FROM #tblFKColTable WHERE Idx = @J AND FOrP = 'P')
1081 BEGIN
1082 SELECT @SQLStr = @SQLStr + N'[' + ColName + N'],'
1083 FROM #tblFKColTable
1084 WHERE Idx = @j
1085
1086 SET @j = @j + 1
1087 END
1088
1089 SET @SQLStr = LEFT(@SQLStr, LEN(@SQLStr) - 1) + ') '
1090
1091 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsDeleteCascade') = 1
1092 SET @SQLStr = @SQLStr + N' ON DELETE CASCADE '
1093
1094 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsUpdateCascade') = 1
1095 SET @SQLStr = @SQLStr + N' ON UPDATE CASCADE '
1096
1097 IF OBJECTPROPERTY(OBJECT_ID(@AssocFKeyName), 'CnstIsNotRepl') = 1
1098 SET @SQLStr = @SQLStr + N' NOT FOR REPLICATION '
1099
1100
1101 -- Now, store this SQL statement, and drop the FK constraint
1102 UPDATE #tblFKTable
1103 SET SQLStmt = @SQLStr
1104 WHERE KeyName = @AssocFKeyName
1105
1106 -- Drop the constraint
1107
1108 EXEC(N'ALTER TABLE [' + @FKTableName + '] DROP CONSTRAINT [' + @AssocFKeyName + N']')
1109
1110 SELECT @AssocFKeyName = MIN(KeyName)
1111 FROM #tblFKTable
1112 WHERE @AssocFKeyName < KeyName
1113 END
1114
1115 -- Now, drop the PK/UQ/UI and recreate it
1116 IF @Type = 'PK'
1117 BEGIN
1118
1119 EXEC(N' ALTER TABLE [' + @TableToMove + N']
1120 DROP CONSTRAINT [' + @KeyName + N'] ')
1121
1122 EXEC(N' ALTER TABLE [' + @TableToMove + N']
1123 WITH NOCHECK ADD CONSTRAINT [' + @KeyName + N']
1124 PRIMARY KEY NONCLUSTERED (' + @ColList + N')
1125 ON [' + @File2Name + N']')
1126 END
1127 ELSE
1128 BEGIN
1129 IF @Type = 'UQ'
1130 BEGIN
1131
1132 EXEC(N' ALTER TABLE [' + @TableToMove + N']
1133 DROP CONSTRAINT [' + @KeyName + N'] ')
1134
1135 EXEC(N' ALTER TABLE [' + @TableToMove + N']
1136 WITH NOCHECK ADD CONSTRAINT [' + @KeyName + N']
1137 UNIQUE NONCLUSTERED (' + @ColList + N')
1138 ON [' + @File2Name + N']')
1139 END
1140 ELSE -- must be UI
1141 BEGIN
1142 SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IsPadIndex')
1143
1144 EXEC(N' DROP INDEX [' + @TableToMove + N'].[' + @KeyName + N']')
1145
1146 IF @IsPadIndex = 1
1147
1148 EXEC(N' CREATE UNIQUE NONCLUSTERED INDEX [' + @KeyName + N']
1149 ON [' + @TableToMove + N'](' + @ColList + N')
1150 WITH PAD_INDEX
1151 ON [' + @File2Name + N']')
1152 ELSE
1153 EXEC(N' CREATE UNIQUE NONCLUSTERED INDEX [' + @KeyName + N']
1154 ON [' + @TableToMove + N'](' + @ColList + N')
1155 ON [' + @File2Name + N']')
1156 END
1157 END
1158
1159 -- And recreate the FK constraints attached to the PK/UQ
1160 -- that was just recreated.
1161 -- Recreate the FK constraint of the table.
1162 SELECT @AssocFKeyName = MIN(KeyName)
1163 FROM #tblFKTable
1164
1165 WHILE @AssocFKeyName IS NOT NULL
1166 BEGIN
1167 SELECT @SQLStr = SQLStmt
1168 FROM #tblFKTable
1169 WHERE KeyName = @AssocFKeyName
1170
1171 EXEC(@SQLStr)
1172
1173 SELECT @AssocFKeyName = MIN(KeyName)
1174 FROM #tblFKTable
1175 WHERE @AssocFKeyName < KeyName
1176 END
1177 END
1178
1179 IF @Type = 'I'
1180 BEGIN
1181 SET @IsPadIndex = INDEXPROPERTY(OBJECT_ID(@TableToMove), @KeyName, 'IsPadIndex')
1182
1183 EXEC(N' DROP INDEX [' + @TableToMove + N'].[' + @KeyName + N']')
1184
1185 IF @IsPadIndex = 1
1186
1187 EXEC(N' CREATE NONCLUSTERED INDEX [' + @KeyName + N']
1188 ON [' + @TableToMove + N'](' + @ColList + N')
1189 WITH PAD_INDEX
1190 ON [' + @File2Name + N']')
1191 ELSE
1192 EXEC(N' CREATE NONCLUSTERED INDEX [' + @KeyName + N']
1193 ON [' + @TableToMove + N'](' + @ColList + N')
1194 ON [' + @File2Name + N']')
1195 END
1196
1197 DELETE FROM #tblKeysTable
1198 WHERE KeyName = @KeyName
1199 AND indid = @indid
1200END
1201
1202
1203
1204
1205GO
1206/****** Object: Table [dbo].[BlockedIP] Script Date: 9/27/2016 12:09:29 PM ******/
1207SET ANSI_NULLS ON
1208GO
1209SET QUOTED_IDENTIFIER ON
1210GO
1211CREATE TABLE [dbo].[BlockedIP](
1212 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1213 [IP] [nvarchar](50) NOT NULL,
1214 [IsManual] [bit] NOT NULL,
1215 [BlockedTill] [datetime] NOT NULL,
1216 CONSTRAINT [PK_BlockedIP] PRIMARY KEY CLUSTERED
1217(
1218 [ID] ASC
1219)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1220) ON [PRIMARY]
1221
1222GO
1223/****** Object: Table [dbo].[CallArguments] Script Date: 9/27/2016 12:09:29 PM ******/
1224SET ANSI_NULLS ON
1225GO
1226SET QUOTED_IDENTIFIER ON
1227GO
1228CREATE TABLE [dbo].[CallArguments](
1229 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1230 [CallID] [bigint] NOT NULL,
1231 [ArgumentName] [nvarchar](50) NOT NULL,
1232 [ArgumentValue] [nvarchar](max) NULL,
1233 CONSTRAINT [PK_CallArguments] PRIMARY KEY CLUSTERED
1234(
1235 [ID] ASC
1236)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1237) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1238
1239GO
1240/****** Object: Table [dbo].[CallToUser] Script Date: 9/27/2016 12:09:29 PM ******/
1241SET ANSI_NULLS ON
1242GO
1243SET QUOTED_IDENTIFIER ON
1244GO
1245CREATE TABLE [dbo].[CallToUser](
1246 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1247 [CallID] [bigint] NOT NULL,
1248 [UserID] [bigint] NOT NULL,
1249 [CallTime] [datetime] NOT NULL,
1250 CONSTRAINT [PK_CallToUser] PRIMARY KEY CLUSTERED
1251(
1252 [ID] ASC
1253)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1254) ON [PRIMARY]
1255
1256GO
1257/****** Object: Table [dbo].[MethodCalls] Script Date: 9/27/2016 12:09:29 PM ******/
1258SET ANSI_NULLS ON
1259GO
1260SET QUOTED_IDENTIFIER ON
1261GO
1262CREATE TABLE [dbo].[MethodCalls](
1263 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1264 [RecordCreated] [datetime] NOT NULL CONSTRAINT [DF_MethodCalls_RecordCreated] DEFAULT (getdate()),
1265 [CallerID] [nvarchar](50) NULL,
1266 [Exception] [nvarchar](max) NULL,
1267 [ExceptionMessage] [nvarchar](max) NULL,
1268 [ClientIP] [nvarchar](50) NOT NULL,
1269 [ProxyIP] [nvarchar](50) NULL,
1270 [ASPSessionID] [nvarchar](50) NULL,
1271 [SecondsTaked] [int] NULL,
1272 [MethodID] [int] NOT NULL,
1273 [SystemUserID] [bigint] NULL,
1274 [CallDate] [date] NOT NULL CONSTRAINT [DF_MethodCalls_CallDate] DEFAULT (getdate()),
1275 CONSTRAINT [PK_MethodCalls] PRIMARY KEY CLUSTERED
1276(
1277 [ID] ASC
1278)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1279) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1280
1281GO
1282/****** Object: Table [dbo].[Methods] Script Date: 9/27/2016 12:09:29 PM ******/
1283SET ANSI_NULLS ON
1284GO
1285SET QUOTED_IDENTIFIER ON
1286GO
1287CREATE TABLE [dbo].[Methods](
1288 [ID] [int] IDENTITY(1,1) NOT NULL,
1289 [MethodName] [nvarchar](100) NOT NULL,
1290 [IsLogin] [bit] NOT NULL CONSTRAINT [DF_Methods_IsLogin] DEFAULT ((0)),
1291 CONSTRAINT [PK_Methods] PRIMARY KEY CLUSTERED
1292(
1293 [ID] ASC
1294)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1295) ON [PRIMARY]
1296
1297GO
1298/****** Object: Table [dbo].[OutPut] Script Date: 9/27/2016 12:09:29 PM ******/
1299SET ANSI_NULLS ON
1300GO
1301SET QUOTED_IDENTIFIER ON
1302GO
1303CREATE TABLE [dbo].[OutPut](
1304 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1305 [RecordCreated] [datetime] NOT NULL,
1306 [CallID] [bigint] NULL,
1307 [MessageSeverity] [int] NOT NULL,
1308 [MessageText] [nvarchar](max) NULL,
1309 CONSTRAINT [PK_OutPut] PRIMARY KEY CLUSTERED
1310(
1311 [ID] ASC
1312)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1313) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1314
1315GO
1316/****** Object: Table [dbo].[Sessions] Script Date: 9/27/2016 12:09:29 PM ******/
1317SET ANSI_NULLS ON
1318GO
1319SET QUOTED_IDENTIFIER ON
1320GO
1321CREATE TABLE [dbo].[Sessions](
1322 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1323 [RecordCreated] [datetime] NOT NULL,
1324 [ASPSessionID] [nvarchar](50) NOT NULL,
1325 [ClientIP] [nvarchar](50) NOT NULL,
1326 [ProxyIP] [nvarchar](50) NULL,
1327 [Cookies] [nvarchar](max) NULL,
1328 [Browser] [nvarchar](max) NULL,
1329 CONSTRAINT [PK_Sessions] PRIMARY KEY CLUSTERED
1330(
1331 [ID] ASC
1332)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1333) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1334
1335GO
1336/****** Object: Table [dbo].[Visits] Script Date: 9/27/2016 12:09:29 PM ******/
1337SET ANSI_NULLS ON
1338GO
1339SET QUOTED_IDENTIFIER ON
1340GO
1341CREATE TABLE [dbo].[Visits](
1342 [ID] [bigint] IDENTITY(1,1) NOT NULL,
1343 [SessionID] [bigint] NOT NULL,
1344 [PageVisited] [nvarchar](max) NOT NULL,
1345 [Referer] [nvarchar](max) NOT NULL,
1346 CONSTRAINT [PK_Visits] PRIMARY KEY CLUSTERED
1347(
1348 [ID] ASC
1349)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1350) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1351
1352GO
1353/****** Object: View [dbo].[IP_Captcha] Script Date: 9/27/2016 12:09:29 PM ******/
1354SET ANSI_NULLS ON
1355GO
1356SET QUOTED_IDENTIFIER ON
1357GO
1358CREATE VIEW [dbo].[IP_Captcha]
1359AS
1360SELECT ClientIP
1361FROM dbo.MethodCalls AS mc
1362WHERE (MethodID IN (357, 358)) AND (RecordCreated > DATEADD(HOUR, - 2, GETDATE())) AND (ExceptionMessage = 'User_Not_Found' OR
1363 ExceptionMessage = 'Password_incorrect')
1364GROUP BY ClientIP
1365HAVING (COUNT(ClientIP) > 2)
1366
1367
1368
1369
1370
1371GO
1372SET ANSI_PADDING ON
1373
1374GO
1375/****** Object: Index [ix_ip] Script Date: 9/27/2016 12:09:29 PM ******/
1376CREATE NONCLUSTERED INDEX [ix_ip] ON [dbo].[BlockedIP]
1377(
1378 [IP] ASC,
1379 [BlockedTill] ASC
1380)
1381INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1382GO
1383SET ANSI_PADDING ON
1384
1385GO
1386/****** Object: Index [ix_ArgumentName] Script Date: 9/27/2016 12:09:29 PM ******/
1387CREATE NONCLUSTERED INDEX [ix_ArgumentName] ON [dbo].[CallArguments]
1388(
1389 [ArgumentName] ASC
1390)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1391GO
1392/****** Object: Index [ix_call] Script Date: 9/27/2016 12:09:29 PM ******/
1393CREATE NONCLUSTERED INDEX [ix_call] ON [dbo].[CallArguments]
1394(
1395 [CallID] ASC
1396)
1397INCLUDE ( [ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1398GO
1399/****** Object: Index [ix_callID] Script Date: 9/27/2016 12:09:29 PM ******/
1400CREATE UNIQUE NONCLUSTERED INDEX [ix_callID] ON [dbo].[CallToUser]
1401(
1402 [CallID] ASC
1403)
1404INCLUDE ( [ID]) 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]
1405GO
1406/****** Object: Index [ix_user_time] Script Date: 9/27/2016 12:09:29 PM ******/
1407CREATE NONCLUSTERED INDEX [ix_user_time] ON [dbo].[CallToUser]
1408(
1409 [UserID] ASC
1410)
1411INCLUDE ( [CallID],
1412 [CallTime]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1413GO
1414SET ANSI_PADDING ON
1415
1416GO
1417/****** Object: Index [_dta_index_MethodCalls_5_21575115__K10_K2_K6_4] Script Date: 9/27/2016 12:09:29 PM ******/
1418CREATE NONCLUSTERED INDEX [_dta_index_MethodCalls_5_21575115__K10_K2_K6_4] ON [dbo].[MethodCalls]
1419(
1420 [MethodID] ASC,
1421 [RecordCreated] ASC,
1422 [ClientIP] ASC
1423)
1424INCLUDE ( [Exception]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1425GO
1426SET ANSI_PADDING ON
1427
1428GO
1429/****** Object: Index [ix_CallDate] Script Date: 9/27/2016 12:09:29 PM ******/
1430CREATE NONCLUSTERED INDEX [ix_CallDate] ON [dbo].[MethodCalls]
1431(
1432 [CallDate] ASC
1433)
1434INCLUDE ( [ExceptionMessage],
1435 [MethodID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1436GO
1437SET ANSI_PADDING ON
1438
1439GO
1440/****** Object: Index [ix_Created] Script Date: 9/27/2016 12:09:29 PM ******/
1441CREATE NONCLUSTERED INDEX [ix_Created] ON [dbo].[MethodCalls]
1442(
1443 [RecordCreated] ASC
1444)
1445INCLUDE ( [ClientIP]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1446GO
1447/****** Object: Index [ix_Created2] Script Date: 9/27/2016 12:09:29 PM ******/
1448CREATE NONCLUSTERED INDEX [ix_Created2] ON [dbo].[MethodCalls]
1449(
1450 [RecordCreated] ASC
1451)
1452INCLUDE ( [ID],
1453 [MethodID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1454GO
1455SET ANSI_PADDING ON
1456
1457GO
1458/****** Object: Index [ix_test] Script Date: 9/27/2016 12:09:29 PM ******/
1459CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[MethodCalls]
1460(
1461 [CallerID] ASC,
1462 [ASPSessionID] ASC,
1463 [MethodID] ASC
1464)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1465GO
1466/****** Object: Index [ix_UserID] Script Date: 9/27/2016 12:09:29 PM ******/
1467CREATE NONCLUSTERED INDEX [ix_UserID] ON [dbo].[MethodCalls]
1468(
1469 [SystemUserID] ASC
1470)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1471GO
1472SET ANSI_PADDING ON
1473
1474GO
1475/****** Object: Index [ix_Name] Script Date: 9/27/2016 12:09:29 PM ******/
1476CREATE UNIQUE NONCLUSTERED INDEX [ix_Name] ON [dbo].[Methods]
1477(
1478 [MethodName] ASC
1479)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]
1480GO
1481SET ANSI_PADDING ON
1482
1483GO
1484/****** Object: Index [_dta_index_OutPut_5_85575343__K4_1_3_5] Script Date: 9/27/2016 12:09:29 PM ******/
1485CREATE NONCLUSTERED INDEX [_dta_index_OutPut_5_85575343__K4_1_3_5] ON [dbo].[OutPut]
1486(
1487 [MessageSeverity] ASC
1488)
1489INCLUDE ( [ID],
1490 [CallID],
1491 [MessageText]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1492GO
1493/****** Object: Index [ix_Call_Severity] Script Date: 9/27/2016 12:09:29 PM ******/
1494CREATE NONCLUSTERED INDEX [ix_Call_Severity] ON [dbo].[OutPut]
1495(
1496 [CallID] ASC,
1497 [MessageSeverity] ASC
1498)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1499GO
1500SET ANSI_PADDING ON
1501
1502GO
1503/****** Object: Index [ix_session] Script Date: 9/27/2016 12:09:29 PM ******/
1504CREATE NONCLUSTERED INDEX [ix_session] ON [dbo].[Sessions]
1505(
1506 [ASPSessionID] ASC
1507)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1508GO
1509SET ANSI_PADDING ON
1510
1511GO
1512/****** Object: Index [_dta_index_Visits_5_53575229__K2_K1_3_4] Script Date: 9/27/2016 12:09:29 PM ******/
1513CREATE NONCLUSTERED INDEX [_dta_index_Visits_5_53575229__K2_K1_3_4] ON [dbo].[Visits]
1514(
1515 [SessionID] ASC,
1516 [ID] ASC
1517)
1518INCLUDE ( [PageVisited],
1519 [Referer]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1520GO
1521/****** Object: Index [ix_session] Script Date: 9/27/2016 12:09:29 PM ******/
1522CREATE NONCLUSTERED INDEX [ix_session] ON [dbo].[Visits]
1523(
1524 [SessionID] ASC
1525)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1526GO
1527ALTER TABLE [dbo].[OutPut] ADD CONSTRAINT [DF_OutPut_RecordCreated] DEFAULT (getdate()) FOR [RecordCreated]
1528GO
1529ALTER TABLE [dbo].[Sessions] ADD CONSTRAINT [DF_Table_1_Created] DEFAULT (getdate()) FOR [RecordCreated]
1530GO
1531ALTER TABLE [dbo].[CallArguments] WITH CHECK ADD CONSTRAINT [FK_CallArguments_MethodCalls] FOREIGN KEY([CallID])
1532REFERENCES [dbo].[MethodCalls] ([ID])
1533GO
1534ALTER TABLE [dbo].[CallArguments] CHECK CONSTRAINT [FK_CallArguments_MethodCalls]
1535GO
1536ALTER TABLE [dbo].[MethodCalls] WITH CHECK ADD CONSTRAINT [FK_MethodCalls_Methods] FOREIGN KEY([MethodID])
1537REFERENCES [dbo].[Methods] ([ID])
1538GO
1539ALTER TABLE [dbo].[MethodCalls] CHECK CONSTRAINT [FK_MethodCalls_Methods]
1540GO
1541ALTER TABLE [dbo].[OutPut] WITH CHECK ADD CONSTRAINT [FK_OutPut_MethodCalls] FOREIGN KEY([CallID])
1542REFERENCES [dbo].[MethodCalls] ([ID])
1543GO
1544ALTER TABLE [dbo].[OutPut] CHECK CONSTRAINT [FK_OutPut_MethodCalls]
1545GO
1546ALTER TABLE [dbo].[Visits] WITH CHECK ADD CONSTRAINT [FK_Visits_Sessions] FOREIGN KEY([SessionID])
1547REFERENCES [dbo].[Sessions] ([ID])
1548ON DELETE CASCADE
1549GO
1550ALTER TABLE [dbo].[Visits] CHECK CONSTRAINT [FK_Visits_Sessions]
1551GO
1552EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
1553Begin DesignProperties =
1554 Begin PaneConfigurations =
1555 Begin PaneConfiguration = 0
1556 NumPanes = 4
1557 Configuration = "(H (1[40] 4[20] 2[20] 3) )"
1558 End
1559 Begin PaneConfiguration = 1
1560 NumPanes = 3
1561 Configuration = "(H (1 [50] 4 [25] 3))"
1562 End
1563 Begin PaneConfiguration = 2
1564 NumPanes = 3
1565 Configuration = "(H (1 [50] 2 [25] 3))"
1566 End
1567 Begin PaneConfiguration = 3
1568 NumPanes = 3
1569 Configuration = "(H (4 [30] 2 [40] 3))"
1570 End
1571 Begin PaneConfiguration = 4
1572 NumPanes = 2
1573 Configuration = "(H (1 [56] 3))"
1574 End
1575 Begin PaneConfiguration = 5
1576 NumPanes = 2
1577 Configuration = "(H (2 [66] 3))"
1578 End
1579 Begin PaneConfiguration = 6
1580 NumPanes = 2
1581 Configuration = "(H (4 [50] 3))"
1582 End
1583 Begin PaneConfiguration = 7
1584 NumPanes = 1
1585 Configuration = "(V (3))"
1586 End
1587 Begin PaneConfiguration = 8
1588 NumPanes = 3
1589 Configuration = "(H (1[56] 4[18] 2) )"
1590 End
1591 Begin PaneConfiguration = 9
1592 NumPanes = 2
1593 Configuration = "(H (1 [75] 4))"
1594 End
1595 Begin PaneConfiguration = 10
1596 NumPanes = 2
1597 Configuration = "(H (1[66] 2) )"
1598 End
1599 Begin PaneConfiguration = 11
1600 NumPanes = 2
1601 Configuration = "(H (4 [60] 2))"
1602 End
1603 Begin PaneConfiguration = 12
1604 NumPanes = 1
1605 Configuration = "(H (1) )"
1606 End
1607 Begin PaneConfiguration = 13
1608 NumPanes = 1
1609 Configuration = "(V (4))"
1610 End
1611 Begin PaneConfiguration = 14
1612 NumPanes = 1
1613 Configuration = "(V (2))"
1614 End
1615 ActivePaneConfig = 0
1616 End
1617 Begin DiagramPane =
1618 Begin Origin =
1619 Top = 0
1620 Left = 0
1621 End
1622 Begin Tables =
1623 Begin Table = "mc"
1624 Begin Extent =
1625 Top = 6
1626 Left = 38
1627 Bottom = 114
1628 Right = 223
1629 End
1630 DisplayFlags = 280
1631 TopColumn = 0
1632 End
1633 End
1634 End
1635 Begin SQLPane =
1636 End
1637 Begin DataPane =
1638 Begin ParameterDefaults = ""
1639 End
1640 End
1641 Begin CriteriaPane =
1642 Begin ColumnWidths = 12
1643 Column = 1440
1644 Alias = 900
1645 Table = 1170
1646 Output = 720
1647 Append = 1400
1648 NewValue = 1170
1649 SortType = 1350
1650 SortOrder = 1410
1651 GroupBy = 1350
1652 Filter = 1350
1653 Or = 1350
1654 Or = 1350
1655 Or = 1350
1656 End
1657 End
1658End
1659' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IP_Captcha'
1660GO
1661EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'IP_Captcha'
1662GO
1663USE [master]
1664GO
1665ALTER DATABASE [TrackingSupport] SET READ_WRITE
1666GO