Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Truncate all tables in DB with RI intact

    Anyone?

    I tried the following code...thought I could trap the RI Error and move on...

    It raises all the way out though....

    Server: Msg 4712, Level 16, State 1, Line 1
    Cannot truncate table 'myTable99' because it is being referenced by a FOREIGN KEY constraint.

    I thought you could trap it this way...

    Code:
    CREATE TABLE myTable99(Col1 int PRIMARY KEY)
    GO
    CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
    GO
    
    INSERT INTO myTable99(Col1) SELECT 1
    INSERT INTO myTable00(Col1, Col2) SELECT 1,2
    GO
    
    CREATE PROC myDynamicSQL99 @sql varchar(8000) AS EXEC(@sql)
    GO
    
    DECLARE @error int, @TABLE_NAME sysname, @sql varchar(8000), @x int, @rc int
    SELECT @error = 0, @x = 0
    
    DoItAgain:
    
    DECLARE myCursor99 CURSOR 
        FOR 
    	SELECT TABLE_NAME 
    	  FROM INFORMATION_SCHEMA.Tables 
    	 WHERE TABLE_NAME LIKE 'myTable%'
          ORDER BY TABLE_NAME DESC
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	SELECT @SQL = 'TRUNCATE TABLE ' + @TABLE_NAME
    	SELECT @SQL
    	EXEC @rc = myDynamicSQL99 @SQL
    	SELECT @Error = @@Error
    	SELECT 'Error Code: ' + CONVERT(varchar(15),@Error) + ' @rc: '+ CONVERT(varchar(15),@rc)  
    	IF @Error <> 0
    		SELECT @x = @x + 1
    	FETCH NEXT FROM myCursor99 INTO @TABLE_NAME
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    
    IF @x <> 0 
      BEGIN
    	SELECT @Error = 0
    	GOTO DoItAgain 
      END
    GO
    
    DROP PROC myDynamicSQL99
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Short answer is that it can not be done. You would have to drop the foreign keys. You can't truncate a parent table, even if the child table is empty, too. According to BOL, truncate can not fire a trigger, as well.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...Tara just let me know as well...but you could do a DELETE...which is not an option....

    Now to find a method to drop the RI from the catalog...I really want to avoid scripts, but there may not be a method....

    Damn

    Thanks
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If what you want is really to truncate every table in the database, can't you just use Enterprise Mangler to generate a script of the whole database (including drops), and run that at will?

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Pat Phelan
    If what you want is really to truncate every table in the database, can't you just use Enterprise Mangler to generate a script of the whole database (including drops), and run that at will?

    -PatP
    I guess....

    I'm trying to make this as hands as possible...and dynamic enough to run against any database..

    This is the sox nonsense where they need to have all the data scrambled for dev from prod...

    AND I have to throw the "privacy" database application over the wall so I can just have the prod db run a release script...

    I'll have to ask them to script out the constraints...from prod...nothing to say something can't change...and then blow up the process...

    You know this b'ness...have to be perfect....

    I hate taking gambles...like relying on a prod dba...
    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
    Sep 2003
    Posts
    364
    Here's a script I use that creates fk drop scripts, truncate table scripts, and re-creates the fk's when done. I know it's not what you're looking for but may help.

    Don't forget to check for views with schemabinding. You can't truncate any tables used by those views either. I always forget about those.
    Attached Files Attached Files
    Last edited by peterlemonjello; 06-22-04 at 14:45.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by peterlemonjello
    Here's a script I use that creates fk drop scripts, truncate table scripts, and re-creates the fk's when done. I know it's not what you're looking for but may help.

    Don't forget to check for views with schemabinding. You can't truncate any tables used by those views either. I always forget about those.
    Are you kidding?

    That's exactly what the doctor ordered...

    small problem though...if a table has a composite fk reference the statements comes aout for each column....

    EDIT: And theres a small problem with the constraint check this out

    CREATE TABLE myTable99(Col1 int PRIMARY KEY)
    GO
    CREATE TABLE myTable00(Col1 int, Col2 int, PRIMARY KEY(Col1,Col2), FOREIGN KEY (Col1) REFERENCES myTable99(Col1))
    GO
    CREATE TABLE myTable77(Col1 int, Col2 int, Col3 int
    , PRIMARY KEY (Col1,Col2,Col3)
    , FOREIGN KEY (Col1,Col2) REFERENCES myTable00(Col1,Col2))
    GO


    myTable77 look like...



    ALTER TABLE [dbo].[myTable00] ADD CONSTRAINT [FK__myTable00__Col1__7ADCFAB5]
    FOREIGN KEY ([Col1]) REFERENCES [myTable99] ( [Col1]) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO
    ALTER TABLE [dbo].[myTable77] ADD CONSTRAINT [FK__myTable77__7DB96760]
    FOREIGN KEY ([Col2]) REFERENCES [myTable00] ( [Col1],[Col2]) ON DELETE NO ACTION ON UPDATE NO ACTION
    GO
    ALTER TABLE [dbo].[myTable77] ADD CONSTRAINT [FK__myTable77__7DB96760]
    FOREIGN KEY ([Col2]) REFERENCES [myTable00] ( [Col1],[Col2]) ON DELETE NO ACTION ON UPDATE NO ACTION
    Last edited by Brett Kaiser; 06-22-04 at 15:14.
    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.

  8. #8
    Join Date
    Sep 2003
    Posts
    364
    Nice catch. Fortunately none of our db's have any composite fk's. Guess that's why I haven't ran into that problem.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'll post the code when it's fixed up...

    Look like you did worry about it though....

    Need to do the same for the FK Defenition...the following is for the REFERENCE

    Code:
    
    -- for each ordinal in the foreign key index...
    declare idx cursor local fast_forward read_only for
    select c.[COLUMN_NAME]
    from [INFORMATION_SCHEMA].[COLUMNS] c
    inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
    on c.[TABLE_NAME] = u.[TABLE_NAME]
    and c.[COLUMN_NAME] = u.[COLUMN_NAME]
    where u.[CONSTRAINT_NAME] = @fkconstraint
    order by u.[ORDINAL_POSITION]
    
    open idx
    
    set @comma = ''
    
    fetch next from idx
    into @column
    
    while @@fetch_status = 0
    begin
    set @createsql = @createsql + @comma + '[' + @column + ']'
    set @comma = ','
    
    fetch next from idx 
    into @column
    
    end
    
    close idx
    deallocate idx
    Last edited by Brett Kaiser; 06-22-04 at 16:05.
    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.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Which could've been written like:

    Code:
    DECLARE @Collist varchar(1000)
     SELECT @ColList = Null
    
    
      SELECT @ColList = COALESCE(@ColList + ', ', '') + 
       	 CAST(c.COLUMN_NAME AS sysname)
        FROM [INFORMATION_SCHEMA].[COLUMNS] c
        JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
          ON c.[TABLE_NAME] = u.[TABLE_NAME]
         AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
       WHERE u.[CONSTRAINT_NAME] = @fkconstraint
    ORDER BY u.[ORDINAL_POSITION]
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    I hate taking gambles...like relying on a prod dba...
    So you are more comfortable relying on the production dba not to change anything in production (like an index or a constraint) than you are on relying on them to get you a script where you can verify at least the time/date stamp? I'd be in favor of the script myself!

    -PatP

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd prefer them to run 1 bat file in a dos prompt, period...except sitting there and watching for alerts...

    And Peter...this is fixed now...THANKS A BUNCH!

    I think I'll store the commands to a table...the CREATES will have be done after the bcp


    John: We need to clean this up, but take the code, and create a sproc out of it, and change the PRINTS to INSERTS. Make a new table, call it....SQLSource...4 Columns...Executed char(1), Add_Dt datetime, DMLName vharchar(25), SQLSource varchar(7000)


    Maybe add a ScheduledDate Column as well...don't know if we'll use it yet...


    Code:
    DECLARE @cr nchar(2), @go nvarchar(8)
    
    SET @cr = nchar(13)+nchar(10)
    SET @go = @cr + 'GO' + @cr
    
    DECLARE @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128) 
    DECLARE @fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9) 
    DECLARE @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000)
    
    --------------------
    /* DROP LOGIC */
    --------------------
    print '----------Foreign Key Drop Statements----------'
    print @cr
    
    DECLARE cstrts CURSOR 
    LOCAL FAST_FORWARD READ_ONLY
    FOR
    SELECT DISTINCT
    	  c.[TABLE_SCHEMA]
    	, c.[TABLE_NAME]
    	, u.CONSTRAINT_NAME
      FROM   [INFORMATION_SCHEMA].[COLUMNS] c
      JOIN   [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
        ON    c.[TABLE_NAME]      = u.[TABLE_NAME]
       AND    c.[COLUMN_NAME]     = u.[COLUMN_NAME]
      JOIN   [INFORMATION_SCHEMA].[table_constraints] t
        ON    u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
     WHERE    t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'
    
    OPEN cstrts
    
    FETCH NEXT FROM cstrts
          INTO @schema, @tablename, @constraint
    
    WHILE @@fetch_status = 0
      BEGIN
    	SELECT    @fktable      = u.[TABLE_NAME]
    		, @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
    		--, @onupdate = r.[UPDATE_RULE]
    		--, @ondelete = r.[DELETE_RULE] 
    		--, @column = u.[COLUMN_NAME]
    	  FROM   [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
    	  JOIN   [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
    	    ON    r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
    	  JOIN   [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
    	    ON    u.[CONSTRAINT_NAME]        = r.[CONSTRAINT_NAME]
    	 WHERE    r.[CONSTRAINT_NAME]        = @constraint
    
    	SET @dropsql = 
    	'ALTER TABLE [' 
    	+ @schema 
    	+ '].[' 
    	+ @tablename 
    	+ '] DROP CONSTRAINT [' 
    	+ @constraint 
    	+ '] '
    	+ @go
    
    	PRINT @dropsql
    
    	FETCH NEXT FROM cstrts
    	      INTO @schema, @tablename, @constraint
      END
    
    CLOSE cstrts
    DEALLOCATE cstrts
    
    --------------------
    /* TRUNCATE LOGIC */
    --------------------
    print @cr
    print '----------Truncate Table Statements----------'
    print @cr
    
    declare cstrts cursor local fast_forward read_only for 
    SELECT o.[name]
    FROM syscolumns c
    INNER JOIN sysobjects o ON o.[Id] = c.[Id]
    INNER JOIN sysusers u ON o.[uid] = u.[uid]
    WHERE c.[status] & 128 = 128
    AND u.[name] = 'dbo'
    AND o.[name] <> 'dtproperties'
    
    open cstrts
    
    fetch next from cstrts
    into @tablename
    
    while @@fetch_status = 0
    begin
    
    set @truncatesql =
    'TRUNCATE TABLE ['
    + @tablename
    + ']'
    + @go
    
    print @truncatesql
    
    fetch next from cstrts
    into  @tablename
    
    end
    
    close cstrts
    deallocate cstrts
    
    --------------------
    /* CREATE LOGIC */
    --------------------
    print @cr
    print '----------Foreign Key Create Statements----------'
    print @cr
    
    declare cstrts cursor local fast_forward read_only for 
    select distinct
    c.[TABLE_SCHEMA],
    c.[TABLE_NAME],
    u.CONSTRAINT_NAME
    from [INFORMATION_SCHEMA].[COLUMNS] c
    inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
    on c.[TABLE_NAME] = u.[TABLE_NAME]
    and c.[COLUMN_NAME] = u.[COLUMN_NAME]
    inner join [INFORMATION_SCHEMA].[table_constraints] t
    on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
    where t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'
    
    open cstrts
    
    fetch next from cstrts
    into @schema, @tablename, @constraint
    
    while @@fetch_status = 0
    begin
    SELECT DISTINCT
    	  @fktable = u2.[TABLE_NAME]
    	, @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
    	, @onupdate = r.[UPDATE_RULE]
    	, @ondelete = r.[DELETE_RULE] 
    	--, @column = u.[COLUMN_NAME]
      FROM [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
      JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
        ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
      JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
        ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
     WHERE r.[CONSTRAINT_NAME] = @constraint
    
    DECLARE @ColList sysname
    SELECT @ColList = Null
    
    select @ColList = COALESCE(@ColList + ', ', '') + 
       	 '['+CAST(COLUMN_NAME AS sysname)+']'
    FROM (SELECT DISTINCT TOP 100 u.COLUMN_NAME, u.[ORDINAL_POSITION]
            FROM [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
            JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
              ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
            JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
              ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
           WHERE r.[CONSTRAINT_NAME] = @constraint
        ORDER BY u.[ORDINAL_POSITION]
    ) AS XXX
    
    set @createsql = 
    'ALTER TABLE [' 
    + @schema 
    + '].[' 
    + @tablename 
    + '] ADD CONSTRAINT [' 
    + @constraint 
    + '] '
    + @cr 
    + 'FOREIGN KEY (' 
    + @colList 
    + ') REFERENCES [' 
    + @fktable 
    + '] ('
    
     SELECT @ColList = Null
    
    
      SELECT @ColList = COALESCE(@ColList + ', ', '') + 
       	 '['+CAST(c.COLUMN_NAME AS sysname)+']'
        FROM [INFORMATION_SCHEMA].[COLUMNS] c
        JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
          ON c.[TABLE_NAME] = u.[TABLE_NAME]
         AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
       WHERE u.[CONSTRAINT_NAME] = @fkconstraint
    ORDER BY u.[ORDINAL_POSITION]
    
    set @createsql = @createsql + @colList
    
    set @createsql = @createsql + ') ON DELETE ' 
    + @ondelete 
    + ' ON UPDATE ' 
    + @onupdate
    + @go
    
    print @createsql
    
    fetch next from cstrts
    into @schema, @tablename, @constraint
    
    
    end
    
    close cstrts
    deallocate cstrts
    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.

  13. #13
    Join Date
    Sep 2003
    Posts
    364
    Thanks for the fix!

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    No, THANK YOU....

    Question: How did you learn to navigate that mess of a catalog?
    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.

  15. #15
    Join Date
    Sep 2003
    Posts
    364
    Well, I started with some scripts other people had done and then spent time browsing through the catalog until I found what I needed. So I guess it was kind of the trial and error approach. Not to mention reading BOL.

    Eventually, I want to reverse engineer every system object and document each in a nice datamodel and datadictionary. You know try to create something more informative and easier to use than the BOL.

    Next, I'm going to try and modify the script to create drop and re-create statements for views but haven't spent much time on it. We have some views that are schema binding in our db so the tables referenced by those views can't be truncated unless the view is dropped or created without schemabinding. Do you know where to pull this info?

Posting Permissions

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