Stored proc with params to select a specific database? 2

[glgcag1991]

I'm trying to build a stored procedure that I can reuse when I'm building and testing databases. Often I want to drop all tables in a database that I specify but I'm not sure of the syntax. Here's my current proc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_DropTables] 

AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here

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

	OPEN c1
	FETCH NEXT FROM c1
    INTO @TableName

	-- loop until we run out of tables
	WHILE @@FETCH_STATUS = 0
	
        IF LEFT (@TableName, 1) != 'V'
            BEGIN
            SET @strSQL = 'DROP TABLE ' + @TableName
            EXECUTE (@strSQL)
            END

	    FETCH NEXT FROM c1
        INTO @TableName

	CLOSE c1
	DEALLOCATE c1
END

If I add a parameter to this sp for a database name, I would assume the database name would go in this part of the proc "SELECT TABLE NAME FROM sys.tables", but I'm not sure how to get to the databases- is it sys.databases."name".tables or something like it?

Thanks for the help!

 

[tran008]

Select Name From master.dbo.SysDatabases

 

[glgcag1991]

OK, but how does this work for deleting tables from a specific database on that server. In other words, where do I plug in the database name so that it knows to only delete tables from the specified database?

 

[PWise]

try

exec sp_msforeachtable 'drop table ?'

 

[glgcag1991]

pwise,

Thanks for the tip- I've been playing around with both sp_MSforeachdb and sp_MSforeachtable and they are awesome!

I've run into a little bump in the road though regarding the drop table using the wildcard for the table. Here's my code:

DECLARE @DBname varchar(200)
DECLARE @SQL varchar(2000)
SET @DBName = 'TestDB'
SET @SQL = 'EXEC ' + @DBName + '..sp_MSforeachtable "drop table ""?"""'
EXEC @SQL

The quotes around the ? are causing it to error. I've tried every way to enclose the ? in multiple single and double quotes and I keep getting errors.

Because I'm passing in the database name I need to do it this way (unless there's a better way) but I can't seem to get the drop table to work!

 

[gmmastros]

DECLARE @DBname varchar(200)
DECLARE @SQL varchar(2000)
SET @DBName = 'TestDB'
SET @SQL = 'EXEC ' + @DBName + '..sp_MSforeachtable ''drop table ?'''
Exec (@SQL)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Isn't it easier to DROP database and then recreate it again?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

The code I posted above should work 'sometimes', but it can be better.

DECLARE @DBname varchar(200)
DECLARE @SQL varchar(2000)
SET @DBName = 'TekTips'
SET @SQL = 'EXEC [!][[/!]' + @DBName + '[!]][/!]..sp_MSforeachtable ''drop table ?'''
Exec (@SQL)

By putting square brackets around the database name, you are protecting yourself against database names with spaces or reserved words. Of course, we never use database names like that, do we?

Also, be aware that this code can still fail. You cannot drop a table that has a foreign key constraint set up for it.

For example....

Create Table Test1(id Int Primary Key)

Create Table Blah(Id int, Test1Id Int References Test1(id))

-- the drop statement fails because it is used
-- in a foreign key constraint.
Drop Table Test1

-- The next 2 will work because we drop the Blah table first.
Drop Table Blah
Drop Table Test1

So... if you create the 2 tables in that last code block, and then try to run your code (for dropping all the tables), it will fail. Running twice will work though. The first time through, you'll get a message about not dropping table test1, but the Blah table will be dropped (which also drops the foreign key constraint). The second time through, the Test1 table does not have a foreign key constraint, so it can now be dropped.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[glgcag1991]

gmmastros,

That did it! I thought I tried that combination, but I guess not! Thanks!

I wrote the code to first store the table and row count in a temp table, then use the @command2 to drop the tables. That way I display all the tables at the end as a visual confirmation of what was deleted.

ALTER PROC [dbo].[spGG_CustomDropTables]
	@DBName varchar(128)
AS
BEGIN
CREATE TABLE #TP(tablename varchar(128), rowcnt int)
DECLARE @SQL varchar(2000)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC ' + @DBName + '..sp_MSforeachtable '
SELECT @SQL = @SQL + '@command1 = "insert into #TP select ""?"", count(*) from ?", '
SELECT @SQL = @SQL + '@command2 = ''DROP TABLE ?'''
PRINT @sql

EXECUTE (@SQL)

SELECT * FROM #TP
	order by tablename
drop table #TP
END

It works nicely!

 

[glgcag1991]

It seems like I should just ignore errors then and run the sp_MSforeachtable again to make sure all tables are truly deleted . . .

And, yes, dropping the database would be easier, but I'm converting a large Access database and the weird things I'm having to do require this tool to make my life a little easier.

Lastly, I've read that I should store sp's specific to a database IN that database, but what about sp's I need in more than one (or all) databases on a server? Where should I put those? And, typically, do you run one giant sp that creates all your system-wide custom sp's/functions when creating a new SQL Server? (I ask this because I'm new to SQL Server programming and I'm amassing some useful tools that I want to deploy for each of the SQL Server installs I'm setting up on different servers.)

Thanks for the help!