Post reply

Drop All Related Constraints and Indexes For Columns

  • March 29, 2006 at 1:38 pm

    #112571

    I'd like a way to generate a script that generates a script shell for dropping and re-adding indexes and constraints for a specified list of columns in all tables in a given database

     

    Read a column from a specified listing of columns

    For each table the column is part of...

    1. Drop any non-clustered indexes that are defined using the column

    2. Drop any primary key constraints that are defined using the column

    3. Drop any check constraints that are defined on the column

    4. Modify the datatype of the column

    5. Re-add the check constraints on the column

    6. Re-add the primary key constraints on the column

    7. Re-add the non-clustered indexes on the column

    Next table the column is a part of...

    Next column from a specified listing of columns…

     

    I would expect at least the following input values to be passed:

    1. Comma separated list of columns to be modified

    2. Database to be searched

     

    Has anyone done this before?

    Would anyone be willing to offer suggestions as to how this can be done using SQL metadata?

    THANKS in advance for any ideas!

  • March 29, 2006 at 4:28 pm

    #629688

    Topic "System tables" in BOL.

    _____________
    Code for TallyGenerator

  • March 29, 2006 at 4:48 pm

    #629693

    Another suggestion is http://www.sqlservercentral.com/scripts

     

    --------------------
    Colt 45 - the original point and click interface

  • March 29, 2006 at 6:15 pm

    #629700

    Thanks... looking for more specific guideance than search all the scripts on SSC or go to bol.

    Has anyone done this before?

    Can anyone share the best way to navigate the 2000 sys... tables to accomplish this?

  • March 29, 2006 at 6:28 pm

    #629702

    There are only 19 of them:

    sysobjects

    sysindexes

    syscolumns

    systypes

    syscomments

    sysfiles1

    syspermissions

    sysusers

    sysproperties

    sysdepends

    sysreferences

    sysfulltextcatalogs

    sysindexkeys

    sysforeignkeys

    sysmembers

    sysprotects

    sysfulltextnotify

    sysfiles

    sysfilegroups

    _____________
    Code for TallyGenerator

  • March 29, 2006 at 8:15 pm

    #629706

    Really? Duh.

    Sergiy, get your posting count up on another thread... I have reviewed your "look in bol" type responses and they are not welcomed in this thread.

    I welcome professional best practice type discussion.

  • March 29, 2006 at 9:01 pm

    #629707

    Joe

    "Professional best practice" is to review the documentation. If there is something in Books Online that you don't understand then explain what you don't understand and we may be able to clarify things.

    Also, you ask "Has anyone done this before?" yet you're not prepared to look in the script library yourself. Try the "search" link at the top of the page. Again if you find something you don't understand in a script, explain what you don't understand and we may be able to clarify things.

    Responding as you have to Sergiy is not very high on my "How to ask for assitance" list

     

     

    --------------------
    Colt 45 - the original point and click interface

  • March 30, 2006 at 7:11 am

    #629825

    The problem I am framing involves not just knowing which system tables the information is a part of but in what manner one can perform the following:

    1.      Iteration through tables in a database

    2.      Find constraints and indexes whose definition includes the column names provided in an input listing provided at execution time

    3.      Using those results, create a script that drops and recreates those constraints and indexes found (on all tables) including a shell column alteration (in the middle of the drop and recreation of constraints and indexes)

    I guess given these fully qualified needs would someone care to elaborate on a way to create a script to generate this kind of output?

    I have read bol and am reasonably familiar with the various system tables. I guess some of the following would be useful but not sure how to fully employ in a solution as described:

    -- gets listing of all user tables within a database

    Select * from sysobjects where xtype = 'U'

    -- gets listing of constraints on all tables in a database

    Select obj.name from sysconstraints con inner join sysobjects obj on con.constid = obj.id

    Many others in the forum respond with great script suggestions and gotchas they have encountered when trying to solve similar problems. This forum is well known for being a great place to discuss best practices. I felt that this problem was one that might encourage collaboration and generate interesting feedback. Needless to say, I did not expect to get a listing of bol system tables.

  • March 30, 2006 at 4:14 pm

    #629988

    I went here

    http://www.sqlservercentral.com/search/turbo.asp

    Typed in Constraint, selected 'Script Library', and clicked 'Find It!'

    Guess what was returned,

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=246

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=213

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=899

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=202

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1202

    http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=246

    Now with the wealth of information that was returned in that simple search, and your relutance to do any of the suggestions put forward by Sergiy and myself, it would seem to me that you would like a script written for you with minimal effort on your part.

     

    --------------------
    Colt 45 - the original point and click interface

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

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