There a better ways of generating random strings in your application using PHP, C#, Python or whatever OOP language you are using but if you need to do this at the database level then here’s a clever little trick using a view and an SQL function. Basically you create a Continue Reading
Bulk Insert Using OPENROWSET()
I needed to bulk import a text file to a table but I couldn’t use BULK INSERT as the text file was only for a few columns that were present in the destination table. Rather than bulk insert to a new temporary table and insert the data from here, I Continue Reading
Database Table Sizes
This is a good query for quickly finding out general information about your table sizes in your specified database: USE [DBName] SELECT t.NAME AS TableName, s.Name AS SchemaName, p.ROWS AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.TABLES Continue Reading
SQLCMD and SQL Variables
I recently worked on a little project that involved outputting some workflow user stats from a database every hour. The scripts must produce an output file that will be transferred via SFTP to a separate database in another domain. The main bulk of the process was contained within a stored Continue Reading
List Foreign Keys, Parent and Referenced Columns
SELECT fk.name AS ForeignKey_Name, ob.name AS ParentTableName, col.name AS ParentColName, ob2.name AS ReferencedTableName, RCol.name AS ReferenceColName FROM sys.foreign_keys AS fk INNER JOIN sys.objects AS ob ON fk.parent_object_id = ob.object_id CROSS JOIN sys.objects AS ob2 INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id INNER JOIN sys.COLUMNS AS COL ON col.object_id Continue Reading
Execution Plans
I’ve been delving deep into the world of execution plans recently and I’ve come up with the following two queries which are quite handy for viewing your current execution plans stored in your plan cache. I’ve sporadically used it to look through and familiarise myself with execution plans in general Continue Reading
The God Delusion
I am now over halfway through reading The Nature of Code by Daniel Shiffman in amongst learning and sketching out ideas in JBox2D, ToxicLibs and the other normal distractions such as earning money doing my day job and watching old black and white movies on the daily commute. I read Continue Reading
3d Solar System Model
Before I started reading The Nature of Code I wanted to go over the some of the magical processing tricks I had learned so far so they had a better chance of staying lodged somewhere in my brain. One of the exercises in Learning Processing was to build a 3d Continue Reading
PHP – Email with Attachments
A while back, a friend of mine asked me to have a look at writing a PHP function for sending email and attaching any uploaded files from a HTML form. I’m very glad she did ask me as this bit of code has been very useful to me over the Continue Reading