USE [VIGIL-VA]
GO


-- DROP

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_shrink_db]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_shrink_db]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_reindex]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_reindex]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_vigil_get_db_indexes]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_vigil_get_db_indexes]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_va_update_rule_type]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_va_update_rule_type]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_update_va_data_privacy]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_update_va_data_privacy]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_scavenger_get_oldest_heatmap]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_scavenger_get_oldest_heatmap]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_va]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_va]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_prune_table]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_prune_table]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_va_pos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_va_pos]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_va_faces]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_va_faces]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_va_counter]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_va_counter]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_insert_va_alarms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_insert_va_alarms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_va_pos]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_va_pos]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_va_faces]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_va_faces]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_va_counters]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_va_counters]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_va_alarms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_va_alarms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_footage_date_range]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_footage_date_range]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_counters_hourly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_counters_hourly]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_dwell_hourly]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_get_dwell_hourly]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_va_data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_va_data]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_invalid_records]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_invalid_records]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_heatmap]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_heatmap]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_delete_analytics_alarms]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_delete_analytics_alarms]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_check_tables]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_check_tables]
GO


-- CREATE

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_analytics_alarms]
@cameranumber int,
@ruleid int
AS
	BEGIN
		delete from vacounter where camera_number = @cameranumber and rule_id = @ruleid
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_heatmap]
@time DATETIME
AS
	DELETE FROM vacounter
	WHERE year(start_time) = year(@time)
	AND month(start_time) = month(@time)
	AND day(start_time) = day(@time)
	AND rule_type = (SELECT TOP 1 idx FROM va_rule_type WHERE [description] = 'Heatmap')
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_invalid_records]
AS
BEGIN
	SET NOCOUNT ON

	declare @countVacounter int
	declare @countVaAlarms int
	declare @countVaFaces int
	declare @countVaPos int

	select @countVacounter = count(*) from vacounter where start_time < '1900-01-01' OR insert_time < '1900-01-01'
	select @countVaAlarms = count(*) from va_alarms where start_time < '1900-01-01' OR end_time < '1900-01-01'
	select @countVaFaces = count(*) from va_faces where start_time < '1900-01-01' OR end_time < '1900-01-01'
	select @countVaPos = count(*) from va_pos where start_time < '1900-01-01' OR end_time < '1900-01-01'

	if @countVacounter > 0
		delete from vacounter where start_time < '1900-01-01' OR insert_time < '1900-01-01'

	if @countVaAlarms > 0
		delete from va_alarms where start_time < '1900-01-01' OR end_time < '1900-01-01'

	if @countVaFaces > 0
		delete from va_faces where start_time < '1900-01-01' OR end_time < '1900-01-01'

	if @countVaPos > 0
		delete from va_pos where start_time < '1900-01-01' OR end_time < '1900-01-01'

	select * from
	(
		values
		('vacounter', @countVacounter),
		('va_alarms', @countVaAlarms),
		('va_faces', @countVaFaces),
		('va_pos', @countVaPos)
	) as t([table], invalid_record_count)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_delete_va_data]
@camera_number int,
@rule_id int,
@rule_name nvarchar(100),
@from_counter bit,
@from_pos bit,
@from_alarms bit,
@from_faces bit = 0
AS
BEGIN
	SET NOCOUNT ON

	IF @from_counter = 1
	BEGIN
		DELETE FROM vacounter WHERE camera_number = @camera_number AND rule_id = @rule_id
	END

	IF @from_pos = 1
	BEGIN
		DELETE FROM va_pos WHERE camera_number = @camera_number AND rule_name = @rule_name
	END

	IF @from_alarms = 1
	BEGIN
		DELETE FROM va_alarms WHERE camera_number = @camera_number AND rule_id = @rule_id
	END
	IF @from_faces = 1
	BEGIN
		DELETE FROM va_faces WHERE camera_number = @camera_number AND rule_id = @rule_id
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_counters_hourly]
@startDate datetime,
@endDate datetime
AS
	SELECT CONVERT(nvarchar(10), [day], 120) as [day], [hour], [rule], camera, sum([value]) as [value], sum([record_count]) as record_count FROM
	(
		-- UDP counter
		SELECT cast(insert_time as date) AS [day], datepart(hour, insert_time) AS [hour], substring(rule_name, 1, len(rule_name) - 8) AS [rule], camera_number as camera, count(*) AS [value], count(*) as record_count FROM vacounter
		WHERE rule_type = 12
		AND rule_name like '%[[]count]'
		AND insert_time BETWEEN @startDate AND @endDate
		AND counter != 0
		GROUP BY cast(insert_time as date), datepart(hour, insert_time), rule_name, camera_number

		UNION ALL

		-- VIGIL native counter
		SELECT cast(insert_time as date) AS [day], datepart(hour, insert_time) AS [hour], rule_name AS [rule], camera_number as camera, sum(counter) AS [value], count(*) as record_count FROM vacounter
		WHERE rule_type in (1, 9)
		AND insert_time BETWEEN @startDate AND @endDate
		GROUP BY cast(insert_time as date), datepart(hour, insert_time), rule_name, camera_number
	) counters
	GROUP BY [day], [hour], [rule], camera
	HAVING SUM([value]) > 0
	ORDER BY [day], [hour], camera, [rule]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_dwell_hourly]
@startDate datetime,
@endDate datetime
AS
	SELECT CONVERT(nvarchar(10), [day], 120) as [day], [hour], [rule], camera, sum([value]) as [value], sum([record_count]) as record_count FROM
	(
		-- UDP wait
		SELECT cast(insert_time as date) AS [day], datepart(hour, insert_time) AS [hour], substring(rule_name, 1, len(rule_name) - 7) AS [rule], sum(counter) AS [value], camera_number as camera, count(*) as record_count FROM vacounter
		WHERE rule_type = 12
		AND rule_name like '%[[]wait]'
		AND insert_time BETWEEN @startDate AND @endDate
		GROUP BY cast(insert_time as date), datepart(hour, insert_time), rule_name, camera_number

		UNION ALL

		-- VIGIL native dwell rule
		SELECT cast(end_time as date) AS [day], datepart(hour, end_time) AS [hour], rule_name AS [rule], sum(dwell) AS [value], camera_number as camera, count(*) as record_count FROM va_pos
		WHERE end_time BETWEEN @startDate AND @endDate
		GROUP BY cast(end_time as date), datepart(hour, end_time), rule_name, camera_number
	) counters
	GROUP BY [day], [hour], [rule], camera
	ORDER BY [day], [hour], camera, [rule]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_footage_date_range]
@partition_type INT
AS
BEGIN
	-- @partition_type corresponds to ATLVigil.EnumPartitionType
	
	IF @partition_type = 5
		SELECT MIN(start_time) as start_time, MAX(start_time) as end_time
		FROM vacounter WITH (NOLOCK)
	ELSE IF @partition_type = 6
		SELECT MIN(start_time) as start_time, MAX(end_time) as end_time
		FROM va_alarms WITH (NOLOCK)
	ELSE IF @partition_type = 7
		SELECT MIN(start_time) as start_time, MAX(end_time) as end_time
		FROM va_pos WITH (NOLOCK)
	ELSE IF @partition_type = 8
		SELECT MIN(start_time) as start_time, MAX(end_time) as end_time
		FROM va_faces WITH (NOLOCK)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_va_alarms]
@start_time DATETIME,
@end_time DATETIME
AS
BEGIN
	SELECT c.name AS camera_name, v.rule_name, r.description AS rule_type, v.start_time, v.end_time, v.counter, v.period, v.description
	FROM va_alarms v WITH (NOLOCK), [VIGIL-DVR].[dbo].[cam_name] c WITH (NOLOCK), va_rule_type r WITH (NOLOCK)
	WHERE c.cam_num = v.camera_number AND
		r.idx = v.rule_type AND
		v.start_time >= @start_time AND
		v.start_time <= @end_time
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_va_counters]
@start_time DATETIME,
@end_time DATETIME
AS
BEGIN
	SELECT c.name AS camera_name, v.rule_name, r.description AS rule_type, v.start_time, v.insert_time, v.counter
	FROM vacounter v WITH (NOLOCK), [VIGIL-DVR].[dbo].[cam_name] c WITH (NOLOCK), va_rule_type r WITH (NOLOCK)
	WHERE c.cam_num = v.camera_number AND
		r.idx = v.rule_type AND
		v.start_time >= @start_time AND
		v.start_time <= @end_time
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_va_faces]
@start_time DATETIME,
@end_time DATETIME
AS
BEGIN
	SELECT c.name AS camera_name, v.rule_name, r.description AS rule_type, v.start_time, v.end_time, v.gender, v.age, v.age_deviation, v.happy, v.sad, v.surprised, v.angry
	FROM va_faces v WITH (NOLOCK), [VIGIL-DVR].[dbo].[cam_name] c WITH (NOLOCK), va_rule_type r WITH (NOLOCK)
	WHERE c.cam_num = v.camera_number AND
		r.idx = v.rule_type AND
		v.start_time >= @start_time AND
		v.start_time <= @end_time
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_get_va_pos]
@start_time DATETIME,
@end_time DATETIME
AS
BEGIN
	SELECT c.name AS camera_name, v.start_time, v.end_time, v.register_num, v.rule_name, v.dwell
	FROM va_pos v WITH (NOLOCK), [VIGIL-DVR].[dbo].[cam_name] c WITH (NOLOCK)
	WHERE c.cam_num = v.camera_number AND
		v.start_time >= @start_time AND
		v.start_time <= @end_time
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_va_alarms]
@CameraNum INT,
@RuleID INT,
@RuleType INT,
@Start_Time DATETIME,
@End_Time DATETIME,
@Counter INT,
@period INT,
@RuleName nvarchar (100),
@Description nvarchar (256),
@IsPrivate BIT = 0,
@Details nvarchar (2000) = NULL
AS
	SET NOCOUNT ON

	INSERT INTO va_alarms
	(camera_number, rule_id, rule_type, start_time, end_time, counter, period, rule_name, description, is_private, details)
	VALUES
	(@CameraNum, @RuleID, @RuleType, @Start_time, @End_Time, @Counter, @Period, @RuleName, @description, @IsPrivate, @Details)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_va_counter]
