December 10, 2007 at 6:12 am
Hello everybody!
I'm trying to change column type length dynamically existing database
when I try to do it directly with the following query:
ALTER TABLE CALCS_ACTIONS ALTER COLUMN CALCNAME varchar (10)
(previously it was varchar(5))
its give me the following error:
Msg 5074, Level 16, State 1, Line 1
The object 'PK_CALCS_ACTIONS' is dependent on column 'CALCNAME'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CALCNAME failed because one or more objects access this column.
The problem is clear but I want to know if there any SP which do the change cascade via all the layer or maybe somebody have other solution
Thank,
Rachamim
December 10, 2007 at 7:15 am
Perhaps the easiest thing to do is to make the change you want in Management Studio but don't click save. Then click the button at the far left to Generate Change Script. Review the script to see what changes are required and in what order. Then either save your changes directly in Management Studio, run the script as it is given, or edit it to suit your needs.
Or, manually -
Most likely you will need to drop the constraints on affected tables, make your change, then recreate the constraints.
Sincerely,
Dan B
December 10, 2007 at 7:46 am
I would also make sure to perform a backup before you make the change. The script is going to rename the existing table, create the new table strcuture, load the data into and such. On 2 occasions (out of hundreds) somehow the part of moving the data failed and the data was lost except for the fact we made a backup beforehand, so I would be very sure to make a backup in case of the unpexpected occurring.
December 10, 2007 at 7:52 am
Thank you Dan
skrilla99 (12/10/2007)
If I understand you clearly, your suggestion is to run the query in Management Studio, well ,I do so,and it gives me the same error. >
Then click the button at the far left to Generate Change Script.
what exactly the name of the button? Is it exist in the express edition?
>Or, manually Most likely you will need to drop the constraints on affected tables, make your change, then recreate the constraints.
eventually I need to do this via C# code.
but if there is a script I can get somehow,it will be great progress for me
Of course I did a backup.(Thank)
Rachamim
December 10, 2007 at 8:02 am
Below is the script which shows what you should do to drop a column like your's. And it's almost 100% complete. All that should be your concern.
create procedure dbo.SysDropColumnFromTable
@cTableName varchar(100),
@cColumnName varchar(100)
as
set nocount on
set xact_abort on
declare @sql varchar(1000)
if not exists(select * from information_schema.columns where table_name = @cTableName and column_name = @cColumnName)
begin
raiserror('Dejanje je spodeletelo. Polje .', 16, 1, @cColumnName, @cTableName)
return -1
end
begin transaction
-- 1. drop checks on column
while exists(select *
from sysobjects o join sysdepends d on
d.id = o.id
join syscolumns c on
c.colid = d.depnumber and
c.id = o.parent_obj
where o.xtype = 'C' and o.parent_obj = object_id(@cTableName)
and c.name = @cColumnName)
begin
select top 1 @sql = 'alter table ' + @cTableName + ' drop constraint ' + o.name
from sysobjects o join sysdepends d on
d.id = o.id
join syscolumns c on
c.colid = d.depnumber and
c.id = o.parent_obj
where o.xtype = 'C' and o.parent_obj = object_id(@cTableName)
and c.name = @cColumnName
exec (@sql)
end
-- 2. drop defaults on column
select @sql = 'alter table ' + @cTableName + ' drop constraint ' + so.name
from sysobjects so join syscolumns sc on
sc.id = so.parent_obj and
sc.colid = so.uid
where so.parent_obj = object_id(@cTableName) and so.xtype = 'D'
and sc.name = @cColumnName
exec (@sql)
-- 3. drop foreign keys
while exists(select *
from sysforeignkeys fk join syscolumns c2 on
c2.id = fk.rkeyid and
c2.colid = fk.rkey
join sysobjects o on
o.id = fk.constid
where object_id(@cTableName) = fk.rkeyid and c2.name = @cColumnName)
begin
select top 1 @sql = 'alter table ' + object_name(fk.fkeyid) + ' drop constraint ' + o.name
from sysforeignkeys fk join syscolumns c2 on
c2.id = fk.rkeyid and
c2.colid = fk.rkey
join sysobjects o on
o.id = fk.constid
where object_id(@cTableName) = fk.rkeyid and c2.name = @cColumnName
exec (@sql)
end
-- 4. drop primary keys
select @sql = 'alter table ' + @cTableName + ' drop constraint ' + t.constraint_name
from information_schema.key_column_usage u join information_schema.table_constraints t on
t.constraint_name = u.constraint_name
join information_schema.columns c on
c.column_name = u.column_name and
c.table_name = t.table_name
where t.table_name = @cTableName and c.column_name = @cColumnName and t.constraint_type = 'PRIMARY KEY'
order by u.ordinal_position
exec (@sql)
-- 5. drop indexes
while exists(select *
from sysindexkeys si join syscolumns s on
si.id = s.id and
si.colid = s.colid
join sysindexes i on
i.id = si.id and
i.indid = si.indid
where s.id = object_id(@cTableName) and s.name = @cColumnName)
begin
select top 1 @sql = 'drop index ' + @cTableName + '.' + i.name
from sysindexkeys si join syscolumns s on
si.id = s.id and
si.colid = s.colid
join sysindexes i on
i.id = si.id and
i.indid = si.indid
where s.id = object_id(@cTableName) and s.name = @cColumnName
exec (@sql)
end
-- 6. drop column
exec ('alter table ' + @cTableName + ' drop column ' + @cColumnName)
commit
return 1
go
December 10, 2007 at 8:14 am
Thank justin I'll check it out.
Rachamim
December 10, 2007 at 9:52 am
Hello Rachamim,
I had the very same problem and since I had to do it using script, the solution was to create a proc. I submited the proc on the script session, but I here it is, just in case you still need it.
Don't forget to backup your database before running this proc.
Let me know if it works for you.
Luiz.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
/************************************************************************
* Stored Procedure: [LSP_ChangeColumnType]
* Creation Date: 20070806
* Written by: Luiz Barros
*
* Purpose: Alter column type, even if column is part of a constraint, such as a primery key
*
*
************************************************************************/
CREATE PROCEDURE [dbo].[LSP_ChangeColumnType]
@TableVARCHAR(50),
@FieldVARCHAR(50),
@NewDataTypeVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE@INDEXNAMEVARCHAR(100),
@SQLVARCHAR(3000),
@PKFIELDSVARCHAR(300),
@NameVARCHAR(50),
@REQUIREDVARCHAR(10),
@DROPINDEXVARCHAR(2000),
@createindexVARCHAR(2000),
@XTYPEVARCHAR(30),
@IndexName1VARCHAR(100)
-- check if table and column really exists
IF NOT EXISTS (SELECT * FROM sysobjects O, syscolumns C WHERE C.ID=O.ID AND O.NAME=@TABLE and O.XTYPE='U' and C.Name=@Field)
RETURN
DECLAREC CURSOR FOR
SELECTI.NAME,C.Name,P.XTYPE -- this will select indexes that use the column which we want to alter
FROMsysobjects O
INNER JOIN syscolumns C ON C.ID=O.ID
INNER JOIN sysindexes I ON I.ID=O.ID
INNER JOIN sysindexkeys IK ON IK.ID=O.ID AND IK.IndID=I.IndID AND IK.ColID=C.ColID
LEFT OUTER JOIN sysobjects P ON P.parent_obj=O.ID AND I.Name=P.Name AND P.XTYPE='PK'
WHEREO.Name=@Table
AND I.NAME NOT LIKE '_WA_Sys_%'
AND I.NAME IN (SELECTI1.NAME
FROMsysobjects O1
INNER JOIN syscolumns C1 ON C1.ID=O1.ID
INNER JOIN sysindexes I1 ON I1.ID=O1.ID
INNER JOIN sysindexkeys IK1 ON IK1.ID=O1.ID AND IK1.IndID=I1.IndID AND IK1.ColID=C1.ColID
WHEREO1.NAME=@TABLE AND C1.NAME=@FIELD)
ORDER BYIK.IndID,IK.keyno
OPENC
FETCHNEXT FROM C INTO @IndexName,@Name,@XTYPE
SET @DROPINDEX= ''
SET @createindex= ''
WHILE @@FETCH_STATUS = 0 BEGIN
IF @XTYPE='PK' BEGIN
SET @DROPINDEX = @DROPINDEX + ' ALTER TABLE '+@TABLE+' DROP CONSTRAINT '+@IndexName
SET @createindex = @createindex + ' Alter Table '+@TABLE +' Add Constraint '+@INDEXNAME+' PRIMARY KEY CLUSTERED ('
END ELSE IF EXISTS(SELECT * FROM SYS_INDICES WHERE TABELA=@TABLE AND @TABLE+'_'+NOME=@INDEXNAME AND UNICO='S') BEGIN
SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
SET @createindex = @createindex + ' CREATE UNIQUE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
END ELSE BEGIN
SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
SET @createindex = @createindex + ' CREATE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
END
SET @IndexName1 = @IndexName
SET @PKFIELDS = ''
WHILE @@FETCH_STATUS = 0 AND @IndexName1 = @IndexName BEGIN
SET @PKFIELDS = @PKFIELDS + @Name + ','
FETCH NEXT FROM C INTO @IndexName,@Name,@XTYPE
END
SET @createindex = @createindex + LEFT(@PKFIELDS, LEN(@PKFIELDS)-1) + ')'
END
CLOSE C DEALLOCATE C
IF EXISTS (SELECT * FROM sysobjects O, syscolumns C WHERE C.ID=O.ID AND O.Name=@Table AND C.NAME=@FIELD AND C.isnullable=0) -- is a required field?
SET @REQUIRED = ' NOT NULL'
ELSE
SET @REQUIRED = ''
SET @SQL = @DROPINDEX + ' ALTER TABLE '+@TABLE +' ALTER COLUMN '+@FIELD +' '+@NewDataType+' '+ @REQUIRED + @createindex
EXEC(@SQL)
PRINT @Table+' - '+@Field
END
December 11, 2007 at 7:43 am
Thank you Luiz its work great!:)
December 12, 2007 at 8:49 am
skrilla99 (12/10/2007)
Perhaps the easiest thing to do is to make the change you want in Management Studio but don't click save. Then click the button at the far left to Generate Change Script. Review the script to see what changes are required and in what order. Then either save your changes directly in Management Studio, run the script as it is given, or edit it to suit your needs.
Do you mean go to design view to change ? I did that and did not see Generate Change Script.
Thx.
December 12, 2007 at 9:36 am
Hey Frances,
Yes, from the design window, the leftmost icon on the design toolbar should be the Save Changes Script. It looks like a little disk with a piece of paper.
Sincerely,
Dan B
December 12, 2007 at 9:37 am
... it will be grayed out before you make any changes, but the tool-tip should still pop up on hover.
-Dan B
December 16, 2007 at 9:17 am
Another problem occur while running this SP:
for some table in the database,I got the following error:
Invalid object name 'SYS_INDICES'.
1.what is 'SYS_INDICES'
2.what cause the problem?
(I was convert this SP to a long query because I run it from a code)
Rachamim
December 17, 2007 at 8:20 am
oops. My bad.
These is not such a table in regular databases. Just in mine. That's because I did this proc for personal use.
Anyway, This conditions is just to know if the index is UNIQUE. Observe:
IF @XTYPE='PK' BEGIN -- First we verify if the index is a primary key
SET @DROPINDEX = @DROPINDEX + ' ALTER TABLE '+@TABLE+' DROP CONSTRAINT '+@IndexName
SET @createindex = @createindex + ' Alter Table '+@TABLE +' Add Constraint '+@INDEXNAME+' PRIMARY KEY CLUSTERED ('
END ELSE IF EXISTS(SELECT * FROM SYS_INDICES WHERE TABELA=@TABLE AND @TABLE+'_'+NOME=@INDEXNAME AND UNICO='S') BEGIN -- if it's not a primery key, we verify if the index is UNIQUE.
SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
SET @createindex = @createindex + ' CREATE UNIQUE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
END ELSE BEGIN
SET @DROPINDEX = @DROPINDEX + ' DROP INDEX '+@TABLE+'.'+@IndexName
SET @createindex = @createindex + ' CREATE INDEX '+@INDEXNAME+' ON '+ @TABLE +'('
END
The problem here is that I don't know how to find out if an index is UNIQUE. Since I had my own personal control, I didn't bother trying to find this. But if you know, just change this condition and it will work.
I'm pretty sure somebody in this forum knows how to find out (using script) if an index is UNIQUE or not.
Can somebody help here?
Luiz.
December 17, 2007 at 9:30 am
This is a great script but to be truly universal or generic, it needs to be enhanced in the if-else section. Right now, it assumes that the primary key is also the clustered index. Usually that is true but not always. And, it needs to handle unique constraints.
Just my two cents,
Joe
December 18, 2007 at 10:13 am
You may try to break the connections between tables before making changes on data type.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply