set nocount on

Declare @name NVARCHAR(128), @rows NVARCHAR(128), @reserved NVARCHAR(128), @data NVARCHAR(128), @index_size NVARCHAR(128), @unused NVARCHAR(128), 
@SpaceUsed NVARCHAR(500)

declare @index_name sysname = '', @is_clustered bit, @column_name sysname, @is_primary bit, @column_count smallint, @sql varchar(2000)

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'ExecutionStatus')
Begin
	CREATE TABLE ExecutionStatus ( Query varchar(400), Status int, executionTime datetime )
End

Create Table #tbl_sp_spaceused (name varchar(100) NULL, rows bigint NULL, reserved varchar(20) NULL, 
		data varchar(20) NULL, index_size varchar(20) NULL, unused varchar(20) NULL )

Create Table #index_details (index_name sysname,type_desc sysname, column_name sysname, column_count smallint,is_primary_key bit)

insert into #index_details
select t1.indexname, t1.type_desc, t1.colname, t2.howmany,t1.is_primary_key 
from
	(
	select i.object_id, i.index_id, indexname = i.name, colName = c.name,type_desc = i.type_desc , i.is_primary_key
	from sys.indexes i
	inner join sys.index_columns ic
		on ic.object_id = i.object_id
		and ic.index_id = i.index_id
	join sys.columns c 
		on c.object_id = ic.object_id
		and c.column_id = ic.column_id
	where i.object_id = object_id('event_audit_mstr')
	and ic.key_ordinal = 1
)t1
cross apply
( select howmany = count(index_column_id)
	from  sys.index_columns
	where object_id = t1.object_id and index_id = t1.index_id
)t2

-------------------------------------------------------------------------------------------------------

------------scpturing spaceused information
INSERT INTO #tbl_sp_spaceused ( name, rows, reserved, data, index_size, unused )
EXEC sp_spaceused event_audit_mstr
select @name = name, @rows = rows, @reserved = reserved, @data = data, @index_size = index_size, @unused = unused from #tbl_sp_spaceused
Set @SpaceUsed =  'name = '+ @name + ' , rows = ' + @rows + ', reserved = ' + @reserved+ ', data = ' +@data + ' , index_size = ' + @index_size + ', unused = ' + @unused;
Insert Into ExecutionStatus Values(@SpaceUsed,-99,CURRENT_TIMESTAMP)

