SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Drop table using statement store in a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
226 Posts

Posted - 05/20/2013 :  05:16:13  Show Profile  Reply with Quote
Hi All,

I have a
table name: TempTablesList
column: Tables

select tables from TempTablesList will return me a list of result:-

drop table temp_AAAA
drop table temp_BBBB
drop table tblArch_Abu
drop table tblArch_Ali


How can i execute this drop table value to drop all the tables listed in the tables at TempTableList table?

Please advise.

Thank you.

Regards,
Micheale

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/20/2013 :  05:47:49  Show Profile  Reply with Quote
you have to run as dynamic statement....
--try this..
DECLARE @SQL VARCHAR(MAX) = ''
SELECT @SQL = @SQL +'; ' + tables FROM TempTablesList;
SET @SQL = STUFF(@SQL, 1, 2, '')
EXEC (@SQL)

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/20/2013 :  07:14:14  Show Profile  Reply with Quote
just do a statement like

SET NOCOUNT ON
GO
SELECT 'DROP TABLE '  + Tables
FROM TempTablesList


choose output as text option in SSMS and execute the above query

in output tab copy the output obtained which will be list of drop statements and paste it into a new window connecting to required db and execute.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 05/20/2013 :  07:23:00  Show Profile  Reply with Quote
Hi visakh,
I think OP has "DROP Statements" in the column Tables of TempTableList (OPs title is statement stored in a table)

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.23 seconds. Powered By: Snitz Forums 2000