October 5, 2014 at 11:49 am
I am trying to remove some temp tables our application created from the INFORMATION_SCHEMA.TABLES. I have restored a backup copy of our production db to our test instance and before I can sync the logins I have to remove these tables. I can do so by opening the object explorer details window, selecting the files, and then delete. However, when I script this I get the error "Cannot drop the table 'TEMP2988_RC144123847', because it does not exist or you do not have permission." I have tried using my admin account, as well as logging in under the sa account. Still no luck. Any ideas what I am missing? Any help will be appreciated.
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT 'drop table [' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'MYAPPLICATION_%'
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
October 5, 2014 at 2:08 pm
Quick thought, if the table/object is not in either the user's default schema or in the dbo schema, the reference to the table must be fully schema qualified
😎
(Changes in bold)
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'MYAPPLICATION_%'
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
October 6, 2014 at 11:00 am
That was it Thanks so much! Any idea how I can run this across three test databases in one piece of code?
October 6, 2014 at 1:39 pm
bpowers (10/6/2014)
That was it Thanks so much! Any idea how I can run this across three test databases in one piece of code?
Quick suggestion
😎
DECLARE @DB TABLE(DBNAME NVARCHAR(128));
INSERT INTO @DB(DBNAME)
VALUES (N'DB_1'),(N'DB_2'),(N'DB3');
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
DECLARE @DB_STR NVARCHAR(MAX) = N'
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT ''drop table ['' + TABLE_SCHEMA + ''].['' + TABLE_NAME + '']''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE ''MYAPPLICATION_%''
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds;
';
SELECT @SQL_STR = (
SELECT
N'USE ' + DB.DBNAME + N';
' + @DB_STR
FROM @DB DB
FOR XML PATH (''), TYPE).value('.[1]','NVARCHAR(MAX)')
SELECT @SQL_STR;
--EXEC (@SQL_STR);
Results
USE DB_1;
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'MYAPPLICATION_%'
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds;
USE DB_2;
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'MYAPPLICATION_%'
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds;
USE DB3;
DECLARE @cmd varchar(4000)
DECLARE cmds cursor for
SELECT 'drop table [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'MYAPPLICATION_%'
OPEN cmds
WHILE 1=1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds;
October 6, 2014 at 4:08 pm
Thanks a million!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply