Custom SSMS

  • Comments posted to this topic are about the item Custom SSMS

  • I am working in an environment where I need to connect to multiple SQL Server instances to do all sort of checks and queries. From SQL Server 2000 (yep, still running here) up to SQL Server 2012!

    In SSMS, Click on View, and select Registered Servers. I have setup all the different instances that I use on a daily basis and group them in functional groups.

    What is nice about this feature is you can color code the instances and when logged onto an instance and opening up a Query window, you will see that color and you can identify with that.

    Another nice feature is that you can run a query which will execute on all Instances in a group, like: SELECT @@VERSION

    Above will return 2 columns, Server Name and column with SQL Server instance version.

  • One trick I find extremely useful is to set up a query short cut with "Select Top 1000 * From".

    Then when I'm investigating a complex query I can simply highlight an individual table name from that query (optionally including database name/schema name), hit Ctrl+4 and I get an instant sample of the data from that table.

    This can be set up via Tools\Options\Environment\Keyboard\Query Shortcuts in SSMS.

  • I have an old version of Redgate SQLPrompt running and I also installed Mladens SSMS Tool pack.

    The only thing I change myself in SSMS is to make sure CTRL+R works to hide the result pane (got somehow wacked in the latest versions) and I set the scripting options to my liking.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I've found that, after being caught out a couple of times, ensuring 'script permissions' (Tools>Options>SQL Server Object Explorer > Scripting > Object Scripting Options > Script Permissions) is enabled is now one of the first things I check.

    RedGate SQL prompt makes working on databases you are unfamiliar with a breeze and the auto format function is great for cleaning up tsql ready for adding to source control.

    Equally 'Tab history' by redgate (I think in the dba bundle) allows you to quickly go through the history of what you have written, even if you did not save it:

    handy for getting back bits of scripts you wished you would have saved!

  • I don't use a lot of custom settings but one thing I do is to set my default isolation level to read uncommitted

  • Hi, some useful SSMS tips being shared here...

    Whenever I'm faced with a virgin install of SQL Management Studio, I usually apply below changes:-

    - Register Servers: create server groups for production, dev, uat, sit environments

    for prod server registrations I colourise the toolbar to bright red to provide visual warning! (Server Registration Properties/Connection Properties/Use custom color)

    - Re-use templates - copy useful sql scripts to folder ( e.g. "C:\Documents and Settings\<UserName>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql )

    - Install Redgate SQL Search - it's free :-), and very useful!

    Cheers

    T

  • I'm the opposite to what Steve Jones comments. I use a heavily modified SSMS via lot of plugins and 3rd part tools and ofc a mix of previous tips:

    - own paid version of SSMSToolsPack from Mladen Prajdic (running on SQL2012): really useful for lot of tasks, it has configurable snippets, tsql execution full history, ordered by independent queries, by tab and set of tabs (I've everything I've run since I started on current position, 1 year already), checks for missing WHERE when running CRUD operations (who hasn't ran a delete or update without where?)

    - RedGate:

    SQL Prompt: it overlaps a lot with SSMSToolsPack so from here I only use couple of features, but I recon is really useful too.

    Tab History: quicker and has more details than the one from SSMSToolsPack

    Search tool: who can live without it?!

    - SSMS Boost: free version, just need to download and install once each 6month. Highly configurable connections settings, coloring, default databases, naming, etc. With this option I avoid having tons of tabs opened, just change connection on a dropdown and automatically connects, change colors, add a small warning in case of prod environment or staging or dev. Also it adds tons of option when right-clicking on results grids to export to hell a lot of formats (HTML, excel, csv, xml) and automaticall generates scripts as INSERT INTO, SELECT WHERE * and more

    - SQL Code Guard: when dev's upload their changes, easy to detect non standard use or miss use of NOLOCKS, objects naming, etc, etc.

    - as other already commented: extensive use of registered servers, together with CMS so anywhere I connect I have access to the different groups of servers and/or instances, staging, test, prod, etc, etc. It really helps on lot of tasks, changing same job on 8 servers for example, getting stats for a particular process from all servers at once, etc

    I think that's all :w00t: 😀

  • Customized T-SQL templates.

    Wayne Sheffield did a presentation on SSMS customizations and timesavers at SQL Saturday in December. It's available at his blog. Look for SSMS Tips & Tricks.

    ____________
    Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.

  • Hi,

    This is my first Post since I'm registered in SQLServerCentral.

    I use:

    - Registed Servers to keep my usual connections in hand.

    - Change the SSMS Font to a lower size monospaced (ProggyCleanTT, size 12).

    - The very useful Red Gate SQL Search add-in (free).

    - The indispensable SSMSBoost (free). This one is a must have. Hit Ctrl + F2 in an object name and it automatically locates the object in the object explorer, and much more...

    Cheers,

    NS

  • Besides RedGate's SQL Prompt, we use AutoHotKey to automate certain key sequences. It is not a SSMS add-in but rather a Windows app, but we use it exclusively for SQL queries. The one I use the most is used to copy a select number of data elements from a query, then using AutoHotKey to "paste" the data as a list with single quotes and commas surrounding each data element. It also allows us to take data from other sources (MS Excel, flat files, etc.), copy select fields and paste them into a SQL query for a quick analysis (versus importing data).

    Joe

    Fred

    Mike

    becomes

    'Joe',

    'Fred',

    'Mike'

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • You can also use QUOTENAME for this, but it does sound interesting!

    SELECT QUOTENAME(Name, '''' ) + ',' FROM Users

  • I use a parameterized SP template for consistency and to ensure that everyone enters the same info.

  • I have SSMSBoost installed Since for 2012 need a paid version of SSMS Toolbox and I didn't feel like asking for one.

    I also use ApexSQL's SQL Complete and SQL Refactor I believe. Maybe their search too.

  • I wrote the T-SQL Flex SSMS add-in to help script selected data to INSERT statements, get highly accurate schema for data returned (including for arbitrary queries, functions, and SPs), and to do Excel export that doesn't destroy formatting for dates, leading zeros, etc.

    I find T-SQL Flex indispensable for someone like me who is more of a developer than a DBA. There are not a lot of bells and whistles like the amazing SSMS Tools Pack or SSMS Boost, but it is open source (MIT) and completely free.

    Get it here: https://github.com/nycdotnet/TSqlFlex

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply