October 15, 2020 at 5:14 am
i need to delete specific tables "_backup " which took before changes to the table, instead of deleting the table one by one , any script is there to complete the task at ones.
October 15, 2020 at 7:24 am
You have to query the system tables for the list of tables matching your criteria, and then use a cursor to delete them (because you have to do it one at a time).
This is crazy ugly, but it works... maybe it will give you some ideas.
DECLARE @objName SYSNAME;
DECLARE @cursor_Tables AS CURSOR;
SET @cursor_Tables = CURSOR FOR
SELECT ao.[name] as objName
FROM sys.all_objects ao
WHERE ao.name LIKE '%_backup'
AND type = 'U';
OPEN @cursor_Tables;
FETCH NEXT FROM @cursor_Tables INTO @objName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON;
PRINT @objName;
--BACKUP DATABASE @name TO DISK = @fileName
DECLARE @sql NVARCHAR(MAX)
SET @sql = 'DROP TABLE dbo.' + QUOTENAME(@objName);
print @sql;
--EXEC sp_executesql @sql;
FETCH NEXT FROM @cursor_Tables INTO @objName;
END
close @cursor_Tables;
deallocate @cursor_Tables;
October 15, 2020 at 8:34 am
Is the query will provide the drop syntax of all the tables _backup or it will delete the tables on a database .
October 15, 2020 at 9:51 am
The query already drops tables in the query at the top.
It fetches each table name 1 by 1 out of the query and executes a drop table + table name
SELECT ao.[name] as objName
FROM sys.all_objects ao
WHERE ao.name LIKE '%_backup'
AND type = 'U';
I want to be the very best
Like no one ever was
October 15, 2020 at 5:37 pm
i need to delete specific tables "_backup " which took before changes to the table, instead of deleting the table one by one , any script is there to complete the task at ones.
Step 1: If it's a one time thing (i.e. not needed as part of a stored procedure and is rather ad hoc in nature), open SSMS, press the [F8] key to open the Object Explorer window (if it's not already open), expand the database in question, right click on tables, select [FILTER SETTINGS], enter the criteria for the table name, and press [OK].
Step 2: Click on tables in the Object Explorer again and then press the [F7] key. The Object Explorer Details window will open with only all of the filtered table names. Review them to make sure that you're not getting to drop something unexpected. Highlight all of the table names and then do a [CTRL] click to deselect any of the filtered tables in the list you may want to keep. Again, review your selections to be sure.
Step 3: Press the [DELETE] key and confirmation window will appear. Last time to check and make sure. If you're positive as to the tables listed to be deleted, click [OK] and all of the selected tables will be deleted. If the system is not able to delete a table, it will be listed as an error.
Strep 4: Please don't take this as being a smarty pants or a blast on you. While I agree that you should learn how to do a lot of things in code, SSMS can do many amazing things with just several clicks that can make such ad hoc tasks quite simple. I strongly urge folks to read the documentation about SSMS and seek out presentations and articles on the subject of SSMS and tricks in SSMS and useful things in SSMS because it can make your life pretty easy. In a lot of cases, it'll also generate a script of changes that you can copy and paste into SSMS for further study provided that you understand that it frequently adds some totally unnecessary stuff to the script (like 3 or 4 USE statements that don't seem to make any sense at all). It will also sometimes generate way too much like if you use it to do some simple table partitioning. If you're not aware, it frequently generates an additional command to undo all the partitioning it just generated code for and will sometimes even have code to drop the table that the other code just created. ALWAYS CAREFULLY STUDY SUCH GENERATED CODE FOR SUCH NUANCES.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply