In this artilce we are going to see how to change ADT linked to column(s) that are part of index.

Let's open the kitchen and see how an article like this one can be written.

Other articles in this series:

• Part 1
• Part 2
• Part 3/a>
• Part 4

Step one

Read all you can about indexes. Recall what you already know. I found that I can create four types of

indexes:

1. Unique clustered index
2. Unique nonclustered index
3. Clustered index
4. Nonclustered index

Step two

I created all four types of indexes. I have includede a with examples in the Resources section below:

'Create set of tables used in Part 5 of article.sql'

Step three

How to view information about created indexes? I recalled that there is a system stored procedure -

sp_helpindex. Here is an example of getting information about indexes in the table

dbo.tblPlanBillEvent

sp_helpindex 'tblPlanBillEvent'

Step four


I analyzed the results from stored procedure sp_helpindex. I kept in front of me the script that I
wrote to create indexes. I thought that I need to change the output of sp_helpindex to get results in
the form I can use to generate 'create and drop index'  statements.


Step five


I opened Enterprise Manager and copied stored procedure sp_helpindex source code. I changed stored
procedure into a table function dbo.ufn_helpindex. By the way, I know that system stored procedures
and functions were written by best professionals in the field. I use their scripts as a study guide.


Here is how to get index information using new table function dbo.ufn_helpindex:

declare @TableName nvarchar(776)
set @TableName = 'tblPlanBillEvent'
select * from dbo.ufn_helpindex(@TableName)
Step six
Create a sql statement to generate create and drop index statements to be used later. In user defined
stored procedure dbo.up_AlterADT I will have logic to dynamically drop and build indexes.
declare @TableName nvarchar(776)
set @TableName = 'tblPlanBillEvent'
-- generate create index statement
select
'CREATE '
+ UniqueProperty  + ' '
+ ClusterProperty + ' INDEX '
+ Index_Name + ' ON ' + 'dbo.'
+ @TableName + '('
+ index_keys + ')'
+ case when IgnoreDupKey is not null then ' ' + IgnoreDupKey else ' ' end
from dbo.ufn_helpindex(@TableName)
declare @TableName nvarchar(776)
set @TableName = 'tblTestEmployee'
-- generate drop index statement
select
'drop index dbo.'
+ @TableName + '.'
+ Index_Name
from dbo.ufn_helpindex(@TableName)
Step seven
I should identify only tables with columns that used in indexes and have ADT linked to them. I tried
this:
select so.[name], si.[name]
from sysobjects so inner join sysindexes si
on so.id = si.id
and so.xtype = 'U'
I am not satisfied because it returns constraints(PK, UQ) and I do not know how to filter them out. I analyzed the contest of sysindexes:
select *
from sysindexes
where (name like 'UC%' or name like 'UI%') -- this is when good naming standards help
To filter out constraints I add - status in ( 2, 16403 )
select * from sysindexes
where (name like 'UC%' or name like 'UI%')
and status in ( 2, 16403 )
I read forums and found this: sysindex.status. The domain values for this column are:
2 = unique index,
16 = clustered index,
64 = index allows duplicate rows,
2048 = the index is used to enforce the Primary Key constraint,
4096 = the index is used to enforce the Unique constraint.
I continued to read forums and found this:
select [name]
from sysobjects
where objectproperty(id,'TableHasNonclustIndex') = 1
I made some changes and got result I like. The query should return all user defined tables that have
indexes.
select so.name
from sysobjects so
where objectproperty(so.id,'IsIndexed') = 1
and  (  objectproperty(so.id, 'IsUserTable') = 1
     or objectproperty(so.id, 'IsUserView')  = 1)
Here is the query that will return tables with columns that used in indexes and have ADT linked to
them.
SELECT  so.[name] AS TableName, c.column_name
from sysindexes idx
inner join sysobjects so ON idx.[id] = so.[id]
inner join INFORMATION_SCHEMA.Columns C
on so.name = c.table_name
where
INDEXPROPERTY( so.[id], idx.[name], 'IsStatistics') = 0
and objectproperty(so.id,'IsIndexed') = 1
and  (  objectproperty(so.id, 'IsUserTable') = 1
     or objectproperty(so.id, 'IsUserView')  = 1)
and idx.status in (2, 16, 64, 16403)
and C.Domain_Name           = 'adt_IntId'
and
(
   C.Column_Name = INDEX_COL( so.[name], idx.indid, 1 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 2 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 3 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 4 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 5 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 6 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 7 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 8 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 9 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 10 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 11 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 12 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 13 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 14 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 15 )
or C.Column_Name = INDEX_COL( so.[name], idx.indid, 16 )
)
Step eight
Modify user defined stored procedure dbo.up_AlterADT to handle changes of ADT that is linked to columns included in indexes.
Step nine - Test
Create test tables using script from file 'Create set of tables used in Part 5 of article.sql'. Here
is how to create a new ADT that will replace existing ADT:
if exists( select * from systypes where [name] = 'adt_BigIntId')
    Print 'adt_BigIntId exists'
else
exec sp_addtype
@typename = 'adt_BigIntId'
,@phystype = 'Bigint'
,@nulltype = 'NOT NULL'
,@owner    = 'dbo'
Here is how to execute stored procedure:
declare @AdtName sysname
set @AdtName        = 'adt_IntId'
declare @NewAdtName sysname
set @NewAdtName     = 'adt_BigIntId'
exec dbo.up_AlterADT @AdtName, @NewAdtName
Here is how to check the results:
SELECT
TABLE_NAME               as TableName
,COLUMN_NAME              as ColumnName
,DATA_TYPE                as DataType
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
Table_Name in ( 'tblTestEmployee', 'tblContractor', 'tblPayroll', 'tblBonus', 'tblDepartment')
and Column_Name in ('DepartmentId', 'ContractId', 'EmployeeId', 'BonusId' )
And another check - rollback all changes and check the results.
declare @AdtName sysname
set @AdtName        = 'adt_BigIntId'
declare @NewAdtName sysname
set @NewAdtName     = 'adt_IntId'
exec dbo.up_AlterADT @AdtName, @NewAdtName
Here is how to check the results:
SELECT
TABLE_NAME               as TableName
,COLUMN_NAME              as ColumnName
,DATA_TYPE                as DataType
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name in ( 'tblTestEmployee', 'tblContractor', 'tblPayroll', 'tblBonus', 'tblDepartment')
and Column_Name in ('DepartmentId', 'ContractId', 'EmployeeId', 'BonusId' )
Credits:
my wife, my kids, my teacher Ilya Petrenko
my coworkers from Viatech Inc.
sqlservercentral Forums
References:
Creating an Index
List of Indexes and indexed columns in a Database by Ramesh Kondaparthy