Tag Archives: SSMS

Multi-server scripts with SSMS using SQLCMD mode

SSMS is the most common tool used by SQL Developers or DBA’s but unfortunately there are some features that are barely known, SQLCMD mode is not the exception and that’s why I decided to follow up in this topic.

You may want to check one of this previous blog post, where you will find instructions how to enable SQLCMD mode and some examples that could help you to become familiar with it.

Continue reading

SQLCMD mode in SSMS

I really like Microsoft philosophy nowadays, they are putting a lot of time developing good open source tools like SQLCLI, SQLOps and Visual Studio Code, those tools has something in common “a built-in terminal” a multi-platform and code geek like me really appreciate those details. It is pretty neat to use the built-int terminal to do stuff without leaving your development enviroment.

This functionality is also available on SSMS (in some way), it is called SQLCMD mode. This feature allows you to interact directly with Windows command line without leaving your SSMS environment, for example long time ago I had the need to generate a SQL file from an output of a query, I knew I can use SQLCMD for such thing but I didn’t want to create an external script to be call from command … so I did my research and figure out SQLCMD mode in SSMS can do such thing without having to run outside SSMS.

Go to Query in main menu, then click SQLCMD mode to enable it

Here is how you can enable SQLCMD mode in SSMS:

Alright! so, now let’s do a quick example. Imagine you don’t want to lose the list of roles assigned to all your database users for a specific database, in order to script out the roles we will run a query and generate an output directly to Windows having the T-SQL script ready to run when needed.

Here is the T-SQL code:

BEGIN
    SET NOCOUNT ON
    :OUT F:\MyDB_Roles.sql

    DECLARE @Role varchar(255)
    SET @Role= '%'

    -- Add Users to roles
    SELECT
        'EXEC sp_addrolemember ''' + RTrim(r.name) + ''',''' + RTrim(Coalesce(l.name,u.name)) + '''' as '-- Add Role Users'
    FROM dbo.sysusers u
        INNER JOIN sysmembers m
        ON u.uid = m.memberuid
        INNER JOIN sysusers r
        ON m.groupuid = r.uid
        LEFT OUTER JOIN master.dbo.syslogins l
        ON u.sid = l.sid
    WHERE r.issqlrole = 1
        AND u.name <> 'dbo'
        AND r.name LIKE '%'
    ORDER BY r.name,u.name

    PRINT '-- End of script'
END

Note the third line, I’m using the :OUT command which will take care of redirecting the query result into a SQL file within Windows:

:OUT F:\MyDB_Roles.sql

Once the query is executed, here is how the SQL File output file looks from Visual Studio code:

As you can see this is a normal T-SQL script generated from SSMS, it contains the roles we wanted to script out from our database. As you may realize by now this is a very handy option in SSMS, and to be fair with SQLCMD mode this is just a simple use case there is more scenarios where this SSMS functionality can help you to achieve very neat things.

For more information about other parameters for SQLCMD,  please check this link from Microsoft docs.

Stay tuned for more DBA mastery tips!

SQL Server Management Studio Reports

Today’s tip is a very basic functionality that comes with any SSMS (SQL Server Management studio), we are talking about SSMS Instance and Database level reports.

These SSMS reports does not require any additional plugin or either SSRS (SQL Server Reporting Services), all these aware reports are part of any out of box SSMS instance you might have installed in your environment.

As mentioned above, there are two different levels of reports please keep in mind each set has unique reports that only applies to the specific level selected.

In order to explore the Instance level reports you just need to simply right click on your SQL Server instance, then select Reports, then Standard Reports:

In order to explore the Database level reports you just need to simply right click on any or your existing  SQL Server databases, then select Reports, then Standard Reports:

We are not going to dive deep on all these reports because as you may noticed there are too many and the report name does well job describing their purpose.

Note:
We will cover the Custom Reports option in the near future, stay tune for more DBA mastery tips.