This is a note on how to silently install SQL Server Management Studio (SSMS) via command (PowerShell).
SSMS is a tool most people use to administer SQL Server and to run SQL queries. If you need to install SSMS many times or on multiple computers, you may want to use the silent installation feature when automating the process.
This ‘silent‘ installation allows you to install SSMS without any user interaction, making it quick and easy to deploy on multiple computers. In this demo, we are using the Standard Microsoft Installer Command-Line Quiet Option (/q).
How to Silently Install SSMS
To perform a silent installation of SSMS, you will need a copy of the SSMS installation files. You can download SSMS from this link here: SSMS Download (Microsoft Docs)
Open Windows Terminal or any command prompt for PowerShell as Administrator and navigate to the directory where you have the SSMS installation files downloaded. Then run the msi exec command as shown:
# Navigate to directory with ssms download
cd ssms
gci
# Install SSMS (silently)
.\SSMS-Setup-ENU.exe /q
The only parameter being used here is /q which specifies that the installation should be performed silently. So SSMS will install in the background and your terminal prompt will return to a command-ready state when the installation has been completed.
SSMS will be installed silently on the host and be ready for use. That should be all there is to this one. Feel free to check out the SSMS Tips Tag for more useful information about this application.
By default, the max number of characters displayed in a single column of a query in SSMS when outputting as text is 256 characters.
We can amend our query outputs in SSMS to Results to Text, rather than the default grid layout. This can be done by clicking the top bar menu button as shown in the screenshot below.
If using text output rather than grid, you may encounter issues with truncated query outputs if a column contains more than 256 chars.
The screenshot below shows an example of a truncated column, only showing 256 out of 550 characters in the column.
The demo below shows how to fix this and increase this SSMS default value to the maximum chars allowed.
Increase SSMS Maximum Characters for Results To Text
To increase your SSMS Maximum Characters for Results to Text, follow these steps.
1. Click Tools at the top menu bar and select Options.
2. Navigate to Query Results > SQL Server > Results to Text.
3. Open a new query Window and re-run your query or procedure.
That’s it, working for you now… I hope.
The reason I note this one down as a blog post is that there are rare occasions where you might need to use Results to Text as a DBA. One example, when working with MSSQL Replication, the output of sp_scriptdynamicupdproc will likely contain more than 256 characters for the procedure statement output.
Feel free to check out my SSMS Tips tag for more random Management Studio tips and stuff.
This post contains a guide on how to show line numbers in the SSMS query window. Useful if you want to see line numbers while you code.
As well as general preference, enabling line numbers in SQL Server Management Studio is particularly useful when debugging an error message in a SQL script.
When you are debugging a SQL script within SSMS, showing line numbers might help you when encountering an error like this one below.
I’ve opened a random Stored Procedure, amended and attempted to run it, knowing it will fail.
The line our code has failed on is included in the output error message: “Msg 102, Level 15, State 1, Procedure sp_addlogin, Line 33 [Batch Start Line 7] Incorrect syntax near ‘gif’. Msg 156, Level 15, State 1, Procedure sp_addlogin, Line 35 [Batch Start Line 7] Incorrect syntax near the keyword ‘else’.“
Line numbers shown in these error messages aren’t always accurate, but it should give you a pointer to the section of broken code.
You can Double-Click the error message to bring you to the referenced line number, or use the Go To Line feature (CTRL + G).
This post is a guide on how to connect to SQL Server with Windows Authentication, but using a different Domain User rather than your own.
The regular Windows SQL Server User connects & runs their queries via SQL Server Management Studio (SSMS). If you’re in a corporate environment, you’ll likely be logged into your computer with an Active Directory (AD) User. In this example, if we open SSMS we will likely connect with our currently logged-in AD account.
This guide shows how to authenticate to MSSQL using an AD account that is not the same as your own logged-in user, which is particularly useful for testing newly created SQL Server AD User access and permissions.
Open SSMS with Other Domain User
A regular SQL Server Management Studio (SSMS) user in Windows has the application pinned to their taskbar. Search with the Windows button and follow the same on the SSMS app icon.
Right-Click SSMS Icon –> Hold CTRL + Right-Click Icon –> Select Run As Different User
Simple as that. No need for much longer explanations. All I can refer you to is my SSMS Tips tag for more Management Studio related blog posts.
The Disk Usage by Top Tables Report in SQL Server is a quick way to get the sizes of all tables within a database. It’ll show all tables largest to smallest tables by total size on disk.
If you notice that a database is growing larger in size, then you will want to know what data is causing the increase. Knowing your largest table(s) on all SQL systems helps you make better decisions overall. For example, with this knowledge, you might want to consider performing a review of indexes or compressing some indexes to save space.
There are many ways to check the size of a table in MS SQL Server. This post is to help show you how to open the SQL Server Disk Usage by Top Tables Report, which is what I find to be one of the more efficient ways to check table sizes in SQL.
I have another post if of interest that shows a variety of ways to check table sizes in MSSQL. Below shows a demo of the Disk Usage by Top Tables Report in MSSQL as described.
Disk Usage by Top Tables Report
To open this report:-
1. Open SQL Server Management Studio (SSMS). 2. Expand Databases. 3. Right-click the Database and select Reports > Standard Reports > Disk Usage by Top Tables
The report shows that this database has 2 tables close to 700MB in size, both containing the same number of rows but there are some differences in the indexes.
For more random tips for checking disk space in MSSQL, have a look at my Measuring Databases tag.
SQL Server Management Studio (SSMS) is the tool that most SQL Server user queries are run from. It has an option that allows you to change the colour of your query banner, which is useful if you want visually mark a server that you work with every day.
My choice of colour below stands out quite well:
Amending this is particularly useful when you are running queries in test, development & production environments from the same host. You want to have a visual warning/display to indicate clearly which MSSQL host you are running your next query on.
This is a feature that I find myself using from time to time, and I see others using it frequently.
Changing Connection Colours in SSMS
To change a connection colour for a particular SQL Server Instance, follow below:
1. Click to connect to a SQL Server instance within SSMS.
2. Click Options.
3. Within the Connection Properties tab, check the box to use custom colours and select your preference.
This post contains a guide on how to export data from a SQL Server database using SSMS.
SSMS, SQL Server Management Studio, is the standard application that most people use to run queries for a Microsoft SQL Server Database. The default export option in SSMS is set as Column-Aligned, and we need to change it to Comma-Delimited to get our query output formatted correctly for CSV.
To export a SQL query to CSV in SSMS we use the Results to File option which is shown in the demo below in this post. We use this instead of the standard query output option, Results to Table.
The first part of this post runs through how to export a SQL query to a file using the default output options in SSMS, and then we are changing the output format column options as mentioned above.
Now that we’ve looked at the default SSMS export file option, let’s change it to Comma Delimited so we can open SQL query outputs as CSV.
Within SSMS, go to Tools > Options as shown in the screenshot below.
In the Options window that appears, navigate to Query Results > SQL Server > Results to Text
The default option is shown above, Column aligned. Change this to Comma delimited.
We now need to disconnect our current session within the SSMS Object Explorer and connect back to the SQL Server instance.
Now, if we run that query as we did above outputting as text, the output file should be formatted correctly when saved as CSV.
You may have identified an issue with the exported CSV data, and it contains “rows affected” at the end of it. The reason for this is that we need to include SET NOCOUNT within the SQL query when using Results to Text. I talk more about this in another one of my posts – Count Rows in CSV Files > Checking for Data Issues
I hope this guide has helped you export a SQL query to CSV today. If you would like more SSMS-related tips, feel free to have a look at my SSMS page which is frequently updated.
There are many ways to check the size of a table in SQL Server. The way you will do it will likely depend on what your task at hand is.
For example, a quick look at the Disk Usage by Top Tables report on a database you’ve never seen before would instantly show you the large tables in a database. But if you want this information saved somewhere else, e.g. to a table in SQL, it’s an easy task for us to run a TSQL script and output to Excel or CSV.
To open this report:- 1. Open SQL Server Management Studio (SSMS). 2. Expand Databases. 3. Right-click the Database and select Reports > Standard Reports > Disk Usage by Top Tables
The report shows that this database has 2 tables close to 700MB in size, both containing the same number of rows but there are some differences in the indexes.
Another way to check the sizes of tables in SQL Server Management Studio (SSMS) is to open the Table Properties within the Object Explorer.
To do this, expand the database and tables. You may need to add a filter to find your table.
Now, right-click the table and select Properties as shown in the screenshot below.
Open the Storage tab on the left-hand sidebar. It may take a second for the window to load, but this will show the index space, data space usage and the table row count.
Lastly, we can run a query on SQL Server system tables to get the table sizes.
-- Get Table Size SQL Server Script
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
That’s us done for now. For more SQL Tips from a random MS SQL DBA in the field, feel free to check out my SQL DBA Blog page which includes a list of my latest posts.
