SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


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:

    :OUT F:\MyDB_Roles.sql

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

    -- Add Users to roles
        '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'

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!

DBA Mastery

I am a very experienced multi platform Database Administrator (SQL Server, Azure SQL DB, Oracle and MySQL). I have more than 10 years of experience in the database administration field, working for large scale US based companies (multiple industries). I have multiple SQL Server certifications under my belt (MCP, MCTS, MCSA, MCSE), during my spare time I like to help others using the #SQLHelp hashtag in Twitter. I hope you find all the information/DBA tips shared in this blog useful


Leave a comment on the original post [dbamastery.com, opens in a new window]

Loading comments...