Drop Temp Tables from INFORMATION_SCHEMA.TABLES

• bpowers

  SSCertifiable

  Points: 5483

  More actions

  October 5, 2014 at 11:49 am

  #299855

  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

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  October 5, 2014 at 2:08 pm

  #1750198

  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

• bpowers

  SSCertifiable

  Points: 5483

  More actions

  October 6, 2014 at 11:00 am

  #1750358

  That was it Thanks so much! Any idea how I can run this across three test databases in one piece of code?

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  October 6, 2014 at 1:39 pm

  #1750400

  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;

• bpowers

  SSCertifiable

  Points: 5483

  More actions

  October 6, 2014 at 4:08 pm

  #1750447

  Thanks a million!