Strip Time From Date and Last Moment in SQL

Pretty simple SQL statement that strips the time off of a date and gets the last moment of today.

SELECT 
    DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
  , DATEADD(ms, -100, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()) + 1))

SQL Server Table Space Used

I recently found a bit of SQL that I haven't run across in a while and thought I would share. From time to time I need to know the space used by large tables. In tables that require millions of records, it can be useful. Below is the SQL script that I use to get the space used by each table and the total number of rows in the database.

DECLARE @t TABLE
(
    [name] NVARCHAR(128),
    [rows] int,
    reserved VARCHAR(18),
    data VARCHAR(18),
    index_size VARCHAR(18),
    unused VARCHAR(18)
)

INSERT @t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT *
FROM   @t

-- # of rows.
SELECT SUM([rows]) AS [rows]
FROM   @t

Chocolatey Goodness and Environment Setup

I have been using Chocolatey (apt-get for Windows) - get it, use it, love it - for many months now and absolutely love the project. I think there are things that could be improved, but from a just do it approach, it works perfectly. I recently acquired a new laptop and what better way to get it setup than to create a reusable script.

First install Chocolatey from the Command Prompt. You can copy the script block below and paste it into the prompt and let it install.

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('https://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

Now I have Chocolatey installed, I need to get a list of the applications that need to be installed. My list is pretty short compared to other bloggers on the net, but my list grows over time.

cinst git
#cinst ruby  # I uncomment this if I want ruby installed
#cinst ruby.devkit  # I uncomment this if I want ruby installed
cinst nodejs.install
cinst notepad2
cinst sublimetext2
cinst SublimeText2.PackageControl
cinst EthanBrown.SublimeText2.GitPackages
cinst PowerGUI
cinst FoxitReader
cinst ilspy
cinst linqpad4
cinst sysinternals
cinst SkyDrive
cinst paint.net
cinst 7zip
cinst virtualbox
cinst Firefox
cinst GoogleChrome
cinst fiddler4
#cinst fiddler
cinst lastpass
cinst Devbox-Clink

I also have some general setup that I like to automate which I run after the applications are installed. Eg. Mounting network drives, make the window border width smaller (it is incredibly thick, by default), and fixing an issue that prevents Netflix from playing videos.

Below is the full script

# @powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('https://chocolatey.org/install.ps1'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin
cinst git
#cinst ruby  # I uncomment this if I want ruby installed
#cinst ruby.devkit  # I uncomment this if I want ruby installed
cinst nodejs.install
cinst notepad2
cinst sublimetext2
cinst SublimeText2.PackageControl
cinst EthanBrown.SublimeText2.GitPackages
cinst PowerGUI
cinst FoxitReader
cinst ilspy
cinst linqpad4
cinst sysinternals
cinst SkyDrive
cinst paint.net
cinst 7zip
cinst virtualbox
cinst Firefox
cinst GoogleChrome
cinst fiddler4
#cinst fiddler
cinst lastpass
cinst Devbox-Clink

cmd /C net use N: \\srv1\Common /Persistent:Yes
cmd /C net use G: \\srv1\GIT /Persistent:Yes

# Windows 8 Smaller Chrome Border
Set-ItemProperty -Path "HKCU:\Control Panel\Desktop\WindowMetrics" BorderWidth "-4"

# Fix for Netflix audio issue
Remove-Item -Path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Audio -Name DisableProtectedAudioDG
net stop audiosrv;
net start audiosrv;

write-host @"
Sublime Text 2 User Preferences file

{
    "color_scheme": "Packages/Theme - Flatland/Flatland Monokai.tmTheme",
    "draw_white_space": "all",
    "ensure_newline_at_eof_on_save": true,
    "flatland_square_tabs": true,
    "ignored_packages":
    [
        "Vintage"
    ],
    "rulers":
    [
        50,
        72,
        80,
        120
    ],
    "theme": "Flatland Dark.sublime-theme",
    "translate_tabs_to_spaces": true,
    "trim_trailing_white_space_on_save": true
}
"@