Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3807
  • Last Modified:

change column size in SQL Server

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
ralph_rea
Asked:
ralph_rea
  • 20
  • 7
  • 5
  • +3
1 Solution
 
chapmandewCommented:
first things first, do you want to increase them to 50?

alter table tablename
alter column eq_id char(50)
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
ralph_reaAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ralph_reaAuthor Commented:
How can I query information schema for tables that had column eq_id ?
0
 
chapmandewCommented:
select * from information_schema.columns
where name = 'eq_id'
0
 
RiteshShahCommented:
if you are increasing limit of your columns than there shouldn't be any error or problem related to PK, constraint and index.
0
 
ralph_reaAuthor Commented:
In attach the result of the query:
select *
from information_schema.columns
where column_name = 'eq_id'
eqid.xls
0
 
chapmandewCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
chapmandewCommented:
I was going to do that, but got lazy and took the Excel approach. :)
0
 
Kevin CrossChief Technology OfficerCommented:
I was impressed. :)
Figured you just used a formula to do it in Excel.
0
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
RiteshShahCommented:
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
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
Have you tested your script on my sql server db (MsSql_Schema.MDF)?
0
 
ralph_reaAuthor Commented:
Maybe I have to first disable all foreign key NOT constraint?
0
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
chapmandewCommented:
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
 
ralph_reaAuthor Commented:
Maybe I have to first disable or remove all indexes (primary key)?
0
 
RiteshShahCommented:
I guess disabling foreign key should work, are you facing any error while increasing limit of the fields with PK?
0
 
ralph_reaAuthor Commented:
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
 
RiteshShahCommented:
yes you can use that,
0
 
RiteshShahCommented:


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
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
RiteshShahCommented:
have you disable PK and FK both?
0
 
ralph_reaAuthor Commented:
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
 
RiteshShahCommented:
before disable PK and FK, try using

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

so that constraint will not be checked.
0
 
ralph_reaAuthor Commented:
Can someone test these scripts on my sql server db (MsSql_Schema.MDF)?
MsSql-Schema.zip
0
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
ralph_reaAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
weissmanCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 20
  • 7
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now