@CameraNum INT,
@RuleID INT,
@RuleType INT,
@Start_Time DATETIME,
@Insert_Time DATETIME,
@Counter INT,
@RuleName nvarchar (100),
@IsPrivate BIT = 0,
@Details nvarchar (2000) = NULL
AS
	SET NOCOUNT ON

	INSERT INTO vacounter
	(camera_number, rule_id, rule_type, start_time, insert_time, counter, rule_name, is_private, details)
	VALUES
	(@CameraNum, @RuleID, @RuleType, @Start_time, @Insert_Time, @counter, @RuleName, @IsPrivate, @Details)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_va_faces]
@cameranum INT,
@ruleid INT,
@ruletype INT,
@start_time DATETIME,
@end_time DATETIME,
@face_id BIGINT,
@gender INT,
@age INT,
@age_deviation INT,
@happy INT,
@sad INT,
@surprised INT,
@angry INT,
@rulename nvarchar (100),
@isprivate BIT = 0
AS
	SET NOCOUNT ON

	INSERT INTO va_faces
	(camera_number, rule_id, rule_type, start_time, end_time, gender, age, age_deviation, happy, sad, surprised, angry, rule_name, is_private)
	VALUES
	(@cameranum, @ruleid, @ruletype, @start_time, @end_time, @gender, @age, @age_deviation, @happy, @sad, @surprised, @angry, @rulename, @isprivate)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_insert_va_pos]
@CameraNum INT,
@Start_Time DATETIME,
@End_Time DATETIME,
@Register_Num INT,
@Rule_Name nvarchar (100),
@Dwell INT,
@IsPrivate BIT = 0
AS
	SET NOCOUNT ON

	INSERT INTO va_pos
	(camera_number, start_time, end_time, register_num, rule_name, dwell, is_private)
	VALUES
	(@CameraNum, @Start_time, @End_Time, @Register_Num, @Rule_Name, @Dwell, @IsPrivate)
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_table]
@table nvarchar(30),
@maxcount INT
AS
BEGIN
	DECLARE @max BIGINT
	DECLARE @min BIGINT
	IF @table = 'va_pos'
	BEGIN
		SELECT @min = MIN(idx), @max = MAX(idx) FROM va_pos
		IF @max - @min >= @maxcount + 1000
			DELETE FROM va_pos WHERE idx <= @max - @maxcount
	END
	IF @table = 'va_alarms'
	BEGIN
		SELECT @min = MIN(idx), @max = MAX(idx) FROM va_alarms
		IF @max - @min >= @maxcount + 1000
			DELETE FROM va_alarms WHERE idx <= @max - @maxcount
	END
	IF @table = 'va_faces'
	BEGIN
		SELECT @min = MIN(idx), @max = MAX(idx) FROM va_faces
		IF @max - @min >= @maxcount + 1000
			DELETE FROM va_faces WHERE idx <= @max - @maxcount
	END
	IF @table = 'vacounter'
	BEGIN
		SELECT @min = MIN(idx), @max = MAX(idx) FROM vacounter
		IF @max - @min >= @maxcount + 1000
			DELETE FROM vacounter WHERE idx <= @max - @maxcount
	END
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_prune_va]
@startTime as DATETIME = NULL,
@endTime as DATETIME = NULL,
@chunkSize as INT = 10000
AS
	SET NOCOUNT ON
	DECLARE @deleteCount INT
	SELECT @deleteCount = 1

	IF (@startTime IS NULL) OR (@endTime IS NULL)
	BEGIN
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM vacounter
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1

		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_pos
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1
		
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_alarms
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1
		
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_faces
			SELECT @deleteCount = @@ROWCOUNT
		END
	END
	ELSE
	BEGIN
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM vacounter WHERE start_time >= @startTime AND insert_time <= @endTime
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1
		
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_alarms WHERE start_time >= @startTime AND end_time <= @endTime
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1
		
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_faces WHERE start_time >= @startTime AND end_time <= @endTime
			SELECT @deleteCount = @@ROWCOUNT
		END
		
		SELECT @deleteCount = 1
		
		WHILE (@deleteCount > 0)
		BEGIN
			DELETE TOP(@chunkSize) FROM va_pos WHERE start_time >= @startTime AND end_time <= @endTime
			SELECT @deleteCount = @@ROWCOUNT
		END
	END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_scavenger_get_oldest_heatmap]
