February 24, 2011 at 9:20 am
I need to write a generic script to change the datatype of any column in it. I want to pass table name and column name only.
This script should check if there is default constraint, null constraint. indexs, primary key constraint on it. If it has the above constraints scripts should drop the constraints first, change the datatyps from int to bigint and recreate the constraints as it was.
Please let me know if any of you have the script ready
i am stuck in checking for indexes portion
thanks
February 24, 2011 at 9:44 am
OR if any one can help me with the script which checks for a index on column.....drops it.... and recreates it.
All i would pass is table name and column name
Thanks
February 24, 2011 at 10:13 am
Please post what you have so far.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 24, 2011 at 10:31 am
--begin tran
----rollback
if exists ( SELECT 1
FROM sys.objects o
join sys.columns c on c.object_id = o.object_id
WHERE o.name = N'MYTABLE'
AND type = 'U'
and c.name = 'MYCOLUMN')
begin
declare @default varchar(1000)
declare @comment varchar(500)
select @default = dc.name
from sys.default_constraints dc
join sys.objects o on dc.parent_object_id = o.object_id
join sys.columns c on dc.parent_column_id = c.column_id and c.object_id = o.object_id
where o.name = 'MYTABLE'
and c.name = 'MYCOLUMN'
if(@default is not null)
begin
begin tran
exec('alter table dbo.MYTABLE drop constraint ' + @default)
--update dbo.MYTABLE set MYCOLUMN = CONVERT (varbinary(max),MYCOLUMN,2)
alter table dbo.MYTABLE alter column MYCOLUMN VarBinary(Max) null
exec ('alter table MYTABLE add constraint ' + @default + ' default(0) for MYCOLUMN')
IF @@error <> 0
Begin
SELECT @comment = 'Error while converting datatype of MYCOLUMN column.'
GOTO done
end
commit tran
end
else
begin
alter table dbo.MYTABLE alter column MYCOLUMN VarBinary(Max) not null
exec ('alter table MYTABLE add constraint default_Constraint ' + ' default(0) for MYCOLUMN')
IF @@error <> 0
Begin
SELECT @comment = 'Error while converting datatype of MYCOLUMN column.'
GOTO done
end
end
end
RETURN
done:
RAISERROR(@comment, 16, 1)
rollback tran
February 24, 2011 at 11:07 am
Your OP says you're trying to alter columns from INT to BIGINT but your script is altering columns to VARBINARY(MAX). What exactly are you trying to do?
In any case you'll need to consider a lot of things:
- default constraints
- indexes (note that a column can participate in more than one index)
- primary keys
- foreign keys
- rules (hopefully you're not using these)
- computed columns
- identity columns
- I am probably missing some...
Your dynamic proc idea is viable however handling all the potential schema dependencies will cause it to become quite complex in a hurry. You will be generating the necessary DDL by querying the system-views and using lots of string manipulation to build your commands by hand. You will be a system-view and string-manipulation ninja by the end, which will be a good outcome, however a huge amount of this tedious work has already been done before by Microsoft developers and it is readily available for us to leverage.
If you have some .NET experience I would recommend looking at SQL Server SMO (Server Management Objects). Using SMO will ease the burden of generating DDL for dependent schema objects before you have to drop them, change the column data type, and then re-apply them. SMO are the same objects used by SSMS when we ask it for DDL scripts. For example when you right-click a table in SSMS Object Explorer and choose "Script table as... > Create > ..." SMO is being used to generate the DDL under the covers. SMO can be a bit on the slow side for some things but it lets you easily traverse your database objects in a familiar hierarchical object model and leverage the built-in DDL scripting functionality. I am OK with SMO being a little slow since when I need SMO it's always for a one-off scripting effort like this.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply