Professional Web Applications Themes

How to drop all trhe data in a database? - Microsoft SQL / MS SQL Server

Is there a way to do this? I have tried the following but get an error as it seems I cant use a variable name as the parameter for the TRUNCATE statement: DECLARE tables_cursor CURSOR FOR select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 DECLARE Name nvarchar(250) OPEN tables_cursor -- Perform the first fetch. FETCH NEXT FROM tables_cursor INTO Name -- Check FETCH_STATUS to see if there are any more rows to fetch. WHILE FETCH_STATUS = 0 BEGIN TRUNCATE TABLE Name -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM tables_cursor INTO Name END ...

  1. #1

    Default How to drop all trhe data in a database?

    Is there a way to do this? I have tried the following but get an error as it
    seems I cant use a variable name as the parameter for the TRUNCATE
    statement:

    DECLARE tables_cursor CURSOR FOR
    select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

    DECLARE Name nvarchar(250)

    OPEN tables_cursor

    -- Perform the first fetch.
    FETCH NEXT FROM tables_cursor INTO Name

    -- Check FETCH_STATUS to see if there are any more rows to fetch.
    WHILE FETCH_STATUS = 0
    BEGIN
    TRUNCATE TABLE Name
    -- This is executed as long as the previous fetch succeeds.
    FETCH NEXT FROM tables_cursor INTO Name
    END

    CLOSE tables_cursor
    DEALLOCATE tables_cursor
    GO



    SamIAm Guest

  2. #2

    Default How to drop all trhe data in a database?

    You have to use dynamic SQL.
    >-----Original Message-----
    >Is there a way to do this? I have tried the following but
    get an error as it
    >seems I cant use a variable name as the parameter for the
    TRUNCATE
    >statement:
    >
    >DECLARE tables_cursor CURSOR FOR
    >select name from dbo.sysobjects where OBJECTPROPERTY(id,
    N'IsUserTable') = 1
    >
    >DECLARE Name nvarchar(250)
    >
    >OPEN tables_cursor
    >
    >-- Perform the first fetch.
    >FETCH NEXT FROM tables_cursor INTO Name
    >
    >-- Check FETCH_STATUS to see if there are any more rows
    to fetch.
    >WHILE FETCH_STATUS = 0
    >BEGIN
    > TRUNCATE TABLE Name
    >-- This is executed as long as the previous fetch
    succeeds.
    > FETCH NEXT FROM tables_cursor INTO Name
    >END
    >
    >CLOSE tables_cursor
    >DEALLOCATE tables_cursor
    >GO
    >
    >
    >
    >.
    >
    DJoe Guest

  3. #3

    Default Re: How to drop all trhe data in a database?

    Thanks

    "DJoe" <DJoe> wrote in message
    news:062301c34035$752e9fa0$a301280aphx.gbl...
    > You have to use dynamic SQL.
    >
    > >-----Original Message-----
    > >Is there a way to do this? I have tried the following but
    > get an error as it
    > >seems I cant use a variable name as the parameter for the
    > TRUNCATE
    > >statement:
    > >
    > >DECLARE tables_cursor CURSOR FOR
    > >select name from dbo.sysobjects where OBJECTPROPERTY(id,
    > N'IsUserTable') = 1
    > >
    > >DECLARE Name nvarchar(250)
    > >
    > >OPEN tables_cursor
    > >
    > >-- Perform the first fetch.
    > >FETCH NEXT FROM tables_cursor INTO Name
    > >
    > >-- Check FETCH_STATUS to see if there are any more rows
    > to fetch.
    > >WHILE FETCH_STATUS = 0
    > >BEGIN
    > > TRUNCATE TABLE Name
    > >-- This is executed as long as the previous fetch
    > succeeds.
    > > FETCH NEXT FROM tables_cursor INTO Name
    > >END
    > >
    > >CLOSE tables_cursor
    > >DEALLOCATE tables_cursor
    > >GO
    > >
    > >
    > >
    > >.
    > >

    SamIAm Guest

  4. #4

    Default Re: How to drop all trhe data in a database?

    It's probably easier to just script out the db, drop it and recreate it than
    to deal with that. You can not truncate tables with child references on
    them anyway.

    --

    Andrew J. Kelly
    SQL Server MVP


    "SamIAm" <samiamrubbachicken.com> wrote in message
    news:OkAffRDQDHA.3016TK2MSFTNGP10.phx.gbl...
    > Is there a way to do this? I have tried the following but get an error as
    it
    > seems I cant use a variable name as the parameter for the TRUNCATE
    > statement:
    >
    > DECLARE tables_cursor CURSOR FOR
    > select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') =
    1
    >
    > DECLARE Name nvarchar(250)
    >
    > OPEN tables_cursor
    >
    > -- Perform the first fetch.
    > FETCH NEXT FROM tables_cursor INTO Name
    >
    > -- Check FETCH_STATUS to see if there are any more rows to fetch.
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > TRUNCATE TABLE Name
    > -- This is executed as long as the previous fetch succeeds.
    > FETCH NEXT FROM tables_cursor INTO Name
    > END
    >
    > CLOSE tables_cursor
    > DEALLOCATE tables_cursor
    > GO
    >
    >
    >

    Andrew J. Kelly Guest

  5. #5

    Default Re: How to drop all trhe data in a database?

    One of my examples just for your reference:

    DECLARE Loop INT
    SET Loop = 0
    DECLARE MaxCount INT
    SET MaxCount = 20
    DECLARE OriginTable SYSNAME
    DECLARE sql NVARCHAR(1000)

    While (Loop < MaxCount)

    BEGIN

    SET Loop = Loop + 1

    SET OriginTable = 'MO_3D_Origin_' + CAST(Loop AS VARCHAR(10))

    SET sql = 'CREATE TABLE [' + OriginTable + ']('
    SET sql = sql + '[ID] [int] IDENTITY (1, 1) NOT NULL ,'
    SET sql = sql + '[x] [int] NULL ,'
    SET sql = sql + '[y] [int] NULL ,'
    SET sql = sql + '[z] [int] NULL)'
    EXEC sp_executesql sql

    END
    GO


    "SamIAm" <samiamrubbachicken.com> wrote in message
    news:OkAffRDQDHA.3016TK2MSFTNGP10.phx.gbl...
    > Is there a way to do this? I have tried the following but get an error as
    it
    > seems I cant use a variable name as the parameter for the TRUNCATE
    > statement:
    >
    > DECLARE tables_cursor CURSOR FOR
    > select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') =
    1
    >
    > DECLARE Name nvarchar(250)
    >
    > OPEN tables_cursor
    >
    > -- Perform the first fetch.
    > FETCH NEXT FROM tables_cursor INTO Name
    >
    > -- Check FETCH_STATUS to see if there are any more rows to fetch.
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > TRUNCATE TABLE Name
    > -- This is executed as long as the previous fetch succeeds.
    > FETCH NEXT FROM tables_cursor INTO Name
    > END
    >
    > CLOSE tables_cursor
    > DEALLOCATE tables_cursor
    > GO
    >
    >
    >

    TH Guest

  6. #6

    Default Re: How to drop all trhe data in a database?

    I asked the seme question a while ago (i only wanted to truncate user
    tables)
    I forget who answerd me so apologies for not crediting them
    anyway here is what works for me

    print 'tables not referenced'
    print ''
    execute sp_msforeachtable N'
    if not exists (select * from sysreferences where rkeyid =
    object_id(''?''))
    begin
    print ''?''
    truncate table ?
    end'

    print ''
    print ''
    print 'tables referenced'
    print ''
    execute sp_msforeachtable N'
    if exists (select * from sysreferences where rkeyid = object_id(''?''))
    begin
    print ''?''
    delete ?
    end'


    "SamIAm" <samiamrubbachicken.com> wrote in message
    news:OkAffRDQDHA.3016TK2MSFTNGP10.phx.gbl...
    > Is there a way to do this? I have tried the following but get an error as
    it
    > seems I cant use a variable name as the parameter for the TRUNCATE
    > statement:
    >
    > DECLARE tables_cursor CURSOR FOR
    > select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') =
    1
    >
    > DECLARE Name nvarchar(250)
    >
    > OPEN tables_cursor
    >
    > -- Perform the first fetch.
    > FETCH NEXT FROM tables_cursor INTO Name
    >
    > -- Check FETCH_STATUS to see if there are any more rows to fetch.
    > WHILE FETCH_STATUS = 0
    > BEGIN
    > TRUNCATE TABLE Name
    > -- This is executed as long as the previous fetch succeeds.
    > FETCH NEXT FROM tables_cursor INTO Name
    > END
    >
    > CLOSE tables_cursor
    > DEALLOCATE tables_cursor
    > GO
    >
    >
    >

    Reg Besseling Guest

Similar Threads

  1. Populating Drop Down Boxes from a database
    By gskar in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 14th, 10:49 PM
  2. Drag and drop data between cells in a data grid
    By Saravana [MVP] in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: June 7th, 03:55 AM
  3. Import Data from flat-database to relational-database
    By Dieter Schmidt in forum FileMaker
    Replies: 1
    Last Post: September 17th, 12:05 PM
  4. Replies: 2
    Last Post: September 16th, 10:30 AM
  5. DB2 on OS/390 drop database
    By R T in forum IBM DB2
    Replies: 0
    Last Post: August 19th, 05:30 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139