Results 1 to 3 of 3

Thread: sp question

  1. #1
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61

    Unanswered: sp question

    Hi,

    I'm trying to execute an SP and i keep getting these errors

    Drop All Foreign Keys
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETScheduledRoutesDROP'.
    -DROPTScheduledRoutes_TBuses_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETScheduledRoutesDROP'.
    -DROPTScheduledRoutes_TDrivers_Alternate_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETScheduledRoutesDROP'.
    -DROPTScheduledRoutes_TDrivers_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETScheduledRoutesDROP'.
    -DROPTScheduledRoutes_TRoutes_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETScheduledRoutesDROP'.
    -DROPTScheduledRoutes_TScheduleTimes_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETTeamPlayersDROP'.
    -DROPTTeamPlayers_TPlayers_FK
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'TABLETTeamPlayersDROP'.
    -DROPTTeamPlayers_TTeams_FK
    DONE


    Here is what my SP looks like:

    CREATE PROCEDURE uspDropForeignKeys
    AS
    SET NOCOUNT ON
    DECLARE @strMessage VARCHAR(100)
    DECLARE @strForeignKey VARCHAR(100)
    DECLARE @strChildTable VARCHAR(100)
    DECLARE @strCommand VARCHAR(250)

    -- Drop all FK's

    SELECT @strMessage = 'Drop All Foreign Keys'
    PRINT @strMessage
    DECLARE crsForeignKeys CURSOR FOR
    SELECT
    name AS strForeignKey,
    OBJECT_NAME (parent_obj) AS strChildTable
    FROM
    sysobjects
    WHERE
    type = 'F'
    AND name LIKE '%FK'
    ORDER BY
    name

    OPEN crsForeignKeys
    FETCH NEXT FROM crsForeignKeys INTO @strForeignKey, @strChildTable

    --Loop until no more records
    WHILE (@@FETCH_STATUS = 0) BEGIN

    SELECT @strMessage = '-DROP' + @strForeignKey
    PRINT @strMessage

    --Build Command
    SELECT @strCommand = 'ALTER TABLE' + @strChildTable + 'DROP CONSTRAINT' + @strForeignKey

    --Execute Command
    EXECUTE (@strCommand)

    FETCH NEXT FROM crsForeignKeys INTO @strForeignKey, @strChildTable
    END

    DEALLOCATE crsForeignKeys

    PRINT 'DONE'






    Any ideas??

    Thanks for ANY help
    Smitty

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Smitty81
    SELECT @strCommand = 'ALTER TABLE' + @strChildTable + 'DROP CONSTRAINT' + @strForeignKey

    Smitty
    SELECT @strCommand = 'ALTER TABLE ' + @strChildTable + ' DROP CONSTRAINT ' + @strForeignKey

  3. #3
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61
    I see now! Thank you!

Posting Permissions

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