JohnKlann / SQL

SQL Sever Management Studio 2014 Custom Solarized Theme (JK_Solar)

I was looking for a really good SQL Sever Management Studio Solarized theme and I found it rather difficult to find any themes that were created specifically for SQL Sever Management Studio. There are a lot of Visual Studio themes that are out there that you can load but the syntax highlight for TSQL was never great. So I took some colors from a collection of Visual Studio themes and created a custom SQL Sever Management Studio theme specially for TSQL. You can find the download link to the vssettings Theme file below. I haven’t tested this with any other version of SQL Sever Management Studio other than 2014 but I don’t see why it wouldn’t work on earlier versions.

JK_Solar SSMS Theme

JK_Solar SSMS Theme

To import this theme into SSMS follow these steps:

  1. Open the Tools menu and select Import and Export Settings.
  2. Select Import selected environment settings then click Next.
  3. Select a place to backup your current settings.
  4. Select the JK_Solar.vssettings file you downloaded then click Next.
  5. Click Finish and you’re done!

Download Link: JK_Solarized.vssettings Count: 1,561 FileSize: 1.9 kB

Link to Script in Screen Capture: Stack Overflow Script


SQL Convert Size in Bytes to Human Readable Units

Came across a lot of databases that hold size of objects, files, whatever in bytes which is not the easiest to glance at and easily understand. So I wrote a basic SQL function to convert size in bytes to human readable format. The goal being that the output is greater than or equal to 1.0, that is automatically gets loops until it is less than 1.0 then stops at the previous division allowing for an easy to understand yet automatic format. I also have it round to 2 decimal places.

--set database to be used
--checks if function already exists if it does it drops and re-adds it otherwise it adds it
IF OBJECT_ID (N'dbo.f_ConvertSize') IS NOT NULL
        DROP FUNCTION Dbo.F_ConvertSize;
        PRINT 'Function f_ConvertSize Alread Exists, Deleting it and Adding it back now'
        PRINT 'Function f_ConvertSize Does Not Exist, Adding it now.'

--creates function that
--parameter: size in bytes
--returns: single value nvarchar of converted size to readable format
CREATE FUNCTION Dbo.F_ConvertSize ( @rawSize FLOAT )
Returns NVARCHAR(12)
        --creates processing variables
        --div = value to divide by
        --counter = number of times divided
        DECLARE @div INT,@counter INT
        --unit is used to capture the unit measure used in the output
        DECLARE @unit NVARCHAR(3)
        --the sizes table variable is used to store the different units
        DECLARE @sizes TABLE (Id INT,Unit NVARCHAR(3))
        INSERT @sizes(Id,Unit)
        VALUES(1,' B'),(2,' KB'),(3,' MB'),(4,' GB'),(5,' TB')
        --sets div and counter
        SET @div = 1024
        SET @counter = 1
        --while loop used to perform the conversion with condition clause that requires the size to be greater or equal to 1
        WHILE @rawSize >= 1 AND @counter <= 5
                --breaks if the size is less than 1 after division
                --else it sets the rawsize to the rawsize divided by the div then it sets the counter to +=1
                IF @rawSize/@div < 1
                    SET @rawSize = @rawSize/@div
                    SET @counter += 1
        --gets units used from the units table variable
        SELECT @unit = Unit
        FROM @sizes
        WHERE Id = @counter
        --converts the new size to varchar and rounds it to 2 decimal places then adds the units and returns the output
        RETURN CAST(ROUND(@rawSize,2) AS NVARCHAR(50))+@unit