AS
	SELECT TOP(1) va.start_time as start_time
	FROM vacounter va
	WHERE va.rule_type = 13
	ORDER BY va.start_time
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_update_va_data_privacy]
@camera_number int,
@rule_id int,
@rule_name nvarchar(100),
@is_private bit
AS
BEGIN
	SET NOCOUNT ON

	-- Ignore for rule_type 13 (heatmap)
	UPDATE vacounter SET is_private = @is_private WHERE camera_number = @camera_number AND rule_id = @rule_id AND rule_type <> 13

	UPDATE va_alarms SET is_private = @is_private WHERE camera_number = @camera_number AND rule_id = @rule_id
	UPDATE va_faces SET is_private = @is_private WHERE camera_number = @camera_number AND rule_id = @rule_id
	UPDATE va_pos SET is_private = @is_private WHERE camera_number = @camera_number AND rule_name = @rule_name
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_va_update_rule_type]
@idx INT,
@description nvarchar(100)
AS
BEGIN
	IF EXISTS(select * from va_rule_type where idx = @idx)
		UPDATE va_rule_type set description = @description where idx = @idx
	ELSE
		INSERT INTO [dbo].[va_rule_type] ([idx], [description]) VALUES (@idx, @description)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_get_db_indexes]
AS
BEGIN
	SET NOCOUNT ON

	create table #results (
		[table] varchar(100),
		[index] varchar(100)
	)

	DECLARE @database varchar(100)
	DECLARE @table varchar(100)
	DECLARE @index varchar(100)

	SELECT @database = DB_NAME()

	DECLARE tableCursor CURSOR FOR
		SELECT name FROM sys.objects
		WHERE [type] = 'U'
	
	OPEN tableCursor

	FETCH NEXT FROM tableCursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		DECLARE indexCursor CURSOR FOR
			SELECT name FROM sys.indexes
			WHERE [object_id] = object_id(@table)
			AND (name LIKE 'ix_%' OR name LIKE 'pk_%')

		OPEN indexCursor

		FETCH NEXT FROM indexCursor INTO @index

		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO #results([table], [index]) VALUES(@table, @index)

			FETCH NEXT FROM indexCursor INTO @index
		END

		CLOSE indexCursor
		DEALLOCATE indexCursor

		FETCH NEXT FROM tableCursor INTO @table
	END

	CLOSE tableCursor
	DEALLOCATE tableCursor

	SELECT * FROM #results
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_reindex]
AS
BEGIN
	DECLARE @TableName sysname
	DECLARE @sql nvarchar(1000)
	DECLARE #Table_Name CURSOR FOR SELECT name FROM sys.objects WHERE [type] = 'u' AND OBJECTPROPERTY ([object_id], 'ismsshipped') = 0 AND OBJECTPROPERTY([object_id], 'OwnerID') = USER_ID('dbo')

	OPEN #Table_Name

	FETCH NEXT FROM #Table_Name INTO @TableName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @sql = 'ALTER INDEX ALL ON ' + quotename(@TableName) + ' REBUILD'
		PRINT @sql
		EXEC(@sql)
		FETCH NEXT FROM #Table_Name INTO @TableName
	END
	
	CLOSE #Table_Name
	DEALLOCATE #Table_Name
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_vigil_shrink_db]
AS
BEGIN
	DBCC SHRINKDATABASE ('VIGIL-VA')
	DBCC SHRINKFILE('VIGIL-VA', 1)
	DBCC SHRINKFILE('VIGIL-VA_LOG', 1)
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_check_tables]
AS
BEGIN
	-- Perform simple query on each table in database.
	-- Output a list of tables for which queries failed.

	SET NOCOUNT ON 

	CREATE TABLE #suspect
	(
		suspect_table sysname
	)

	DECLARE @table sysname

	DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables
	OPEN table_cursor
	FETCH NEXT FROM table_cursor INTO @table

	WHILE @@FETCH_STATUS = 0
	BEGIN
		BEGIN TRY
			EXEC('SELECT TOP 1 * INTO #tmp FROM [' + @table + ']')
		END TRY
		BEGIN CATCH
			INSERT INTO #suspect VALUES (@table)
		END CATCH

		FETCH NEXT FROM table_cursor INTO @table
	END

	CLOSE table_cursor
	DEALLOCATE table_cursor

	SELECT * FROM #suspect
END
GO
