Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130

    Unanswered: how to tranct all tables data in single line

    hi,
    how to use d tranct option for all tables at a time in single reason thanks for helping
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    A one-liner for this does not exist in SQL Server. If you don't have any referential integrity, you may issue the following little piece of code:
    Code:
    declare
      @sql nvarchar(max);
    set @sql = '';
    select
      @sql = @sql + 'truncate table ['+ss.name+'].['+so.name+']'+char(13)+char(10)
    from
      sys.objects so
        inner join
      sys.schemas ss on
          ss.schema_id = so.schema_id
    where
      so.type = 'U'
    exec sp_executesql @sql;
    If you have referential integrity, you can either run it until it does not return any error, or you may alter the code to issue the truncation in the correct order. But as I said, out of the box there is no such thing as a one liner for truncating all tables, and thank God for that, I can clearly see the frustration of someone with too much privileges that just have wiped out the entire database.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you have referential integrity enforced, you can't use TRUNCATE at all....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Nice catch blindman. I thought you could if there were no rows referencing the table, but apparently not. Thanks for the update.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Blindman, Roac, you guys are REALLY amazing.

    how to use d tranct option for all tables at a time in single reason thanks for helping
    How did you knew the question was about the use of TRUNCATE? The best I could interpret this question, was it had something to do with a giant transaction to be performed on all tables of a database. But I wasn't even sure about the correctness of that interpretation.

    English is not my native language, is that the reason why I failed where you succeeded? But nor is English the native tongue of Roac ...

    Anyway, you guys are Gods! Err ... angels.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The other way to do this would be to use some undocumented stored procedures that loop through all the tables.
    sp_msforeachdb and sp_msforeachtable.
    Here's a link to more info on them:
    Take advantage of undocumented SQL Server iteration procedures | TechRepublic
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    English is definitely not my native language, and I could not know whether he asked about a huge transaction or truncation, but: Transactions are in fact held for all tables being accessed, and it seemed plausible that someone would ask how to truncate all the tables in a database. It would actually be a nice feature when debugging data load
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  8. #8
    Join Date
    Jun 2011
    Posts
    6

    Becarefull to run this query

    It's solve truncate problem

    DECLARE @SqlTable VARCHAR(max)
    SET @SqlTable = ''

    SELECT @SqlTable = @SqlTable + 'DELETE FROM ' + o.[name] + ';
    DBCC CHECKIDENT ('''+[name]+''',RESEED,1);
    '
    FROM sys.objects o
    WHERE o.[type] = 'U'


    exec @SqlTable

  9. #9
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by roac View Post
    A one-liner for this does not exist in SQL Server. If you don't have any referential integrity, you may issue the following little piece of code:
    Code:
    declare
      @sql nvarchar(max);
    set @sql = '';
    select
      @sql = @sql + 'truncate table ['+ss.name+'].['+so.name+']'+char(13)+char(10)
    from
      sys.objects so
        inner join
      sys.schemas ss on
          ss.schema_id = so.schema_id
    where
      so.type = 'U'
    exec sp_executesql @sql;
    thanks a lot for ur help in my database there are around 500 tables so i asked this qsn i hope this will helpful for me
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

  10. #10
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by greenx View Post
    It's solve truncate problem

    DECLARE @SqlTable VARCHAR(max)
    SET @SqlTable = ''

    SELECT @SqlTable = @SqlTable + 'DELETE FROM ' + o.[name] + ';
    DBCC CHECKIDENT ('''+[name]+''',RESEED,1);
    '
    FROM sys.objects o
    WHERE o.[type] = 'U'


    exec @SqlTable
    thanks i will try both n intimate u thanks again n again for all persons in this forum
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

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