Have you even been into situation where you have to drop many tables in a database? Or drop multiple databases? As a developer, you might right click on each table/database and choose “delete”. If you are equipped with little T-SQL knowledge, you might write a script to do the same. Do you know that management studio can do it?
Let’s create multiple databases and learn the trick. I have used NEW_ID to get completely random name of databases.
declare @number_of_databases int, @loop int select @number_of_databases = 10 select @loop = 1 declare @random_name varchar(40) declare @str nvarchar(100) while (@number_of_databases >= @loop) begin select @str = N'Create database [' + convert(varchar(100),NEWID()) +N']' exec sp_executesql @str set @loop=@loop+1 end
Once the script if executed, I have below in my management studio. Your databases would definitely be different.
Now, my task is – drop those databases which are created today. Without knowing the trick, one would get all databases name by query and then right click on database, choose delete. That would open a new window and we would choose “close existing connection..” and again hit OK. This has to be done 10 times. Now, here is my way of doing it.
Click F7 on keyboard when SSMS is open. This would open “Object Explorer Details”. You can also use “View” in menu bar and choose “Object Explorer Details” item there. This would open a interface which is “details” on the object selected in “Object Explorer” . I would refer this as OED in later part of the blog. (OE of left and OED on right)
In OED, we have capability to sort the item based on various columns available. I want to sort databases them using date created. But by default that column is not shown. Let’s Right click on column header in OED and choose the column which we are interested. I have selected “Date Created”
Once that’s done, I can choose the position of the column by “dragging” the column name
Now, we can SORT as well by clicking on column name. I have sorted them by date created and as we can see all databases which I have created by script have listed next to each other. OED has capability the select multiple objects. This can be done by:
- Use Shift key to choose all objects next to each other.
- Use Ctrl key to choose multiple object using mouse
Once desired objects are selected, use right click and choose delete. Simple. huh?
And once we choose delete, we get “Delete Objects” dialogue box which is similar to single delete.
We can choose the desired option and hit OK.
Same operation would work for tables as well. We just need to choose proper node on “Object Explorer” and get details under “Object Explorer Details” – OED
Again, choose multiple tables in OED, right click and delete.
I always felt that SQL Server Management Studio is one of “unexplored” and “underestimated” tool in SQL Server Product. Hope this blog has shown a little “hidden” gem of SSMS. If you know any such trick, please use comment section to share your knowledge.
Help: SQL Server 