alter column with PK / index

I have code that builds up a list of all tables requiring a column size
change and then executes the alter table command in dynamic sql via a cursor
.
problem is that sql server will not allow column to grow in size (char
datatype) if there are any PK or indexes on that column.
what is the best method of deploying my change?
do I need to check for all possible indexes upfront, drop, and then alter
table or is there a way to get around this issue. don't really want to have
to drop and recreate indexes due to time involved in rebuilding.
if I have to drop dependencies, can you assist with some code to detect and
build up the indexes again as I will have to do this afterwards.
many thanks.Hi
If this was in source code control your task would be a lot simpler!
Assuming that your PKs/FKs/Indexes are always the same then you could script
them and drop/apply then en-mass or tailor the scripts to do less work. As
you know, this may prolong the process and it is less likely to cope with an
y
anonomalies that may occur.
If you want to do less work start by looking at the sysindexkeys table
and/or INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.KEY_COLUMN_USAGE views
John
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.|||maybe you want to look at some true database change management...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"sysbox27" wrote:

> I have code that builds up a list of all tables requiring a column size
> change and then executes the alter table command in dynamic sql via a curs
or.
> problem is that sql server will not allow column to grow in size (char
> datatype) if there are any PK or indexes on that column.
> what is the best method of deploying my change?
> do I need to check for all possible indexes upfront, drop, and then alter
> table or is there a way to get around this issue. don't really want to hav
e
> to drop and recreate indexes due to time involved in rebuilding.
> if I have to drop dependencies, can you assist with some code to detect an
d
> build up the indexes again as I will have to do this afterwards.
> many thanks.