-------------------------------------------------Deleting nonclustered index
Set @sql = ''
while(1=1)
begin
	set @index_name  = ''
	select top(1) @index_name = index_name, @column_name = column_name, @column_count = column_count from #index_details 
	where type_desc = 'Nonclustered' And is_primary_key = 0
	if @index_name = '' break;

	if(@column_count > 1)
		set @sql = 'drop index [' + @index_name +  '] on dbo.event_audit_mstr'

	else if(@column_name not in('person_id', 'created_by', 'Action_id'))
		set @sql = 'drop index [' + @index_name +  '] on dbo.event_audit_mstr'

	else if(@column_name in('person_id', 'created_by', 'Action_id') 
			And Exists(Select 1 from #index_details where type_desc = 'clustered' and column_name != 'event_timestamp'))
		set @sql = 'drop index [' + @index_name +  '] on dbo.event_audit_mstr'

	if(@sql != '')
	Begin
		Exec(@sql)
		Insert Into ExecutionStatus Values(@sql,@@ERROR,CURRENT_TIMESTAMP)
	End
	Set @sql = ''
	delete from #index_details where index_name = @index_name
end
GO
------------------------------------------------ Deleting primary key 
declare @index_name sysname = '', @is_clustered bit, @column_name sysname, @is_primary bit, @column_count smallint,@type_desc sysname, 
@sql varchar(2000)

select top(1) @index_name = index_name, @column_name = column_name, @column_count = column_count, @type_desc = type_desc from #index_details 
where is_primary_key = 1

if(@type_desc = 'Clustered') or (@type_desc = 'Nonclustered' And @column_name <> 'event_id')
begin
	set @sql = 'alter table dbo.event_audit_mstr drop constraint [' +  @index_name + ']'
	exec(@sql)
	Insert Into ExecutionStatus Values(@sql,@@ERROR,CURRENT_TIMESTAMP)
end

delete from #index_details where index_name = @index_name
GO
------------------------------------------------ Deleting clustered index 
declare @index_name sysname = '', @is_clustered bit, @column_name sysname, @is_primary bit, @column_count smallint,@type_desc sysname, 
@sql varchar(2000)

select top(1) @index_name = index_name, @column_name = column_name, @column_count = column_count, @is_primary = is_primary_key from #index_details 
where type_desc = 'Clustered'

if(@column_name <> 'event_timestamp')
begin
	if(@is_primary = 1)
		set @sql = 'alter table dbo.event_audit_mstr drop constraint [' + @index_name +']'
	else
		set @sql = 'drop index [' + @index_name +  '] on dbo.event_audit_mstr'
End

if(@sql != '')
Begin
	Exec(@sql)
	Insert Into ExecutionStatus Values(@sql,@@ERROR,CURRENT_TIMESTAMP)
End

Set @sql = ''
delete from #index_details where index_name = @index_name
		
GO
-------------------------------------------------Creating Clustered Index on event_timestamp
Declare @sql varchar(2000);
If Not Exists(Select 1 from sys.indexes where object_id = object_id('event_audit_mstr') And type_desc = 'Clustered')		
Begin
	set @sql = 'create clustered index cix_event_audit_mstr on dbo.event_audit_mstr(event_timestamp desc)'
	exec(@sql)
	Insert Into ExecutionStatus Values(@sql,@@ERROR,CURRENT_TIMESTAMP)		
End	
GO
-------------------------------------------------Creating nonclusterd Primary Key on event_id
Declare @sql varchar(2000);
If Not Exists(Select 1 from sys.indexes where object_id = object_id('event_audit_mstr')
	And type_desc = 'nonclustered' and is_primary_key =1)		
Begin
	set @sql = 'alter table dbo.event_audit_mstr add constraint pk_event_audit_mstr primary key nonclustered (event_id)'
	exec(@sql)
	Insert Into ExecutionStatus Values(@sql,@@ERROR,CURRENT_TIMESTAMP)	
End	
GO

-------------------------------------------------create 3 other nonclustered index
declare @index_name sysname = '', @is_clustered bit, @column_name sysname, @is_primary bit, @column_count smallint,@type_desc sysname, @sql varchar(2000)

insert into #index_details
select t1.indexname, t1.type_desc, t1.colname, 0,t1.is_primary_key 
from
	(
	select i.object_id, i.index_id, indexname = i.name, colName = c.name,type_desc = i.type_desc , i.is_primary_key
	from sys.indexes i
	inner join sys.index_columns ic
		on ic.object_id = i.object_id
		and ic.index_id = i.index_id
	join sys.columns c 
		on c.object_id = ic.object_id
		and c.column_id = ic.column_id
	where i.object_id = object_id('event_audit_mstr')
	and ic.key_ordinal = 1
)t1


IF NOT EXISTS (select 1 from #index_details where column_name = 'person_id') 
begin
	CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_person_id]  ON [event_audit_mstr] (person_id)
	Insert Into ExecutionStatus Values('CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_person_id]  ON [event_audit_mstr] (person_id)',@@ERROR,CURRENT_TIMESTAMP)
end
GO

IF NOT EXISTS (select 1 from #index_details where column_name = 'created_by') 
begin
	CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_created_by] ON [event_audit_mstr] (created_by)
	Insert Into ExecutionStatus Values('CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_created_by] ON [event_audit_mstr] (created_by)',@@ERROR,CURRENT_TIMESTAMP)
end
GO	

IF NOT EXISTS (select 1 from #index_details where column_name = 'Action_id') 
begin
	CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_Action_id] ON  [event_audit_mstr] (Action_id)
	Insert Into ExecutionStatus Values('CREATE NONCLUSTERED INDEX [nix_event_audit_mstr_Action_id] ON  [event_audit_mstr] (Action_id)',@@ERROR,CURRENT_TIMESTAMP)
end
GO	
------------scpturing spaceused information
Declare @name NVARCHAR(128), @rows NVARCHAR(128), @reserved NVARCHAR(128), @data NVARCHAR(128), @index_size NVARCHAR(128), @unused NVARCHAR(128), 
@SpaceUsed NVARCHAR(500)
INSERT INTO #tbl_sp_spaceused ( name, rows, reserved, data, index_size, unused )
EXEC sp_spaceused event_audit_mstr
select @name = name, @rows = rows, @reserved = reserved, @data = data, @index_size = index_size, @unused = unused from #tbl_sp_spaceused
Set @SpaceUsed =  'name = '+ @name + ' , rows = ' + @rows + ', reserved = ' + @reserved+ ', data = ' +@data + ' , index_size = ' + @index_size + ', unused = ' + @unused;
Insert Into ExecutionStatus Values(@SpaceUsed,-99,CURRENT_TIMESTAMP)

Drop table #index_details
Drop table #tbl_sp_spaceused

