adamkdean

software engineering

Find column from all tables

By Adam K Dean on

A very useful SQL snippet today, courtesy of Danny Dawes and SQLAuthority.

The following query will list all tables that reference a specific column name:

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

Just change %EmployeeID% to the column name you're looking for.

Counting commits with Git

By Adam K Dean on

To count commits on a branch, you can use git rev-list.

For the branch you're on, use:

git rev-list --count HEAD
5

Or for another <branch>, use:

git rev-list --count <branch>
5

Also, to count commits by author, you can use git shortlog:

git shortlog -s -n
4 Some User
1 Joe Bloggs

Creating empty files with specific sizes

By Adam K Dean on

If you're doing speed tests, you may find yourself needing large files with which to test your connection speeds and stability.

On Linux, you can do this with dd like so, creating a 100 MB file (or thereabouts):

dd if=/dev/zero of=output.png bs=1M count=100

On Windows servers, you can do this using fsutil, like so:

fsutil file createnew output.png 104857600

Remember, 1024 bytes in a kilobyte, 1024*1024 bytes in a megabyte, and so forth.

Count files in a directory in Linux

By Adam K Dean on

Count files in current directory with:

ls | wc -l

Or count files in a specific directory with:

ls /some/path | wc -l

RDP via SSH (PuTTY)

By Adam K Dean on

If you're like me, and you're paranoid about opening up access to your servers to the internet, you might sometimes find yourself needing to access them but not being on the local network. A handy trick to circumvent this, if you have access to SSH, is to open a tunnel.

For this we're going to need:

First, open up either command prompt, or MinGW or something, and navigate to your PuTTY path:

cd /c/PuTTY/

Then connect to the SSH server specifying a tunnel like so:

putty.exe -L <localport>:<remotehost>:<remoteport> <sshserver>

For example:

putty.exe -L 8001:192.168.1.2:3389 adamkdean.co.uk

This will open up a PuTTY window. Authenticate and open up another cmd/mingw prompt. In this one we're now going to use mstsc (remote desktop) to connect through the tunnel. We opened up port 8001 on our local machine, so lets connect to that.

mstsc -v localhost:8001

If you've done everything right, you should now get a remote desktop prompt.

Update from 11/02/2016: You can tunnel connections with regular ssh like so:

ssh user@hostname -L <localport>:<host>:<remoteport> -N

-L is for your tunnel, and -N is no command executed on connect.