I haven't done a puzzle for a long time so I figured let's do a simple one. Return the right 6 characters of the column but without using the RIGHT function. Here is what the table looks like CREATE TABLE #Puzzle(SomeCol CHAR(7)) INSERT #Puzzl…

Today I was checking an older server and decided to run sp_helpdb. On a bunch of databases I noticed that the owner was ~~UNKNOWN~~. The only reason I noticed this was when I tried to look at a specific database which is mirrored. I was greeted with thi…

If you look at the sys.sysobjects view, you will see an xtype column listed Object type. Can be one of the following object types: AF = Aggregate function (CLR) C = CHECK constraint D = Default or DEFAULT constraint F = FOREIGN KEY constraint L…

April Fools' Day is a day when people play practical jokes and hoaxes on each other. Why not trying to play some practical jokes on your friendly DBA :-) The first thing we are going to do is to spoof the host and program name. This is easy to do. Click on Connect, choose Database Engine, you will see the following box

There was a requirement to create a new user who would have only insert permissions to one table, this user would also have insert and select permissions to another table. This is pretty simple to accomplish. First create this simple database with tw…

The other day I was doing some testing and then from the same connection I executed a stored procedure only to be greeted with the following message Msg 213, Level 16, State 1, Procedure prTestTemp, Line 5 Column name or number of supplied values do…

Sometimes you want to quickly see if there are any databases or logs being backed up or restored at this moment. I blogged at one point how you can check how much longer the restore will take here: How much longer will the SQL Server database restore ta…

Sometimes you want to quickly see how many files a database has, how much space a file is using and how much space is free. You can use the sysfiles/sys.files views or compatible views for that. From SQL Server 2005 onward you can also use the sys.datab…

A SQL Server Support Windows Phone App has been released to the store. This app will work on Windows Phone 7 as well as Windows Phone 8 Description from the site Get quick access to SQL Server and SQL Azure troubleshooting content from Microsof…

Sometimes you want to quickly see what tables have the most rows in your database. This is especially rue if you inherited a new database and you want to know some stats about this database. Instead of doing a count(*) against every table, I usually just use the sp_spaceused stored procedure. this will run many times faster, usually it is instantaneous.