SQL Server Performance

Truncate Entire DB

Discussion in 'General DBA Questions' started by slappy, Dec 21, 2005.

  1. slappy New Member

    Hello All,

    I'm very new still to SQL 2000 and was wondering if there is a way to truncate an entire database so that all data is wiped out but all other structures remain?

    I created a second database on the same machine for a new project and the way I ended up doing it restored old data in the new database. I would like to avoid redoing everything and wonder if you would have any recommendations on how to keep everything intact minus data.

    Thank you in advance
  2. Madhivanan Moderator

    You need to truncate every table in the table
    Otherwise Run Generate Script for that Db; Drop that DB;Run the script to create the objects

    Madhivanan

    Failing to plan is Planning to fail
  3. johnson_ef Member

    Please try this

    First you change the result in 'text' from the 'Grid'

    Then execute this script:
    select 'truncate table ' + name + CHAR(10) + 'GO ' + CHAR(10) from sysobjects where xtype='U'

    then you will get the list of truncate script for the entire tables in the database.

    Just copy and paste it in the QA and execute the given scripts.

    I think this will help you to truncate all tables in the database.

    -Johnson
  4. ranjitjain New Member

    Hi johnson,
    The script will not run as the GO will be inline with truncate statement.
    It has to be on the next line of truncate statement.
  5. RGKN New Member

    I think that that is why johnson said get the query results to go to text rather than grids, and that is what the char(10)s are for, it looks to me like it would work.

    Regards,

    Robert.



  6. RGKN New Member

    PS If you have spaces in your table names you will need to include [ and ] around the table name.

    Robert.
  7. Adriaan New Member

    Robert, you can't have spaces in object names, only in aliases.
  8. Madhivanan Moderator

    quote:Originally posted by Adriaan

    Robert, you can't have spaces in object names, only in aliases.
    I dont understand. Do you mean this is not possible?
    Create table [My table with spaces] (i int)

    Madhivanan

    Failing to plan is Planning to fail
  9. Adriaan New Member

    Okay, let me rephrase that - I was under the impression that SQL Server would refuse names with spaces but clearly it doesn't, and that's a disappointment!

    You should not have spaces in names, not even in aliases.

    You always make errors when typing out object names, but trust me: you will make more errors when you do include spaces. Plus you always have to use square brackets, because if you don't then you either get run-time errors, or you have ambiguous T-SQL statements that behave unpredictably.
  10. RGKN New Member

    Adriann,

    This I was unaware of, however if you run the following pieces of code you will be able to create a table that looks like it has a space in it.


    USE Northwind
    GO

    CREATE TABLE [Robert Test] (TID INT IDENTITY(1,1) NOT NULL,
    TName varchar(10) NOT NULL)
    GO

    select name from sysobjects where xtype='U'
    and name like 'Robert%'
    GO

    INSERT [Robert Test] (TName)
    VALUES('Adriaan')
    INSERT [Robert Test] (TName)
    VALUES('Robert')
    GO
    SELECT * FROM [Robert Test]
    GO
    -- Next line will error without the square brackets.
    TRUNCATE TABLE Robert Test
    GO
    TRUNCATE TABLE [Robert Test]
    GO

    SELECT * FROM [Robert Test]
    GO
    DROP TABLE [Robert Test]
    GO


    Which would mean if the developer had done as they have done in Northwind with the lazily/irritatingly named table [Order Details] then Johnson's code won't work. To all intents and purposes the value stored in the name field of sysobjects has a space in it.

    Regards,

    Robert.



  11. RGKN New Member

    Ooops, missed the two replies in between!

    yes indeed it is an extraordinary irritation and bad practice to put spaces in but it seems it can be done. I was trying to help slappy with his question, hence the comment about square brackets.

    Robert.

  12. surendrakalekar New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by ranjitjain</i><br /><br />Hi johnson,<br />The script will not run as the GO will be inline with truncate statement.<br />It has to be on the next line of truncate statement.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]Hey Ranjit, If you disply the result in text format then it will be on the next line.<br /><br /><h6>Surendra Kalekar</h6>
  13. Madhivanan Moderator

    Well. I would always use Underscore(_) if there is need of space for clarity [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. Adriaan New Member

    Note that you won't get any errors for this ...<br /><br />CREATE TABLE [delete from master.dbo.sysobjects] (i INT)<br />DROP TABLE [delete from master.dbo.sysobjects]<br /><br />Now what happens if you type in just the name of the table, no brackets, and you execute the query? Who can you blame?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  15. Madhivanan Moderator

    Thats Good Point Adriaan. Thats why I always use Underscore if needed [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  16. RGKN New Member

    You blame the incompetent developer who created the table...
  17. mmarovic Active Member

    Anyway, simple truncate table script will not work because tables referenced by fk constraints can't be truncated. Simpler solution that works is to generate all tables drop/creation script from EM and execute that script in your db using QA.
  18. gkrishn New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Note that you won't get any errors for this ...<br /><br />CREATE TABLE [delete from master.dbo.sysobjects] (i INT)<br />DROP TABLE [delete from master.dbo.sysobjects]<br /><br />Now what happens if you type in just the name of the table, no brackets, and you execute the query? Who can you blame?[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I tried without quoted identifier , and no harm with that, just a syntax error . [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  19. Adriaan New Member

    ... or use:

    DELETE FROM <tablename>

    The point about the FKs is of course a very good one, and when you try to a DELETE in that case you will get errors too.

    There must be standard scripts around to detect all tables that are referenced for FKs, so you can leave those for last. Also you are probably unnecessarily deleting from child tables - again, there should be scripts to detect that.
  20. slappy New Member

    I appreciate the responses thus far. I certainly didn't expect so many but thanks! Just for clarification the only separator in the tables in this particular database are under_scores.
  21. FrankKalis Moderator

    If you need a copy of all database object and don't care for the data, I would check out the scripting function of the SQL Server Enterprise Manager. You can have all objects scripted in one file and can run this file against another server.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  22. satya Moderator

    True, if you need the skeleton of the database structure then generating scripts from the database is the best method and ensure the database object naming convention is followed to avoid any surprised when restoring to other server and connection from an application.

    http://vyaskn.tripod.com/object_naming.htm fyi.

    Satya SKJ
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page