DROP at table, if exists

declare @table varchar(14)
set @table = select name from dbo.sysobjects where left(name,14)='ee_tablename_2'

IF EXISTS(select 1 from dbo.sysobjects where LEFT(name,14)='ee_tableName_2')
drop table @table

each day the table is going to be ee_tablename_YYYYMMDD, where the date is previous day.
I only want to keep them around for a 24 hr period, blowing them out each night before creating the new one.

i figured i could do a LEFT/SUBSTRING on name from sysobjects, but I can't get the DROP done syntactically.

 'incorrect syntax neer '@table'.

anybody?
LVL 18
dbaSQLAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
declare @table varchar(14)
select @table =  name from dbo.sysobjects where left(name,14)='ee_tablename_2'

IF EXISTS(select 1 from dbo.sysobjects where NAME = @Table)
EXEC('drop table '+@table )
0
dbaSQLAuthor Commented:
oooh, i was almost there.  i should have known that myself, aneesh. thank you very much


0
dbaSQLAuthor Commented:
but it didn't drop it....  i created a new one, filled it up and renamed two tables.
i did not, however, delete the pre-existing ee_tablename_2
now i've got ee_tablename_20080128, ee_tablename_20080129, and ee_tablename

i just want to delete the pre-existing one each night, such that I am only carrying one 'extra' table w/me for 24hrs... just in case.  do you see my error?

DECLARE @table NVARCHAR(20)
DECLARE @Object NVARCHAR(200)
SELECT @table = Name FROM dbo.sysobjects WHERE LEFT(name,14)='ee_tablename_2'
SET @Object = 'ee_tablename'+'_'+ convert(varchar,dateadd(dd,0,getdate()),112)
IF EXISTS(select 1 from dbo.sysobjects where LEFT(name,14) = @table)
EXEC('DROP TABLE '+@table)
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[ee_tablename]'))
  EXEC sp_Rename '[dbo].[ee_tablename]', @Object
  EXEC sp_rename 'tableNEW','ee_tablename'
  GRANT SELECT ON dbo.ee_tablename TO username
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbaSQLAuthor Commented:
what's the right way to cast that drop string so i can double check what it's doing?
0
Aneesh RetnakaranDatabase AdministratorCommented:
did you miss the  'BEGIN ... END '

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[ee_tablename]'))
BEGIN
  EXEC sp_Rename '[dbo].[ee_tablename]', @Object
  EXEC sp_rename 'tableNEW','ee_tablename'
END
0
dbaSQLAuthor Commented:
aneesh, the 2 renames work just fine.  it's the DROP that isn't happening
do you know how i would cast the DROP statement into a string, such that i can select/print it out and see what it's actually running?
0
dbaSQLAuthor Commented:
do you see my error, aneesh?  do you see why the DROP TABLE isn't happening?  or, better said, can you help me cast the string on the drop so i can see myself?
0
dbaSQLAuthor Commented:
i am still unable to drop that table.  the renames work just fine, but i can't drop that table
i was trying to cast the drop string so i could see what it's running, but i'm struggling syntactically
any suggestions?

DECLARE @table NVARCHAR(20)
DECLARE @Object NVARCHAR(200)
SELECT @table = Name FROM dbo.sysobjects WHERE LEFT(name,14)='ee_tablename_2'
SET @Object = 'ee_tablename'+'_'+ convert(varchar,dateadd(dd,0,getdate()),112)
IF EXISTS(select 1 from dbo.sysobjects where LEFT(name,14) = @table)
EXEC('DROP TABLE '+@table)
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[ee_tablename]'))
  EXEC sp_Rename '[dbo].[ee_tablename]', @Object
  EXEC sp_rename 'tableNEW','ee_tablename'
  GRANT SELECT ON dbo.ee_tablename TO username
0
dbaSQLAuthor Commented:
this really is becoming a problem, i must be able to dop this table nightly
is anyone able to advise?
0
shadowsaintCommented:
Unless you actually have a table named 'ee_tablename_2' nothing is going to be dropped the way you have things written.

I would maybe try something like
SELECT @sql = 'DROP TABLE dbo.ee_tablename_' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112)
EXEC(@sql)

this will then take the previous date formatted yyymmdd and tack it on to the end of the table.

You might want to do this in your check as well.
0
dbaSQLAuthor Commented:
well, the drop worked, but now my rename isn't happening.  surely it's a matter of syntax.
i need to rename the existing ee_tablename to ee_tablename_YYYYMMDD, using today's date
i need to rename tableNEW to ee_tablename
i need to drop ee_tablename_YYYYMMDD, where it is yesterday's date

DECLARE @sql VARCHAR(2000)
DECLARE @Object NVARCHAR(2000)
SET @Object = 'ee_tablename'+'_'+convert(varchar,dateadd(dd,0,getdate()),112)
IF EXISTS(select 1 from dbo.sysobjects where LEFT(name,14) = @object)
  EXEC sp_Rename '[dbo].[ee_tablename]',@Object
  EXEC sp_rename 'tableNEW','ee_tablename'
  GRANT SELECT ON dbo.ee_tablename TO username

SELECT @sql = 'DROP TABLE dbo.ee_tablename_'+CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()),112)
EXEC (@sql)
--PRINT (@sql)


do you see the right way to step through this?
0
shadowsaintCommented:
This should do it:
exec sp_rename 'table to rename', 'new table name'
--Rename Table With Current Date
DECLARE @sql varchar(4000),
	@date varchar(8)
 
