Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reverse engineering alias data types - Part 5

By Yakov Shlafman,

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:

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

Resources:

up_AlterAdt.sql | ufn_helpindex.sql | Create set of tables used in Part 5 of article.sql
Total article views: 1148 | Views in the last 30 days: 72
 
Related Articles
FORUM

dynamically creating a tablename in tsql

dynamically creating a tablename in tsql

FORUM

Rename TableName To Datetime

Rename TableName To Year

FORUM

Tips on creating indexes

Tips on creating indexes

FORUM

Dynamically Creating Indexes

Dynamically Creating Indexes

FORUM

Creating an index

Creating an index

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones