Solved

change column size in SQL Server

Posted on 2009-05-06
39
3,694 Views
Last Modified: 2012-05-06
Hi,
I'd like to to increase the size of the 2 columns EQ_ID and EQ_STD on my DB SQL Server 2005.
EQ_ID char(50)
EQ_STD char (50)
How can I create a script to change the size of this columns on all my DB?
Can I have any problems if  change this size?
In attach my SQL Server db (I modify MsSql_Schema.MDF to MsSql_Schema.txt)


Thanks in advance!

Raf
MsSql-Schema.zip
0
Comment
Question by:ralph_rea
  • 20
  • 7
  • 5
  • +3
39 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24313789
first things first, do you want to increase them to 50?

alter table tablename
alter column eq_id char(50)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24313856
You would query information schema for tables that had column eq_id for example and then using dynamic sql build sql statement chapmandew posted for each.

EXEC ('alter table [' + @table + '] alter column [' + @column + '] char(50)')
0
 

Author Comment

by:ralph_rea
ID: 24313862
yes I want to increase them to 50 on all columns eq_id and eq_std of my DB

Can I have any problems if  change this size (primary key, constraints, index......)?
0
 

Author Comment

by:ralph_rea
ID: 24313877
How can I query information schema for tables that had column eq_id ?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24313899
select * from information_schema.columns
where name = 'eq_id'
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24313902
if you are increasing limit of your columns than there shouldn't be any error or problem related to PK, constraint and index.
0
 

Author Comment

by:ralph_rea
ID: 24314059
In attach the result of the query:
select *
from information_schema.columns
where column_name = 'eq_id'
eqid.xls
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24314102
alter table       hwr      alter column eq_id char(50)
alter table       eq_reserve      alter column eq_id char(50)
alter table       hwrsum      alter column eq_id char(50)
alter table       wr      alter column eq_id char(50)
alter table       rm_resource_std      alter column eq_id char(50)
alter table       eq_sched      alter column eq_id char(50)
alter table       eq_trial      alter column eq_id char(50)
alter table       pmpsum      alter column eq_id char(50)
alter table       eqport      alter column eq_id char(50)
alter table       pms      alter column eq_id char(50)
alter table       helpdesk_sla_request      alter column eq_id char(50)
alter table       pmforecast_tr      alter column eq_id char(50)
alter table       eqprph      alter column eq_id char(50)
alter table       softinv      alter column eq_id char(50)
alter table       hactivity_log      alter column eq_id char(50)
alter table       mo_eq      alter column eq_id char(50)
alter table       eq_audit      alter column eq_id char(50)
alter table       activity_log      alter column eq_id char(50)
alter table       wrhwr      alter column eq_id char(50)
alter table       wrview      alter column eq_id char(50)
alter table       eq_bar_code_list      alter column eq_id char(50)
alter table       activity_logview      alter column eq_id char(50)
alter table       hactivity_logmonth      alter column eq_id char(50)
alter table       hwr_month      alter column eq_id char(50)
alter table       eq_compinvsur      alter column eq_id char(50)
alter table       activity_log_hactivity_log      alter column eq_id char(50)
alter table       resources      alter column eq_id char(50)
alter table       card      alter column eq_id char(50)
alter table       eq_compinvtrial      alter column eq_id char(50)
alter table       ep      alter column eq_id char(50)
alter table       eq      alter column eq_id char(50)
alter table       eq_dep      alter column eq_id char(50)
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 24314190
Wow.  You could just do what Tim did OR to be able to run one script without the manual step in between use dynamic sql as I mentioned.
declare @sql nvarchar(max)
 

select @sql = isnull(@sql+';','')

+ 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] char(50)

from information_schema.columns

where column_name = 'eq_id'

and character_maximum_length < 50
 

exec(@sql)

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24314199
Type-o...
declare @sql nvarchar(max)

 

select @sql = isnull(@sql+';','')

+ 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] char(50)'

from information_schema.columns

where column_name = 'eq_id'

and character_maximum_length < 50

 

exec(@sql)

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24314224
I was going to do that, but got lazy and took the Excel approach. :)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24314375
I was impressed. :)
Figured you just used a formula to do it in Excel.
0
 

Author Comment

by:ralph_rea
ID: 24315093
I run this script:
declare @sql nvarchar(max)
select @sql = isnull(@sql+';','')
+ 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] char(50)'
from information_schema.columns
where column_name = 'eq_id'
and character_maximum_length < 50
exec(@sql)

but I get these errors:
Msg 5074, Level 16, State 1, Line 1
The object 'eq_reserve_eq_id' is dependent on column 'eq_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN eq_id failed because one or more objects access this column.

hwr table changed to char(50)

Have you any idea?
0
 

Author Comment

by:ralph_rea
ID: 24315519
If I run:
alter table [afm].[eq] alter column [eq_id] char(40)

I get:

Msg 5074, Level 16, State 1, Line 1
The object 'eq_PK' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_compinvtrial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_bar_code_list_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'ep_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'helpdesk_sla_request_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'wr_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'resources_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'rm_resource_std_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'softinv_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'card_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'pms_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'activity_log_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'mo_eq_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqprph_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqport_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_trial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_sched_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_reserve_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_dep_eq_id' is dependent on column 'eq_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN eq_id failed because one or more objects access this column.
0
 

Author Comment

by:ralph_rea
ID: 24315625
I tried to modify the column eq_reserve.eq_id manually from TOAD but I get this error:

Column 'afm.eq.eq_id' is not the same length as referencing column 'eq_reserve.eq_id' in foreign key 'eq_reserve_eq_id'. Columns participating in a foreign key relationship must be defined with the same length.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

Maybe I have to first disable all constraints?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24315802
you can disable all constraint from all table with simple script.


EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
--run your alter script and then run below command to make your constraint active again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
0
 

Author Comment

by:ralph_rea
ID: 24320820
here the steps that I run:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Command(s) completed successfully.

alter table [afm].[hwr] alter column [eq_id] char(50)
Command(s) completed successfully.

alter table [afm].[hwrsum] alter column [eq_id] char(50)
Command(s) completed successfully.

alter table [afm].[hactivity_log] alter column [eq_id] char(50)
Command(s) completed successfully.

alter table [afm].[eq] alter column [eq_id] char(50)
Msg 5074, Level 16, State 1, Line 1
The object 'eq_PK' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_compinvtrial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_bar_code_list_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'ep_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'helpdesk_sla_request_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'wr_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'resources_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'rm_resource_std_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'softinv_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'card_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'pms_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'activity_log_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'mo_eq_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqprph_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqport_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_trial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_sched_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_reserve_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_dep_eq_id' is dependent on column 'eq_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN eq_id failed because one or more objects access this column.

I Have always same error.

Have someone any idea?
0
 

Author Comment

by:ralph_rea
ID: 24320855
Have you tested your script on my sql server db (MsSql_Schema.MDF)?
0
 

Author Comment

by:ralph_rea
ID: 24320897
Maybe I have to first disable all foreign key NOT constraint?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:ralph_rea
ID: 24321028
here all foreign keys:

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
0
 

Author Comment

by:ralph_rea
ID: 24321265
I disabled all foreign key constraints with this script:

select 'alter table afm.[' + table_name + '] nocheck constraint [' + constraint_name + ']' as fk_disable
from (
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
) a

but I have again any problems

For example, from TOAD If I tried to change eq.eq_id to char(50) I get this error:

The constraint 'eq_PK' is being referenced by table 'resources', foreign key constraint 'resources_eq_id'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not drop constraint. See previous errors.

If I tried to change resources.eq_id to char(50) I get this error:
Column 'afm.eq.eq_id' is not the same length as referencing column 'resources.eq_id' in foreign key 'resources_eq_id'. Columns participating in a foreign key relationship must be defined with the same length.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.

seem impossible to change eq_id column to char(50)

Help me please!!!!!
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24321614
Hello,
Here is an article that I started working on but haven't quite finished yet.  HOWEVER, it does the scripting for dropping and recreating ALL foreign keys on your system.

http://sqlservernation.com/blogs/admin/archive/2009/03/24/how-to-disable-and-enable-foreign-keys.aspx
0
 

Author Comment

by:ralph_rea
ID: 24323692
Maybe I have to first disable or remove all indexes (primary key)?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323700
I guess disabling foreign key should work, are you facing any error while increasing limit of the fields with PK?
0
 

Author Comment

by:ralph_rea
ID: 24323704
disable all indexes
exec sp_MSforeachtable ALTER INDEX ALL ON ? DISABLE

 ENABLE ALL INDEXES
exec sp_MSforeachtable ALTER INDEX ALL ON ? REBUILD

any idea????
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323712
yes you can use that,
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323721


EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? DISABLE'
--run your alter script and then run below command to make your constraint active again
EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'
0
 

Author Comment

by:ralph_rea
ID: 24323734
RiteshShah I think that this error:
The constraint 'eq_PK' is being referenced by table 'resources', foreign key constraint 'resources_eq_id'

means that PK "eq_PK" is active on eq table
0
 

Author Comment

by:ralph_rea
ID: 24323770
again error:

alter table [afm].[eq] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'eq' because its clustered index 'eq_PK' is disabled.


alter table [afm].[resources] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'resources' because its clustered index 'resources_PK' is disabled.

any idea?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323783
have you disable PK and FK both?
0
 

Author Comment

by:ralph_rea
ID: 24323937
below my single steps:
select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] char(50)'
from information_schema.columns
where column_name = 'eq_id'
and character_maximum_length < 50

I exported in a text file this query
--DISABLE FK
select 'alter table afm.[' + table_name + '] nocheck constraint [' + constraint_name + ']' as fk_disable
from (
SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
) a
I exported in a text file this query (1000 rows) and run (Command(s) completed successfully)

--disable PK
EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? DISABLE'
Warning: Foreign key 'serv_serv_std' on table 'serv' referencing table 'servstd' was disabled as a result of disabling the index 'servstd_PK'.
Warning: Foreign key 'hwrpt_wr_id' on table 'hwrpt' referencing table 'hwr' was disabled as a result of disabling the index 'hwrpt_PK'.
Warning: Foreign key 'eq_po_line_id' on table 'eq' referencing table 'po_line' was disabled as a result of disabling the index 'po_line_PK'.
Warning: Foreign key 'ta_po_line_id' on table 'ta' referencing table 'po_line' was disabled as a result of disabling the index 'po_line_PK'.
..........................................................................................................................................................................................................................
..........................................................................................................................................................................................................................


NOW I RUN THIS SCRIPT:
alter table [afm].[hwr] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'hwr' because its clustered index 'hwr_PK' is disabled.

alter table [afm].[eq] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'eq' because its clustered index 'eq_PK' is disabled.

alter table [afm].[eq_reserve] alter column [eq_id] char(50)
alter table [afm].[hwrsum] alter column [eq_id] char(50)
alter table [afm].[wr] alter column [eq_id] char(50)
alter table [afm].[rm_resource_std] alter column [eq_id] char(50)
alter table [afm].[eq_sched] alter column [eq_id] char(50)
alter table [afm].[eq_trial] alter column [eq_id] char(50)
alter table [afm].[pmpsum] alter column [eq_id] char(50)
alter table [afm].[eqport] alter column [eq_id] char(50)
alter table [afm].[pms] alter column [eq_id] char(50)
alter table [afm].[helpdesk_sla_request] alter column [eq_id] char(50)
alter table [afm].[pmforecast_tr] alter column [eq_id] char(50)
alter table [afm].[eqprph] alter column [eq_id] char(50)
alter table [afm].[softinv] alter column [eq_id] char(50)
alter table [afm].[hactivity_log] alter column [eq_id] char(50)
alter table [afm].[mo_eq] alter column [eq_id] char(50)
alter table [afm].[eq_audit] alter column [eq_id] char(50)
alter table [afm].[activity_log] alter column [eq_id] char(50)
alter table [afm].[eq_bar_code_list] alter column [eq_id] char(50)
alter table [afm].[eq_compinvsur] alter column [eq_id] char(50)
alter table [afm].[resources] alter column [eq_id] char(50)
alter table [afm].[card] alter column [eq_id] char(50)
alter table [afm].[eq_compinvtrial] alter column [eq_id] char(50)
alter table [afm].[ep] alter column [eq_id] char(50)
alter table [afm].[eq_dep] alter column [eq_id] char(50)
alter table [afm].[wrhwr] alter column [eq_id] char(50)
alter table [afm].[wrview] alter column [eq_id] char(50)

What I wrong???

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24323954
before disable PK and FK, try using

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

so that constraint will not be checked.
0
 

Author Comment

by:ralph_rea
ID: 24323968
Can someone test these scripts on my sql server db (MsSql_Schema.MDF)?
MsSql-Schema.zip
0
 

Author Comment

by:ralph_rea
ID: 24324009
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? DISABLE'

after I disabled FK, but I get same error:
alter table [afm].[hwr] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'hwr' because its clustered index 'hwr_PK' is disabled.

alter table [afm].[eq] alter column [eq_id] char(50)
Msg 1974, Level 16, State 3, Line 1
Cannot perform the specified operation on table 'eq' because its clustered index 'eq_PK' is disabled.
0
 

Author Comment

by:ralph_rea
ID: 24324031
If I enable PK:
EXEC sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

alter table [afm].[hwr] alter column [eq_id] char(50)
Command(s) completed successfully.


alter table [afm].[eq] alter column [eq_id] char(50)
Msg 5074, Level 16, State 1, Line 1
The object 'eq_PK' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_compinvtrial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_bar_code_list_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'ep_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'wr_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'softinv_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'rm_resource_std_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'resources_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'pms_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'card_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'mo_eq_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'activity_log_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'helpdesk_sla_request_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqprph_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eqport_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_trial_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_sched_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_reserve_eq_id' is dependent on column 'eq_id'.
Msg 5074, Level 16, State 1, Line 1
The object 'eq_dep_eq_id' is dependent on column 'eq_id'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN eq_id failed because one or more objects access this column.



0
 

Author Comment

by:ralph_rea
ID: 24332283
I thought to drop and recreate PK and FK:
--DROP
ALTER TABLE afm.eq_compinvtrial DROP CONSTRAINT eq_compinvtrial_eq_id
ALTER TABLE afm.eq_bar_code_list DROP CONSTRAINT eq_bar_code_list_eq_id
ALTER TABLE afm.ep DROP CONSTRAINT ep_eq_id
ALTER TABLE afm.wr DROP CONSTRAINT wr_eq_id
ALTER TABLE afm.softinv DROP CONSTRAINT softinv_eq_id
ALTER TABLE afm.resources DROP CONSTRAINT resources_eq_id
ALTER TABLE afm.pms DROP CONSTRAINT pms_eq_id
ALTER TABLE afm.card DROP CONSTRAINT card_eq_id
ALTER TABLE afm.mo_eq DROP CONSTRAINT mo_eq_eq_id
ALTER TABLE afm.activity_log DROP CONSTRAINT activity_log_eq_id
ALTER TABLE afm.helpdesk_sla_request DROP CONSTRAINT helpdesk_sla_request_eq_id
ALTER TABLE afm.eqprph DROP CONSTRAINT eqprph_eq_id
ALTER TABLE afm.eqport DROP CONSTRAINT eqport_eq_id
ALTER TABLE afm.eq_trial DROP CONSTRAINT eq_trial_eq_id
ALTER TABLE afm.eq_sched DROP CONSTRAINT eq_sched_eq_id
ALTER TABLE afm.eq_reserve DROP CONSTRAINT eq_reserve_eq_id
ALTER TABLE afm.eq_dep DROP CONSTRAINT eq_dep_eq_id
ALTER TABLE afm.pt DROP CONSTRAINT fk_pt_eq_id
ALTER TABLE afm.rm_resource_std DROP CONSTRAINT rm_resource_std_eq_id

--DROP PRIMARY KEY
ALTER TABLE afm.eq DROP CONSTRAINT eq_PK
ALTER TABLE afm.eq_dep DROP CONSTRAINT eq_dep_PK
ALTER TABLE afm.eq_sched DROP CONSTRAINT eq_sched_PK
ALTER TABLE afm.eq_trial DROP CONSTRAINT eq_trial_PK
ALTER TABLE afm.pmpsum DROP CONSTRAINT pmpsum_PK
ALTER TABLE afm.eqport DROP CONSTRAINT eqport_PK
ALTER TABLE afm.pmforecast_tr DROP CONSTRAINT pmforecast_tr_PK
ALTER TABLE afm.mo_eq DROP CONSTRAINT mo_eq_PK
ALTER TABLE afm.eq_audit DROP CONSTRAINT eq_audit_PK
ALTER TABLE afm.eq_bar_code_list DROP CONSTRAINT eq_bar_code_list_PK
ALTER TABLE afm.eq_compinvsur DROP CONSTRAINT eq_compinvsur_PK
ALTER TABLE afm.ep DROP CONSTRAINT ep_PK

alter table [afm].[hwr] alter column [eq_id] char(50)
alter table [afm].[eq_reserve] alter column [eq_id] char(50)
alter table [afm].[hwrsum] alter column [eq_id] char(50)
alter table [afm].[wr] alter column [eq_id] char(50)
alter table [afm].[rm_resource_std] alter column [eq_id] char(50)
alter table [afm].[pms] alter column [eq_id] char(50)
alter table [afm].[helpdesk_sla_request] alter column [eq_id] char(50)
alter table [afm].[eqprph] alter column [eq_id] char(50)
alter table [afm].[softinv] alter column [eq_id] char(50)
alter table [afm].[hactivity_log] alter column [eq_id] char(50)
alter table [afm].[activity_log] alter column [eq_id] char(50)
alter table [afm].[resources] alter column [eq_id] char(50)
alter table [afm].[card] alter column [eq_id] char(50)
alter table [afm].[eq_compinvtrial] alter column [eq_id] char(50)
alter table [afm].[eq] alter column [eq_id] char(50)
alter table [afm].[eq_dep] alter column [eq_id] char(50)
alter table [afm].[eq_sched] alter column [eq_id] char(50)
alter table [afm].[eq_trial] alter column [eq_id] char(50)
alter table [afm].[pmpsum] alter column [eq_id] char(50)
alter table [afm].[eqport] alter column [eq_id] char(50)
alter table [afm].[pmforecast_tr] alter column [eq_id] char(50)
alter table [afm].[mo_eq] alter column [eq_id] char(50)
alter table [afm].[eq_audit] alter column [eq_id] char(50)
alter table [afm].[eq_bar_code_list] alter column [eq_id] char(50)
alter table [afm].[eq_compinvsur] alter column [eq_id] char(50)
alter table [afm].[ep] alter column [eq_id] char(50)

--ADD PRIMARY KEY
alter table afm.eq alter column eq_id char(50) NOT NULL
ALTER TABLE afm.eq ADD CONSTRAINT eq_PK PRIMARY KEY (eq_id)

alter table afm.eq_dep alter column eq_id char(50) NOT NULL
alter table afm.eq_dep alter column report_id char(50) NOT NULL
ALTER TABLE afm.eq_dep ADD CONSTRAINT eq_dep_PK PRIMARY KEY (eq_id,report_id)

alter table afm.eq_sched alter column eq_id char(50) NOT NULL
alter table afm.eq_sched alter column date_scheduled char(50) NOT NULL
alter table afm.eq_sched alter column schedule_type char(50) NOT NULL
ALTER TABLE afm.eq_sched ADD CONSTRAINT eq_sched_PK PRIMARY KEY (eq_id,date_scheduled,schedule_type)

alter table afm.eq_trial alter column eq_id char(50) NOT NULL
alter table afm.eq_trial alter column layer_name char(50) NOT NULL
ALTER TABLE afm.eq_trial ADD CONSTRAINT eq_trial_PK PRIMARY KEY (eq_id,layer_name)

alter table afm.pmpsum alter column eq_id char(50) NOT NULL
alter table afm.pmpsum alter column date_todo char(50) NOT NULL
alter table afm.pmpsum alter column pmp_id char(50) NOT NULL
alter table afm.pmpsum alter column tr_id char(50) NOT NULL
ALTER TABLE afm.pmpsum ADD CONSTRAINT pmpsum_PK PRIMARY KEY (date_todo,eq_id,pmp_id,tr_id)

alter table afm.eqport alter column eq_id char(50) NOT NULL
alter table afm.eqport alter column port_id char(50) NOT NULL
ALTER TABLE afm.eqport ADD CONSTRAINT eqport_PK PRIMARY KEY (eq_id,port_id)

alter table afm.pmforecast_tr alter column eq_id char(50) NOT NULL
alter table afm.pmforecast_tr alter column pmp_id char(50) NOT NULL
alter table afm.pmforecast_tr alter column tr_id char(50) NOT NULL
ALTER TABLE afm.pmforecast_tr ADD CONSTRAINT pmforecast_tr_PK PRIMARY KEY (eq_id,pmp_id,tr_id)

alter table afm.mo_eq alter column eq_id char(50) NOT NULL
alter table afm.mo_eq alter column mo_id char(50) NOT NULL
ALTER TABLE afm.mo_eq ADD CONSTRAINT mo_eq_PK PRIMARY KEY (eq_id,mo_id)

alter table afm.eq_audit alter column eq_id char(50) NOT NULL
alter table afm.eq_audit alter column survey_id char(50) NOT NULL
ALTER TABLE afm.eq_audit ADD CONSTRAINT eq_audit_PK PRIMARY KEY (eq_id,survey_id)

alter table afm.eq_bar_code_list alter column eq_id char(50) NOT NULL
ALTER TABLE afm.eq_bar_code_list ADD CONSTRAINT eq_bar_code_list_PK PRIMARY KEY (eq_id)

alter table afm.eq_compinvsur alter column eq_id char(50) NOT NULL
ALTER TABLE afm.eq_compinvsur ADD CONSTRAINT eq_compinvsur_PK PRIMARY KEY (eq_id)

alter table afm.ep alter column eq_id char(50) NOT NULL
alter table afm.ep alter column part_id char(50) NOT NULL
ALTER TABLE afm.ep ADD CONSTRAINT ep_PK PRIMARY KEY (eq_id,part_id)

--ADD
ALTER TABLE afm.eq_compinvtrial  ADD CONSTRAINT eq_compinvtrial_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eq_bar_code_list  ADD CONSTRAINT eq_bar_code_list_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.ep  ADD CONSTRAINT ep_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.wr  ADD CONSTRAINT wr_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.softinv  ADD CONSTRAINT softinv_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.resources  ADD CONSTRAINT resources_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.pms  ADD CONSTRAINT pms_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.card  ADD CONSTRAINT card_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.mo_eq  ADD CONSTRAINT mo_eq_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.activity_log  ADD CONSTRAINT activity_log_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.helpdesk_sla_request  ADD CONSTRAINT helpdesk_sla_request_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eqprph  ADD CONSTRAINT eqprph_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eqport  ADD CONSTRAINT eqport_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eq_trial  ADD CONSTRAINT eq_trial_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eq_sched  ADD CONSTRAINT eq_sched_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eq_reserve  ADD CONSTRAINT eq_reserve_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.eq_dep  ADD CONSTRAINT eq_dep_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.pt  ADD CONSTRAINT fk_pt_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)
ALTER TABLE afm.rm_resource_std  ADD CONSTRAINT rm_resource_std_eq_id FOREIGN KEY (eq_id) REFERENCES afm.eq (eq_id)

WHAT DO YOU THINK ABOUT?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24349093
G'Day ralph_rea,

Having a look now but mostly it is what everyone has already said, it is just getting the sequence and every instance covered off.

1) foreign keys
2) indexes
3) change size
4) Primary Keys
5) Other Indexes (always start with Primary key first - the others can use some of the PK information)
6) foreign keys

From what I can see above, you have that (except sequence of 4 and 5).  It is also possible to create other contraints in your tables that can reference other tables like using stored procedures, or triggers. Have you considered those ?

Have you already tried your script ? Whilst long hand, it is sometimes the very best approach and most specific/detailed way of managing these "big" changes.

Would you like me to try it out ?
0
 

Expert Comment

by:weissman
ID: 25319877
hi all,
try this to change the description field in "table_name" table
declare @MSSQL_VERSION nvarchar(255) select @MSSQL_VERSION = cast (serverproperty('productversion') as nvarchar) 

declare @df_name nvarchar(255) 

declare @sql nvarchar(1000)  

if substring(@MSSQL_VERSION,1,1) = '8'  

  select @df_name=d.name from sysobjects t,sysobjects d,syscolumns c 

  where t.id = d.parent_obj and d.xtype='D' and t.xtype='U' and t.name='table_name'  and c.name = 'description' and t.id=c.id  and d.info=c.colid  

else  select @df_name=d.name from sys.tables t join sys.default_constraints d on d.parent_object_id = t.object_id join sys.columns c 

  on c.object_id = t.object_id and c.column_id = d.parent_column_id  where t.name = 'table_name' and c.name = 'description'  

   select @sql='ALTER TABLE table_name DROP CONSTRAINT '+ @df_name exec (@sql) 

alter table table_name alter column description ntext 

ALTER TABLE table_name ADD CONSTRAINT DF__table_name_description default NULL for description

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now