Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 1,818,370 hits
  • Select GETDATE()

    October 2020
    M T W T F S S
     1234
    567891011
    12131415161718
    19202122232425
    262728293031  

Posts Tagged ‘ssms tips and tricks’

Tips and Tricks : Delete Complete History and Settings of Management Studio

Posted by blakhani on July 15, 2014


How many times you have browsed internet and cleared complete history of Internet Explorer (or any other browser)? I have done it many times and due to various reasons. Few days back one of my friend asked that how can same thing be achieved in SQL Server 2014 Management Studio? His goal was to delete all the saved setting of management studio for his login. This would include:

  • Server List and Login credentials saved on login screen.
  • History of reports launched – (Read Pinal’s blogs)
  • Settings change in Object Explorer Details like sorting and ordering of columns.
  • All setting which are saved under Tools > Options in menu bar.

(This does NOT effect registered severs list)

This all information is saved under file SQLStudio.bin.

Below is the file in SQL 2012 Management Studio. (Go to start>run> and paste below path)

%appdata%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

Below is the file in SQL 2014 Management Studio.

%appdata%\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

image

In earlier version of SQL Server (before SQL 2012), the path was different. Refer Jonathan’s blog

Hope this helps.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Management Studio, SSMS | Tagged: , , , , , | Leave a Comment »

    SSMS Tip: How to drop multiple objects?

    Posted by blakhani on June 3, 2014


    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.

    image

    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)

    image

    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”

    image

    Once that’s done, I can choose the position of the column by “dragging” the column name

    image

    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?

    image

    And once we choose delete, we get “Delete Objects” dialogue box which is similar to single delete.

    image

    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

    image

    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.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Posted in SQL Server Management Studio, SSMS | Tagged: , , , , , , , | 1 Comment »