November 14, 2010 at 11:39 pm
I want to drop the tables has the following name but i am not able to drop the table by using variable please provide any help?
declare @top1 as varchar(20),
@sql varchar(100)
set @top1 = (select top 1 name from sys.tables where name like 'tran%' order by name desc)
print @top1
/*
begin
drop table @top1
end
*/
select @sql = "DROP TABLE " + '@top1'
exec (@sql)
go
November 15, 2010 at 2:46 am
DECLARE @ToDrop SYSNAME,
@sql NVARCHAR(MAX);
SET @ToDrop =
(
SELECT TOP (1)
T.name
FROM sys.tables T
WHERE T.[schema_id] = SCHEMA_ID(N'dbo')
AND name LIKE N'tran%'
ORDER BY
T.name DESC
);
IF @ToDrop IS NOT NULL
BEGIN
SET @sql = N'DROP TABLE [dbo].' + QUOTENAME(@ToDrop) + N';'
-- Show the string
PRINT @sql;
--EXECUTE (@SQL);
END;
ELSE
BEGIN
RAISERROR('Table not found.', 16, 1);
END;
November 15, 2010 at 4:48 am
thanks......
November 15, 2010 at 5:51 am
I wonder is it sensible to use dynamic SQL for deleting a table considering the dangers of SQL Injection.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 16, 2010 at 8:00 am
When you execute the SQL use sp_executesql instead of simply exec.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply