script to drop dependencies--HOW??

Results 1 to 2 of 2

Thread: script to drop dependencies--HOW??

  1. #1
    Join Date
    Dec 1969

    Default script to drop dependencies--HOW??

    I need help to write a script in MS SQL 7.0 to drop the table dependencies.<BR><BR>Here is my problem.<BR>I created a table t_topic which has a field user_Id which is linked to table t_user through the foreign key u_Id.<BR>Now I realize that I no longer need the field user_Id in t_topic.<BR>I ran the script to Alter table and drop the column. However it doesn&#039;t allow me to drop that field. How do I solve this .<BR>Any help to his learner will be greatly appreciated <BR>

  2. #2
    Join Date
    Dec 1969

    Default I know about Oracle so..

    .. this might be true for SQL Server too, YOU CAN NOT delete the column from a table once it is created, what u can do is this:<BR>Create a "View" from the table getting values from all columns except the column u wanna delete.<BR>&#062;&#062;<BR>Original table has following fields:<BR>table name-"mytable" <BR>fields-name, age, sex, dept, address<BR><BR>Now u want to remove "address" from the table then u do this:<BR>&#062;&#062;<BR>Step 1. <BR>Create view myview as (select name,age,sex,dept from mytable)<BR><BR>Now u have all the fields in the view barring the "address" field.<BR><BR>Step 2.<BR>Drop table mytable<BR><BR>The table structure and all data is gone.<BR><BR>Step 3.<BR>create table mytable as (select * from myview)<BR><BR>Boom u have the table back with the fields u want.<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts