This is a question not a bug or NFR !
In the below active table [GCMA].[APP].[BillGroupSchedules] , there is a column
that needs to be deleted ,[CutOffID] .
I can see how to drop the column, and Foreign key in the Table Alter, but I
cannot see where I can adjust the index
idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID .
However, I do see that I can seperately alter the index under the index tab..
Therefore, I have concluded that this was/is a design standard for Toad-SS,
where the altering of Indexes is outside the scope of the Table Alter process.
It should be noted that SSMS treats index management differently as well and not
very well either.
Please advise if this set in stone, for I would see advantages to making the
functionality (altering indexes) part of Table Alter.
Granted this may be an item for the idea pond, but prior to that I want to hear
from support and development on the afforementioned.
Hank Freeman
Senior Systems, Database/Data Warehouse Architect
678.414.0090 my cell Primary
Hank.Freeman50 (Skype)
/****** Object: Table [APP].[BillGroupSchedules] Script Date: 3/8/2012 10:03:46
AM ******/
USE [GCMA] ;
GO
IF EXISTS
( SELECT
FROM
sys.objects
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
type IN (N 'U' ))
BEGIN
DROP TABLE [GCMA].[APP].[BillGroupSchedules] ;
END
GO
SET ANSI_NULLS ON ;
GO
SET QUOTED_IDENTIFIER ON ;
GO
IF NOT EXISTS
( SELECT
FROM
sys.objects
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
type IN (N 'U' ))
BEGIN
CREATE TABLE [APP].[BillGroupSchedules](
[BillGroupScheduleID] INT IDENTITY ( 1 , 1 ) NOT NULL
,[BillYear] INT NOT NULL
,[BillMonth] INT NOT NULL
,[BillGroupID] INT NOT NULL
,[BillDate] DATETIME NOT NULL
,[CompanyID] INT NOT NULL
,[ReadingDate] DATETIME NOT NULL
,[CutOffDate] DATETIME NULL
,[CutOffID] INT NOT NULL
,[CreateUserID] INT NOT NULL
,[CreateDate] DATETIME NOT NULL DEFAULT ( getdate ())
,[LastUpdateUserID] INT NOT NULL
,[LastUpdateDate] DATETIME NOT NULL
,[DeleteFlag] BIT NOT NULL DEFAULT (( 0 ))
, CONSTRAINT [FK__BillGroup__BillG__1B4B60F5] FOREIGN KEY
([BillGroupID])
REFERENCES [APP].LuBillGroups
, CONSTRAINT [FK__BillGroup__CutOf__1C3F852E] FOREIGN KEY
([CutOffID])
REFERENCES [APP].LuBillGroups
, CONSTRAINT [PK_BillGroupSchedules_BillGroupScheduleID] PRIMARY KEY
CLUSTERED
([BillGroupScheduleID] ASC )
WITH (PAD_INDEX = OFF
, FILLFACTOR = 90
,IGNORE_DUP_KEY = OFF
,STATISTICS_NORECOMPUTE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
,DATA_COMPRESSION = NONE)
ON [PRIMARY]
, CONSTRAINT [CK__BillGroup__Delet__72A872BE] CHECK
(([DeleteFlag] = ( 0 ) OR
[DeleteFlag] = ( 1 ))))
ON [PRIMARY] ;
END ;
GO
/****** Object: Index
[APP].[BillGroupSchedules].[idx_BillGroupSchedules_BillGroupID] Script Date:
3/8/2012 10:03:46 AM ******/
IF EXISTS
( SELECT
FROM
sys.indexes
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
name = N 'idx_BillGroupSchedules_BillGroupID' )
BEGIN
DROP INDEX [idx_BillGroupSchedules_BillGroupID] ON
[GCMA].[APP].[BillGroupSchedules] ;
END
GO
IF NOT EXISTS
( SELECT
FROM
sys.indexes
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
name = N 'idx_BillGroupSchedules_BillGroupID' )
BEGIN
CREATE NONCLUSTERED INDEX [idx_BillGroupSchedules_BillGroupID]
ON [APP].BillGroupSchedules
WITH (
PAD_INDEX = OFF
, FILLFACTOR = 90
,IGNORE_DUP_KEY = OFF
,STATISTICS_NORECOMPUTE = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
,DATA_COMPRESSION = NONE)
ON [PRIMARY] ;
END
GO
/****** Object: Index
[APP].[BillGroupSchedules].[idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID]
Script Date: 3/8/2012 10:03:46 AM ******/
IF EXISTS
( SELECT
FROM
sys.indexes
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
name = N 'idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID' )
BEGIN
DROP INDEX [idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID] ON
[GCMA].[APP].[BillGroupSchedules] ;
END
GO
IF NOT EXISTS
( SELECT
FROM
sys.indexes
WHERE
object_id = OBJECT_ID (N '[APP].[BillGroupSchedules]' ) AND
name = N 'idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID' )
BEGIN
CREATE NONCLUSTERED INDEX [ idx_BillGroupSchedules_incBGID_Rdate_COdata_CoID ]
ON [APP].BillGroupSchedules
INCLUDE
(
[BillGroupID], [ReadingDate], [CutOffDate], [CutOffID] )
WITH (
PAD_INDEX = OFF
, FILLFACTOR = 100
,IGNORE_DUP_KEY = OFF
,STATISTICS_NORECOMPUTE = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
,DATA_COMPRESSION = NONE)
ON [PRIMARY] ;
END
GO