Loop through all tables and drop all

[glgcag]

I am just starting a conversion from Access to SQL Server 2005 and I'm using it as an opportunity to learn SQL Server in the process.

I am testing extensively but want to occasionally update the SQL Server testing db with data from my live Access database.

I need to drop all tables from my database and then run my stored procs and SSIS package every time I update the data, which is fine, but I have a simple thing I need to do that I can't seem to figure out.

(Keep in mind I'm new to writing T-SQL!)

Here's the code I'm using to drop all the tables (and it's not working!)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Description:	DROPS all the tables in the database--for use
--              during import of the GPTData Access database.
-- =============================================
ALTER PROCEDURE [dbo].[DropTables] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

    DECLARE @TableName varchar(800);
    DECLARE @strSQL    varchar(8000);
    DECLARE c1 CURSOR FOR
        SELECT TABLE_NAME
          FROM INFORMATION_SCHEMA.TABLES
	FOR READ ONLY

	OPEN c1
	FETCH NEXT FROM c1
    INTO @TableName

	-- loop until we run out of rows
	WHILE @@FETCH_STATUS = 0
	BEGIN

        IF LEFT (@TableName, 1) != 'V'
            BEGIN
            SET @strSQL = 'DROP TABLE ' + @TableName
            EXECUTE (@strSQL)
            END

	    FETCH NEXT FROM c1
        INTO @TableName
	END
	CLOSE c1
	DEALLOCATE c1

END

Any suggestions for what I'm doing wrong?

Thanks for the help!

 

[onpnt]

Are you absolutely sure you need to drop all the tables? That always seems a bit odd to me

_{____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own}

 

[glgcag]

Yes, basically I run three things:

- SP to delete all tables
- SP to create initial tables with proper keys, indexes
- SSIS package to suck the data in from Access

It's a decent sized database with enough Access crud that there's a lot going on in the conversion. So this process is necessary each time I update the actual data in my test SQL Server database. Once I'm done testing, I'll just do this once more and make the SQL Server my production back end and all will be done, but until then, this is my lot.

 

[aspvbnetnerd]

Ain't nothing wrong with that code...That should work

 

[gmmastros]

You should use....

Select Table_Name from information_Schema.tables [!]Where Table_Type = 'Base Table'[/!]

Also, it's possible to have a table with a space in the name (not good practice), so you should use square brackets around the table name.

SET @strSQL = 'DROP TABLE [!][[/!]' + @TableName + '[!]][/!]'


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[glgcag]

aspvbnetnerd,

For some reason it doesn't work though.

gmmastros,

I don't have any tables with spaces in them, thankfully, but I added your code all the same.

Still doesn't work though!

 

[aspvbnetnerd]

Just test the code like this

SET @strSQL = 'DROP TABLE ' + @TableName
SELECT strSQL 
--EXECUTE (@strSQL)

And the try to drop the from strSQL result and se if it works

George

 

[glgcag]

You know what, I think it IS working . . .

In Microsoft SQL Server Management Studio I keep hitting the refresh button on the Object Explorer pane after running the sp, and the tables are all still listed. But I just right clicked on the "Tables" folder and selected "refresh" and the tables disappeared. Hmmmmm . . .

OK, I ran the whole process again, and sure enough, hitting refresh at the "Tables" folder level was what I was missing!

Man, do I feel like an idiot!

Thanks for the help!

 

[bborissov]

glgcag,
Isn't it easier to DROP the entire DataBase and then recreate it from scratch?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[glgcag]

bborissov,

It just adds an additional step of setting up the database again, as opposed to just deleting all the tables and then recreating the tables and importing the data.

 

[mutley1]

glgcag,

I think Borislav means you could have a template of the database, drop it and recreate it from the template, the same way the model database works in SQL. There are a few ways to do it, but you could create the blank DB with empty tables then take a backup. Write a stored proc that will drop the db then restore from the backup, OR just a restore to overwrite the existing.

HTH,

M.