Skip to content


Get synonym definitions for all databases in server

If you want to audit your enviroment to look at all your synonyms and see where they are pointing, you can use exec sys.sp_MSforeachdb to loop through databases, and even filter. It will save some coding. However, my research indicates it is probably a bad practice to rely on this undocumented function as it may have issues not forseen and fully tested.

Additionally, support may drop for it in the future. I recreated what I needed with a cursor to obtain all the synonym definitions into a temp table and display results.:

    create temp table for holding synonym definitions & list of DB

    if object_id('tempdb..#dblist') is not null
        drop table #dblist;
    into #dblist
        name not in ('master', 'tempdb', 'model', 'msdb')
        and State_desc = 'ONLINE'
        and Is_In_Standby = 0
    if object_id('tempdb..#temp') is not null
        drop table #temp;

    create table #temp
            db_name               sysname
            ,object_id             int
            ,name                  sysname
            ,base_object_name      sysname
            ,server_name_hardcoded as case
                when base_object_name like '%ThisDatabaseIsOkToHardCode%'
                then 0
                when len(base_object_name)
                        - len(replace(base_object_name, '.', '')) > 2
                then 1
                else 0


    declare @DbName sysname
    declare @XSQL varchar(max)
    declare @CompleteSQL varchar(max)
    declare db_cursor cursor fast_forward read_only local for select
    open db_cursor
    fetch next from db_cursor into @DbName;

    while @@fetch_status = 0
        set @XSQL = '
                    insert into #temp
                    ( db_name ,object_id ,name,base_object_name )
                        sys.synonyms s
        set @CompleteSQL = 'USE ' + @DbName
                            + '; EXEC sp_executesql N'''
                            + @XSQL + '''';
        exec (@CompleteSQL)
        fetch next from db_cursor into @DbName;

    close db_cursor
    deallocate db_cursor
        #temp t

Calculating the next beginning of month and the current end of month

Handling dates is always a fun challenge in T-SQL! Finding the current end of month and next months beginning of month is straight forward, but I like to find new ways to do things that take less coding, and hate date conversions that require a lot of manipulation of characters and concatenation. This was what I came up with for avoiding character conversions and concatenation for finding the current BOM (beginning of month) and EOM (end of month) values. Adjust according to your needs. Cheers!

    --use datediff from 0, ie default 1900 date, to calculate current months as int
    declare @ThisMonth int = datediff(month,0,cast(getdate() as date))

    --add 1 to the current month to get the next month
    declare @NextBom date = dateadd(month,@ThisMonth+1,0)

    -- subtract a day from the beginning of next month to get the current end of month, without worrying about 28, 30, or 31 days.
    declare @ThisEom date = dateadd(day,-1,@NextBom)
    select @ThisMonth select @NextBom select @ThisEom

Installing SSMS 2012 all by it's lonesome

SQL Server Management Studio (SSMS) is not offered as a standalone download on MSDN. Installation requires the user to download the sql server installation package and choose to install only this single feature. For developers, SQL Developer edition is a great choice.

Here's some screenshots to give you a guide on installing SSMS by itself when working with the full installer.

{{< admonition type="info" title="Updated: 2017-02" >}} This doesn't apply for future SSMS versions as they began (I believe with 2014) to package SSMS outside of the database engine installer, allowing continual iterations and improvements for SSMS outside of being included as part of SQL server patching (finally!). If you are still reading this.... get an updated SSMS! {{< /admonition >}}














snippet designate a certain time of the day in getdate()

Snippet to designate a certain time of the day to evaluate in the current day. If you need to limit a result to the current date after a particular time, strip the time out of the date, and concatenate the current time together with it, and then convert back to datetime2.

select convert(datetime2(0),cast(cast(getdate() as date) as varchar(10)) + ' 09:00 ')

Native SSMS a second class citizen no longer...

Intellisense can be a boon to adding quick development. Quick hints on scope specific variables, syntax suggestions, function descriptions and more provide a valuable tool to productive coding.Coding in SQL Server Management Studio (SSMS) has greatly improved over the version releases, but it still lags behind the power of Visual Studio's intellisense, template insertions with "fill in the blank" functionality .

Additionally, the lack of automatic indentation means that lining up sub-queries and levels of logic can be annoyingly time consuming... especially when not everyone on a team following the exact same coding standards. Legibility can easily suffer. Intellisense fails to properly update at times and reads pending statements as errors in syntax disabling the prompts from providing help. Automatic SQL formatting has been a long missed feature in SSMS. Trying to line up levels of nested queries can be very time consuming, and variances in the way people layout the query can effect readability.

As a developer, I'm constantly looking for ways to improve my coding experience and streamline repetitive bits of coding. One such tool is SSMSBoost which provides many valuable shortcuts and features that SSMS omitted. The SSMSBoost team provides a free community edition to their fellow developers and responds to feedback promptly. I'd highly recommend checking them out.

However, SSMS Intellisense improvement is by far the most powerful and productive coding tool that developers can look to improve. This tool enables quick hints to job the foggy mind missing that all important cup of espresso in the morning!

Cut above the competition

In my search for coding tools I've reviewed three main contenders in the arena: ApexSQL Complete, Red Gate SQL Prompt, and DbForge SQL Complete. For me, the best tool ended up being DbForge SQL Complete. The product fills in the gap on intellisense improvements. I've installed in SSMS 2012, which is an improved environment and developers can use while still working with SQL Server 2008R2.

ApexSQL Complete was offered free to the community with some powerful formatting capabilities. However, I was never able to get the formatting to truly match the clean results of Red Gate SQL Prompt and DbForge SQL Complete. Additionally, ApexSQL Complete had some lag issues originally which seem to have been improved in recent releases, but still seemed to get in the way more than the other tools. Their object info prompts were rudimentary and not formatted well.

Red Gate SQL Prompt is a fine product, and rates highly. However, the customization offered in the formatting profiles was very limited and seemed focused on simplicity rather than offering complete control. It also seemed to have some unpolished edges, such as typing INSERT INTO TABLE, would provide the template of columns and value upon accepting the intellisense prompt, but the insertion didn't follow the same formatting standards you had setup, and thus was harder to work with. It also was an expensive option compared to the others, with a Professional license running over $300+ for just a year of upgrades and support. DbForge SQL Complete offered the best solution for my needs. The amount of customization offered was incredible and the end result was a powerful formatting tool with amazing results, and a intellisense tool that truly improved my coding experience (enough that I wanted to share with others this tool!) This tool is also compatible with Visual Studio, so now those doing SQL work in Visual studio will have a great tool to help fill the deficiencies of VS for SQL development.

Before reviewing the tool, I'd also mention that DBForge offers a user vote/forum with which I've had feedback on almost every single issue right away. Their communication seems top notch, in additional to providing special programs and discounts for their products. One bug I found in the formatting was addressed with a new update within 2 weeks. That's a responsive company!

DB Forge SQL Complete

This review is performed on the full version. An express version is also offered to the community as a service. This tool has a portion of the full version's functionality to whet the eager developer's appetite. A free trial is offered on the full version... but beware you won't be able to be satisfied with native SSMS features after this!

General Options - The Normally Boring Stuff That's Not Quite Boring

Improvements to the SSMS Coding environment are shown. A shortcut to refresh the cache of intellisense, formatting options, a command to execute the currently selected statement, outline the structure of the document, snippet management, and feedback. I'll review only the portions that seem to have the greatest impact, as most of it is self explanatory.


Of particular note is the "automatically trigger after" option. For users that want intellisense to help out only after a long pause and not constantly change while typing, this option is helpful. Setting a longer time on the ms will keep the box from showing up unless a command is pressed to initiate the intellisense showing up (Default: Control-Space).

The highlight occurrences option is a great feature added by Devart. When a particular object is selected in the query editor window, other occurrences are highlighted for easy viewing.



Automatic alias generation is another boon to the developer. SQL Complete is will automatically generate an alias for you upon object selection from intellisense. This also means that column selections later on will be correctly assign the alias, reducing typing. select * from company c -- this alias was auto generated when selected


Seamless Shortcuts provide SSMS Satisfaction

Alias generation is powerful, but overshadowed by one of the simplest yet much needed features that SSMS Native Intellisense lacks: column selection and wild card expansion.

Dragging the column names from object explorer results in a list of columns delimited by commas, but not stacked vertically, it also doesn't handle multiple table columns being dragged at the same time, or a "limited selection of columns."

SQL Complete fills this gap. Pressing tab allows expansion of columns in a stacked list, with alias's already assigned correctly. The column select appends to the same line, but with the automatic formatting tool, the finished list is easily stacked vertically with the press of the format shortcut. These are simple examples, but I've found to extremely helpful when working with complex queries involving numerous tables. Expanding all the columns available or selecting, with correct alias generation is a major improvement to the SSMS development environment




Insert statements are "auto completed" helping provide quick statement generation, this is a lifesaver!



Prompts also help developers know the table structure of objects/views, stored procedure parameters, and function usage. Might save some folks MSDN googling just to remind themselves about the correct function usage!



Compare this to the native function prompt:


Snippet Templates are made a reality

<enter mind blown event> Snippet tools are helpful for saving some typing, but until now they've been typically limited to just pasting in text. The full functioned snippet functionality of Visual Studio with field selection by navigating with tab hasn't existed in SSMS.... till now.

Snippets are brought to us with SQL Complete with an amazing thought to detail. Not only are the snippets brought into our editor window, but are also displayed in intellisense, with"fields" provided to navigate to to "fill in the blanks." Variables for customization are offered and the sky is the limit here!



Below I demonstrate a cursor snippet. The only keystrokes I used to complete this action were:

cu + Tab @MyCustom + Tab + custom_name_i_entered_in_one_place Total Keystrokes = 48 (10.9% Cost ) Total keystrokes without addin (and no mistakes) = 440 (89.1% cost) Would you keep an execution plan version that was 89% higher cost... 392 keystrokes later?


Formatting made painless

Formatting SQL code manually can be a pain. There are online tools offered with some limited functionality, but who really wants to use a web based formatting tool with production code? Addins for SSMS formatting are limited in number. Parsing the logical construct of a SQL statement isn't quite a simple as indenting a foreach loop in C#. SQL Complete offers one of the best SSMS SQL formatters I've come across.

The level of customization is intense. A few screenshots are provided, but I will not go into all the levels of customization, instead I'll provide you with some MSDN code samples that SQL Complete beautified. Running the format can be done on a selection or an entire document. Settings can be saved so a team could easily share a formatting standard. This would prevent each person from having to configure all their settings manually, a major plus in setup for any team!



Ugly Duckling SQL turned into the beautified SQL you'd be proud of



Here are some examples of the formatting tool at work in order from a simple query to a complex recursive CTE. I believe this tool handles it beautifully!







After: *line breaks handled with union's






Final Thoughts

Additional features are available, such as automatically finding object in object explorer when selected in query window, outlining of document, contextually based join statements, and more. I covered only the features I considered the highest impact on my workflow. Finding a tool to enhance the development process in SSMS should be something SQL developer's consider.

Once you utilize a tool like this, you'll find that the improvements help you focus on the more important things. Spending time thinking about "lining" up code is a waste of time when a developer could be focused on the content. Formatting all code to be lined up the same with a press of your shortcut keys also helps you not focus on trying to discern the formatting standards of others, and instead get down to the important work! The continual improvements and responsiveness of Devart to feedback and resolving issues I had makes me give their product a two thumbs up award!


I do not work for Devart or any competing product. I reviewed this program since I've been sold on the value of it, and wanted to participate in their High Five program which helps developers that like their products share with others, and be eligible for discount/reward if a review of their product is completed. I believe the tool to be useful, and has improved my experience with SSMS. I hope other SQL developers can benefit, especially those who were unaware of the availability of such a great addin.

dynamic sql and a char crash

Dynamic SQL can be helpful, but a pain to debug. I spent hours today working on figuring out why my simple date comparison in dynamic SQL wasn't working. Found out that the remote database I was connecting to had a char date instead of a datetime. I found the comparison of CHARDATE > VARCHARDATE failed to error out, but also failed to give a proper result set. Changing the look-up to ensure both dates were converted to date fixed the issue. During this debugging I was reviewing my dynamically created SQL statement.

I learned that SSMS limits the amount of text it will return. In trying to view the single large UNION ALL statement, I was experiencing truncated results. I wanted to ensure the code being executed looked proper, but couldn't get past the truncation.

Enter SSMSBoost to the rescue!

SSMSboost is created by developers and very responsive to requests. I'll do a proper review soon. They offer a visualize data option that goes beyond the usage I employed. For my purpose, I clicked on the cell and selected visual cell as text, and opened the data in notepad++. This showed the full text without truncation. I was able to move on in my debugging then as I knew the dynamic sql statement was not actually truncated except to my SSMS output. Dynamic SQL is a great tool, but if I had been working with direct queries, the issue would have been much faster to resolve!

How to default SSRS date parameters to the first and last day of the the previous month

Populating default dates in SSRS can be helpful to save the user from having to constantly input the date range they normally would use. When a report is pulled for last month's information, defaulting the date fields for the user can help streamline their usage of the report, instead of them manually selecting with the date-picker control in SSRS. The formula's I used were:

Beginning of Current Month (EOM) DateSerial(Year(Date.Now), Month(Date.Now), 1)
Beginning of Last Month (BOM) DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))
End of Last Month (EOM) DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

To set the default date of the parameters:

  1. First open up the Report Data Window, and choose your date parameters.
  1. Navigate to Default values, and click the Fx button to edit the expression for the field.
  2. Paste the formula into the expression field and save.

Result: Your default dates should now show last month's date range. You can apply your own rounding or date types if you wish, this provides the time as well, since I was working with smalldatetime, datetime, and datetime2 datatypes.