Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: How to retrieve all information abt all constraints inorder to drop and recreate them

    Hello,
    I need to retrieve all the information about all the foreign key constraints,inorder to store them temporarily (to be deleted later)and then recreated after making the necessary modifications to the concerned tables.
    The stored proc sp_helpconstraint shows all the constraint types, their user-defined or system-supplied name, the columns on which they have been defined, and the expression that defines them.But I don't know whether it can be manipulated to get what I want.I need to get it done programmatically..so that I can integrate it in my program which I'm building up progressively.
    Any help or scripts would be appreciated!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    First step - dropping:

    select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE='FOREIGN KEY'

  3. #3
    Join Date
    May 2004
    Posts
    7

    How to retrieve all information abt all constraints inorder to drop and recreate them

    i first need to store the information about the constraints before dropping them, so that they can be recreated later after the necessary changes have been made..i have 200+ tables...any tips?

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    select *
    INTO TempConstraintStore
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE='FOREIGN KEY'

    select 'alter table '+TABLE_NAME+ ' DROP CONSTRAINT '+CONSTRAINT_NAME
    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    where CONSTRAINT_TYPE='FOREIGN KEY'

    INSERT INTO INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    SELECT * FROM TempConstraintStore

    or something like that
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    INSERT into system tables...hmmmmmmm

    No thanks...

    Just go in and Script the contraints and save the script

    Do the drops, necessary changes have been made..then rerun the script

    What are the changes?

    If RI is out of wack the constraints will fail.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2004
    Posts
    7
    i'm aware that scripting the constraints using EM would make life easier,but no such luck for me..it has to be done using TSQL..i'm using the information schemas to retreive info,like referential_constraints, columns,constraint_column_usage..is that enough??

  7. #7
    Join Date
    Apr 2004
    Posts
    22

    Lightbulb use SQL-DMO

    try to use SQL-DMO with something like that (VBS):

    Set dmoSQLServer = CreateObject("SQLDMO.SQLServer")
    dmoSQLServer.Connect "MyServer", "MyLogin", "MyPSWD"

    For Each dmoObj In dmoSQLServer.Databases("MyDBName").Tables
    If dmoObj.SystemObject = False Then
    dmoObj.Script 134348800, dmoObj.Name & ".fky"
    End If
    Next

    dmoSQLServer.Disconnect
    Set dmoSQLServer = Nothing

    One file storing all FK creates for each table (TableName.fky). If table have no FK this file will be empty

  8. #8
    Join Date
    Oct 2003
    Posts
    7

    add a foreign key to an existing column

    Similar problem?

    I simply want to add a foreign-key constraint to an existing column.

    This is what I have but no luck so far.

    alter table table_name with check
    alter column column_name
    add constraint foreign_key_name
    references Reference_Table (Reference_column)

    I know it can't be far from this - I think I've even done it before!

    John

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use something like:
    Code:
    ALTER TABLE myTable
       ADD CONSTRAINT myConstraint
          FOREIGN KEY (myColumn)
             REFERENCES anotherTable (differentColumn)
    -PatP

Posting Permissions

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