dcsimg
Results 1 to 9 of 9

Thread: script FK constraints

  1. #1
    Join Date
    Oct 2002
    Posts
    123

    Arrow script FK constraints

    Does anyone have a script that could script all the FK constraints on a database?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918
    Are you using SQL 2000?.

    If yes then you could generate the script from Enterprise Manager when you create scripts for tables. All FK script are created after create tables so you can easily get that from the script file generated.

    If you want the fun way then you can use SQL DMO table object's script method.

  3. #3
    Join Date
    Oct 2002
    Posts
    123
    Thanks for the quick response!
    But I was really looking at scripting the FK like "Alter Table drop constraint...'
    and "Alter Table Add constraint...'
    through the system tables..any clue?

    Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,918

  5. #5
    Join Date
    Oct 2002
    Posts
    123
    sorry for the confusion...but this is what i am looking at...now this script is only able to give alters for FK with only 1 column, i want to get all FK columns included...

    thanks a bunch!


    CREATE PROCEDURE dbo.spGetFKConstraints
    (
    @DBNAME VARCHAR(128)
    )
    AS

    DECLARE @QUERY VARCHAR(8000)

    SET @QUERY =
    'USE ' + @DBNAME + '

    SELECT CAST(F.NAME AS VARCHAR(255)) AS ForeignKeyName,
    CAST(c.name as varchar(255)) AS ForeignTable,
    CAST(fc.name as varchar(255)) AS ForeignColumn,
    cast(fc2.name as varchar(255)) as ForeignColumn2,
    CAST(p.name as varchar(255)) AS PrimaryTable,
    CAST(rc.name as varchar(255)) AS PrimaryColumn,
    cast(rc2.name as varchar(255)) as PrimaryColumn2
    INTO #GetFKConstraints
    FROM SYSOBJECTS F
    INNER JOIN SYSOBJECTS C ON F.PARENT_OBJ = C.ID
    INNER JOIN SYSREFERENCES R on F.ID = R.CONSTID
    INNER JOIN SYSOBJECTS P ON R.RKEYID = P.ID
    INNER JOIN SYSCOLUMNS RC ON R.RKEYID = RC.ID AND R.RKEY1 = RC.COLID
    INNER JOIN SYSCOLUMNS FC ON R.FKEYID = FC.ID AND R.FKEY1 = FC.COLID
    left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    WHERE F.TYPE = '+'''F'''+'

    --SELECT * from #GetFKConstraints'+'

    SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable + ' + '''
    DROP CONSTRAINT ''' + '+ ForeignKeyName
    FROM #GetFKConstraints
    ORDER BY ForeignTable'
    +'

    SELECT ' + '''ALTER TABLE ''' + '+ ForeignTable +' + '''
    ADD CONSTRAINT ''' + '+ ForeignKeyName +'
    + ''' FOREIGN KEY ('''+ ' + ForeignColumn + '+ ''')
    REFERENCES ''' + '+ PrimaryTable + ' + '''(''' + '+ PrimaryColumn + '+ ''')''' +
    'FROM #GetFKConstraints
    ORDER BY ForeignTable
    '

    EXEC (@QUERY)
    GO

  6. #6
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152

    Smile I hope this is what you want ...

    set nocount on
    declare @MyTable table
    ( id int identity
    , fkeyid int not null
    , constid int not null
    , keyno int null
    , name varchar(40) not null)

    ---
    insert into @MyTable (fkeyid , constid , keyno , name)
    select fkeyid , constid , max(keyno) as keyno , OBJECT_NAME(constid) as name
    from sysforeignkeys group by fkeyid , constid , rkeyid

    --SCRIPT_1FK ADD CONSTRAINT...
    select cmd as '-- script 1' from (

    select id, 1 as num, 0 as keyno, 'ALTER TABLE '+OBJECT_NAME(fkeyid)+' ADD CONSTRAINT '+name+' FOREIGN KEY' as cmd
    from @MyTable

    union
    --columns for fks - (foreign table)
    select id, 2 as num, k.keyno
    , case k.keyno when 1 then ' ' else ',' End
    +COL_NAME(k.fkeyid,k.fkey)
    +case k.keyno when i.keyno then ') REFERENCES '+OBJECT_NAME(k.rkeyid)+' (' else '' End as cmd
    from @MyTable i , sysforeignkeys k
    where k.fkeyid = i.fkeyid and k.constid = i.constid

    union
    --columns for fks - (reference table)
    select id, 3 as num, k.keyno
    , case k.keyno when 1 then ' ' else ',' End+COL_NAME(k.rkeyid,k.rkey) as cmd
    from @MyTable i , sysforeignkeys k
    where k.fkeyid = i.fkeyid and k.constid = i.constid

    union

    select id, 4 as num, 255 as keyno,');' as cmd
    from @MyTable

    ) as viewx order by id,num,keyno

    --SCRIPT_2FK DROP CONSTRAINT
    select 'ALTER TABLE '+OBJECT_NAME(fkeyid)+' DROP CONSTRAINT '+OBJECT_NAME(constid) as '-- script 2'
    from sysforeignkeys
    You Have To Be Happy With What You Have To Be Happy With (KC)

  7. #7
    Join Date
    Oct 2002
    Posts
    123
    Thanks YuckFou! This is what i was looking for!

    The only problem is that while it works like a charm on some databases, on the others it is giving the following error:

    String or binary data would be truncated.
    The statement has been terminated.

    It doesnt print the first script but prints the second script

    Thanks!

  8. #8
    Join Date
    Mar 2003
    Location
    Woking, UK
    Posts
    152
    I should used biger value for column [name] in @MyTable...
    Try grove it up like this

    declare @MyTable table
    ( id int identity
    , fkeyid int not null
    , constid int not null
    , keyno int null
    , name varchar(60) not null)


    -- 60 or more the top value is 128 or datetype sysname>
    ...
    , name sysname not null)
    You Have To Be Happy With What You Have To Be Happy With (KC)

  9. #9
    Join Date
    Oct 2002
    Posts
    123
    Works great! Thanks

Posting Permissions

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