August 6, 2010 at 1:27 pm
I'm doing some clean and would like to automate it. I would like to drop temporary tables. I have the following script. I want to execute the actual sql statements placed in the temporary table at the end of the script. However, it is not working for me. Any suggestions? I've spent hours trying different suggestions online.
Thank you!
Create TABLE #SQLCmd
(
SqlCmdLine varchar(max)
)
BEGIN TRANSACTION
INSERT INTO #SQLCmd (SqlCmdLine)
SELECT
'DROP TABLE ' +
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +
'.' +
QUOTENAME(o.name)
FROM sys.partitions AS p INNER JOIN
sys.indexes AS i INNER JOIN
sys.objects AS o ON i.object_id = o.object_id INNER JOIN
sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id ON p.object_id = o.object_id
WHERE (i.index_id < 2)
AND (o.is_ms_shipped = 0)
AND (o.name LIKE '%temp%')
--AND (o.create_date <= GETDATE() - 7)
ORDER BY o.Name
select * from #SQLCmd
-- NOTHING WORKS BELOW THIS LINE
exec 'Select SqlCmdLine from #SQLCmd'
--Declare @runsqlcmd varchar(4000)
--Select @runsqlcmd = 'Select SqlCmdLine from #SQLCmd'
--exec (@runsqlcmd)
Drop Table #SQLCmd
¤ §unshine ¤
August 6, 2010 at 1:58 pm
-- NOTHING WORKS BELOW THIS LINE
exec 'Select SqlCmdLine from #SQLCmd'
Can you try this?
exec ('Select sqlcmdline from #sqlcmd') ?
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 6, 2010 at 2:07 pm
I get the result set of what is inside the table twice. The temp tables are still there. 🙁
¤ §unshine ¤
August 6, 2010 at 2:24 pm
sunshine-587009 (8/6/2010)
I get the result set of what is inside the table twice. The temp tables are still there. 🙁
Yes. I didnot notice that. Is there a need of storing this in the #table? Instead you can directly store the output of the 'drop' t-sql to a variable and then execute it using execute (@var).
[font="Arial"]BASKAR BV
http://geekswithblogs.net/baskibv/Default.aspx
In life, as in football, you won’t go far unless you know where the goalposts are.[/font]
August 6, 2010 at 2:53 pm
I found this script here for something else and it works...
Thank you so much for your help! 🙂
DECLARE @TEMP TABLE
(
ID INT IDENTITY(1, 1),
OBJECT_NAME VARCHAR(100)
)
INSERT INTO @TEMP ( OBJECT_NAME )
SELECT
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) +
'.' +
QUOTENAME(o.name)
FROM sys.partitions AS p INNER JOIN
sys.indexes AS i INNER JOIN
sys.objects AS o ON i.object_id = o.object_id INNER JOIN
sys.dm_db_partition_stats AS ddps ON i.object_id = ddps.object_id AND i.index_id = ddps.index_id ON p.object_id = o.object_id
WHERE (i.index_id < 2)
AND (o.is_ms_shipped = 0)
AND (o.name LIKE '%temp%')
--AND (o.create_date <= GETDATE() - 7)
ORDER BY o.Name;
DECLARE @tablename VARCHAR(100)
DECLARE @counter INT
SET @counter = 1
DECLARE @max-2 INT
SET @max-2 = ( SELECT MAX(id)
FROM @TEMP
)
WHILE @counter <= @max-2
BEGIN
SET @tablename = ( SELECT OBJECT_NAME
FROM @TEMP
WHERE id = @counter
)
EXEC ( 'DROP Table ' + @tablename
)
SET @counter = @counter + 1
END
END
¤ §unshine ¤
August 7, 2010 at 1:59 pm
Create TABLE #SQLCmd
(
SqlCmdLine varchar(max)
)
...
-- NOTHING WORKS BELOW THIS LINE
exec 'Select SqlCmdLine from #SQLCmd'
...
Dynamic SQL is executed in a separate scope than the batch. The temp table #SQLCmd is created outside of the dynamic sql, therefore #SQLCmd is falling out of the scope of the dynamic sql.
Besides the solution you mentioned above, you can also try to use global temp table instead of local temp table to solve the problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply