Category: Tips and Tricks

Exec sp_help on a table with a schema

Really quick post regarding sp_help and tables in Schemas (not in dbo). Usually when doing a sp_help, you can do something resembling the following : However, if you try to do the same with a table with a Schema (SomeSchema.TableName) you will get an error “Incorrect syntax near ‘.’”. To execute the sp_help on a table with a schema, simply...

What’s the Difference Between Where and Having in SQL?

A really quick post for you all today over the differences between Where and Having in SQL. This has been a question in a lot of interviews, but no one seems to know why and when to use each of these clauses. Where The where clause is used to filter based on criteria other than aggregates. So say for example...

SQL-IN : A Tool to Quickly Build The In Condition

So after years of copying data to Notepad++, then formatting to use in an IN condition, I decided to create an application to perform that function for me, at a fraction of the time. The tool is called SQL-In and can be used to parse data with common or a custom delimiter, then format to use with an IN condition. The...

Package ‘Microsoft SQL Managment Studio Package’ failed to load

I had  a fresh Windows 7 operating system, SQL Server install, and had just finished installing SQL Server management Studio(SSMS). I tried running SSMS and got the failed to load error. I had noticed when I was researching this error there were a few solutions besides the one I found, so I thought I’d compile them here. Solution 1 : Re-installing SQL...

Nested Case Statements in SQL

Need to do nested Case statements in SQL? Use the following syntax. This should result in ‘a1b1 – yar’ This took me a little time to work out the syntax, but pretty simple once I understood case statements a little better. For some more info on case statements, you can go to the msdn  article located at http://msdn.microsoft.com/en-us/library/ms181765.aspx

Using Transactions in the Entity Framework (EF4)

You have multiple updates and want to implement transactions into your database updates in the Entity Framework? I had to do just that in C# , so I thought I’d share what I learn for to remember, and for you to learn. To do this, we must create a TransactionScope, and move around our save function a little bit in...

Automated Backups of Your SQL Database

Ever find yourself taking precious time and backing up all your databases, only to forget and be behind? I am currently working on some Alpha-PreAlpha software whose database structure changes with time like any other application. To solve the issue with remembering to back up however, I decided to automate it, and not have to worry. There probably is a...

How to Add, Modify, Rename, and Delete a Column in SQL

There has been a few times where I needed to compose a script to update a customer’s database, and I’ve had to use probably the most popular of the SQL ALTER TABLE syntax: Add, Modify, Rename, and Drop. To help save for my own notes and to possibly give some help,  I thought I’d write a quick article on these 4...

Delete or Truncate All Data in a Database

A quick tip for you all, ever need to delete or truncate all the data from a database? There has been a few times during testing after my database grew, that I needed a quick solution to clear a database. The solution is using the MSForEachTable stored procedure. Simply do the following : DatabaseName – The name of the database you...

Specify Your Own Select And Edit Top(n) in SQL Server Management Studio

When right clicking a table in SQL Server Management Studio, you have an array of options. Probably the two that I use most however is the “Select Top 1000 Rows” and the “Edit Top 200 Rows”. However, sometimes you want to look at a little more than that. I know most of the time I want to select all from...

%d bloggers like this: