Post reply

Stored procedure to change all FK attribute (NOT FOR REPLICATION)

  • August 3, 2005 at 10:44 pm

    #88586

    Hi,

    I am trying and thinking very hard to solve a "simple" problem (my knowledge of TSQL is limited):

    I need a stored procedure to change all Foreign Key attributes in my database to have the NOT FOR REPLICATION attribute set. This is needed when I use the MsACCESS Upsize Wizard to generate a SQL server database, which I will use as a replicated database. The Upsize Wizard does not set this attribute.

    Can you please help?

    Thanks, Edwin

  • August 4, 2005 at 11:06 am

    #579729

    I think you'd need to scan all the tables for the FKs and then somehow alter them. I'm not sure you can just add not for replication, but I could be wrong. The tricky part is idoentifying all the constraints and knowing what needs to changed to build the syntax.

    You might be able to use this script (http://www.sqlservercentral.com/scripts/contributions/25.asp) to disable them and then reenable with the NOT FOR REPL parameter.

  • August 4, 2005 at 7:17 pm

    #579888

    Hi

    Thanks for the reply. I have seen this SP and I am still stuck using this SP to loop through all the table's foreign key names. The statement to alter the constraint "NOT FOR REPLICATION" for the column is straightforward. I just don't know how to program a stored procedure to loop through these foreign keys.

    Thanks, Edwin

  • August 6, 2005 at 10:10 pm

    #580263

    Hi

    I have been working a bit more on this problem. Using the following call I managed to create and export a list of all tables with their foreign keys:

    sp_msforeachtable @command1="sp_MStablekeys @tablename='?'"

    I modified the sp_MStablekeys procedure to include the tablename in the output as well and now I am able to import this in an Access database table (using Excel linked table).

    The statement I made earlier that the ALTER TABLE is easy is not true. These seem not to be a way to alter the foreign key CONSTRAINT, to add the NOT FOR REPLICATION. The way around this I found is to DROP the constraint and then to ADD is back to the table. A good thing is that the sp_MStablekeys gathers all the needed information to recreate the CONSTRAINT.

    Does anybody know that this is indeed the only way to solve this problem? Ultimately I would like to build a Stored Procedure which does all of this (I start with modifying sp_MStablekeys), but I do not have too much time to look into writing SPs unfortunately.

    Thanks, Edwin.

Viewing 4 posts - 1 through 4 (of 4 total)

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