SELECT @sql = '
EXEC SP_RENAME ''ee_tablename'', ''ee_tablename_@date'''
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR,GETDATE(),112))
EXEC(@sql)
 
--Rename New Table
EXEC SP_RENAME 'tableNew', 'ee_tablename'
 
--Drop Yesterdays Table
SELECT @sql = 'DROP TABLE dbo.ee_tablename_' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112)
EXEC(@sql)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbaSQLAuthor Commented:
PERFECT!  thank you very much
0
shadowsaintCommented:
Anytime! =)
0
dbaSQLAuthor Commented:
hey shadowsaint, can you help me get an if exists on that drop table?  even on the renames?
0
shadowsaintCommented:
This should get you on the right track...
might take some playing around to get it to work properly but thats the basic format.
SELECT @sql = '
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = ''ee_tablename_'' + CONVERT(varchar, DATEADD(d, -1, GETDATE()), 112))
BEGIN
DROP or RENAME code (you dont have to assign the code to sql var in here.)
END'
[place any replace code here]
EXEC(@SQL)

Open in new window

0
dbaSQLAuthor Commented:
i did this last night, seemed to work out pretty well.  what do you think?

/* drop/rename several tables */
DECLARE @sql1 varchar(1000),
    @sql2 varchar(1000),
    @sql3 varchar(1000),
    @sql4 varchar(1000),
    @date varchar(8),
    @tablename varchar(25)
 
/* Drop Yesterdays Table */
SET @tablename = 'ee_tablename_'+CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112)
SELECT @sql1 = '
IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = '+CHAR(39)+@tablename + CHAR(39)+')
DROP TABLE ee_tablename_'+CONVERT(VARCHAR,DATEADD(d,-1,GETDATE()),112)

/*  Fill tableNEW  */
SELECT @sql2 = '
SELECT * INTO tableNEW FROM database.dbo.ee_tablename WHERE tsdate >= convert(varchar, dateadd(d,-7,getdate()), 112)'

/* Rename table w/current date */
SELECT @sql3 = '
IF EXISTS(SELECT 1 FROM database..sysobjects WHERE NAME = ''ee_tablename'')
EXEC SP_RENAME ''ee_tablename'', ''ee_tablename_@date'' '
SELECT @sql3 = REPLACE(@sql3, '@date', CONVERT(VARCHAR,GETDATE(),112))

/* Rename New Table  */
SELECT @sql4 = '
IF EXISTS(SELECT * FROM database..sysobjects WHERE NAME = ''tableNEW'')
EXEC SP_RENAME ''tableNew'',''ee_tablename'' '

 
--PRINT(@sql1 + @sql2 + @sql3 +@sql4)
EXEC(@sql1 + @sql2 + @sql3 + @sql4)
0
shadowsaintCommented:
i wouldnt execute them all at the end like that personally, if it works great but i find it neater to execute after each step (easier for debugging)
and probably would put in begin and end after my if exists

I cleaned up what you have written but its just me being picky :)

Other then that it looks good
/* drop/rename several tables */
DECLARE @sql varchar(1000),
		@tablename varchar(25)
 
/* Drop Yesterdays Table */
SET @tablename = 'ee_tablename_'+CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112)
 
SELECT @sql = '	IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = @tablename AND xtype = ''U'')
				BEGIN
					DROP TABLE ee_tablename_@date
				END'
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR(8),DATEADD(d,-1,GETDATE()),112))
EXEC(@sql) -- Change to SELECT(@sql) to view code in text view. nice for debugging certain sections
 
/*  Fill tableNEW  */ -- Shouldnt need to put this in dynamic sql :)
SELECT *
INTO tableNEW
FROM database.dbo.ee_tablename
WHERE tsdate >= convert(varchar, dateadd(d,-7,getdate()), 112)
 
/* Rename table w/current date */
SELECT @sql = '
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE NAME = ''ee_tablename'' AND xtype = ''U'')
BEGIN
EXEC SP_RENAME ''ee_tablename'', ''ee_tablename_@date''
END'
SELECT @sql = REPLACE(@sql, '@date', CONVERT(VARCHAR,GETDATE(),112))
EXEC(@sql) -- Change to SELECT(@sql) to view code in text view. nice for debugging certain sections
 
/* Rename New Table  */
SELECT @sql = '
IF EXISTS(SELECT * FROM database..sysobjects WHERE NAME = ''tableNEW'' AND xtype = ''U'')
BEGIN
EXEC SP_RENAME ''tableNew'',''ee_tablename''
END'
EXEC(@sql) -- Change to SELECT(@sql) to view code in text view. nice for debugging certain sections
 
-- God im such a picky coder lol but this is how i would have written it :D

Open in new window

0
dbaSQLAuthor Commented:
I keep getting this:

Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@tablename'.
0
dbaSQLAuthor Commented:
got it.  the 1st @sql, @tablename needed to be handled differently

SELECT @sql = 'IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = '+@tablename+' AND xtype = ''U'')
      BEGIN
         DROP TABLE tablename_@date
      END'

PRINTs out correctly:
IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = tablename_20080206 AND xtype = 'U')    BEGIN      DROP TABLE tablename_20080206    END


>>if it works great but i find it neater to execute after each step (easier for debugging)
This makes sense.  I guess I am concerned about flow control, though.  If any step fails, can it all rollback?
0
shadowsaintCommented:
Yeah i see my problem, i forgot a replace in that top one, i like replaces because i think its easier to read instead of a bunch of ugly open and close ' ' with + signs everywhere and trying to line everything up :P
all you would have to do is either keep the fix you have or after the date replace put in
SELECT @sql = REPLACE(@sql, '@tablename', @tablename)

and about it failing and rolling back im not sure, i know you can mess with the transaction log but i havent really had the need to mess with it.

This might be something worth looking into
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
0
dbaSQLAuthor Commented:
excellent.  thank you, shadow.  i will take a look.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.