Post reply

Removing fields from a Table

  • February 16, 2005 at 4:54 am

    #85094

    A number of the fields in one of the Tables in our Data Warehouse are due to be deleted in a week or so and I have been tasked with identifying possible problems that this may cause.

    I am aiming to do the following and would welcome any further advice/ideas:

    1. Display the dependencies via EM of the Table that the fields are to be deleted from
    2. Check dependencies shown from step1 (i.e. Stored Procs, Views, etc.)
    3. Manually check all DTS packages to identify whether any of the soon to be deleted fields are reported on.

     

    A couple of developers have been tasked with identifying potential problems with applications using this table.

    Is there anything else I need to check or be aware of ?

     

    Regards

     

    Carl

     

  • February 16, 2005 at 5:25 am

    #542502

    There is a thrid party tool where you can search whether that field is used by other sps or views called SQL Digger

    Search the internaet this is a free tool




    My Blog: http://dineshasanka.spaces.live.com/

  • February 16, 2005 at 5:28 am

    #542503

    Thanks for that.

     

    Is there any quick way to check DTS apckages or will I have to check each package maunally?

     

     

  • February 16, 2005 at 5:30 am

    #542504

    That is unkown to me. I will try and let you know




    My Blog: http://dineshasanka.spaces.live.com/

  • February 16, 2005 at 10:06 am

    #542591

    Carl,

    I don't know of any way to scan DTS packages and detect the use of a particular column.  I think you're stuck with looking at them manually.

    Greg

    Greg

  • February 16, 2005 at 9:27 pm

    #542693

    my firstfeeling was also the same?. but there can be some tools.




    My Blog: http://dineshasanka.spaces.live.com/

  • February 17, 2005 at 12:10 am

    #542708

    When I need to delete columns I use the scripts of the database to do this - removing the column definitions and then testing a database build using DB Ghost (http://www.dbghost.com). That way I know if I will break anything - IE: any other objects that still reference the non-existant column will now fail the build and I've quickly identified what else needs to change in order to create a perfect release. Using the dependacies facilities is not reliable as dependancy information may not be added due to Deferred Name Resolution and Compilation.

  • February 17, 2005 at 2:48 am

    #542743

    Yes, you have third party tools to check dependencies. Some ETL tools have impact analysis capabilities if you use them from end to end.

    But the best course of action for you would be :

    1. Create a VB application that uses DSO to check on dependencies or generate a script for the whole database and use a good editor to check for the columns you are removing.

    2. for the DTS dependencies, you best bet would be to save the DTS package as a VB module, open the VB file and you will have source code that you can scan to find any references to your columns.

    HABIB.

     

    Kindest Regards,

    Habib Zmerli (MVP)

  • February 17, 2005 at 2:56 am

    #542747

    there's no guareentee that your dependancy information will be correct due to SQL Servers capability of Deferred Name Resolution and Compilation. Text searches will can also be inaccurate.

    Building from source code is accurate 100% of the time and to me that's what counts.

     

     

    regards,

    Mark Baekdal

    http://www.dbghost.com

    http://www.innovartis.co.uk

    +44 (0)208 241 1762

    Database change management for SQL Server

     

     

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply