# LessThanDot

All Blogs

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

# SQL Puzzle.. How many uppercase and lowercase characters in a column

by SQLDenis on Jun 12, 2013 in categories Database Programming, Microsoft SQL Server. Article views: 7972 views

In this week's puzzle we are going to try to figure out how many uppercase, how many lowercase characters are in a column, we are also interested in how many are neither uppercase or lowercase Here is the table and the data CREATE TABLE Puzzle…

# SQL Puzzle.. Sorting results without using ORDER BY

by SQLDenis on Jun 05, 2013 in categories Microsoft SQL Server. Article views: 5318 views

Time for this week's puzzle/teaser. I want to return the results in ascending order but without using ORDER BY If you run this code CREATE TABLE Puzzle (Col1 varchar(20) NOT NULL PRIMARY KEY CLUSTERED, Col2 varchar(20) NOT NULL UNIQUE…

# SQL Puzzle...how many even numbers are there in a row

by SQLDenis on May 29, 2013 in categories Database Programming, Microsoft SQL Server. Article views: 5108 views

In last week's puzzle SQL Puzzle: RIGHT without using the RIGHT function we looked at how to do a RIGHT function without using the RIGHT function. Today we are going to find out how many columns are even in a row. Before starting I want you to be aw…

# SQL Puzzle: RIGHT without using the RIGHT function

by SQLDenis on May 22, 2013 in categories Database Programming, Microsoft SQL Server. Article views: 8866 views

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…

# SQL Server Database owner ~~UNKNOWN~~

by SQLDenis on May 14, 2013 in categories Database Administration, Microsoft SQL Server Admin, Microsoft SQL Server. Article views: 13870 views

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…

# Where to find the xtype info for SQL Server in a table

by SQLDenis on Apr 26, 2013 in categories Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server. Article views: 11519 views

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…

# Messing with your friendly DBA on April Fools' Day

by SQLDenis on Mar 29, 2013 in categories Microsoft SQL Server Admin. Article views: 10982 views

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

# Giving only insert permissions to a table for a new login

by SQLDenis on Mar 04, 2013 in categories Database Programming, Database Administration, Microsoft SQL Server Admin, Microsoft SQL Server. Article views: 3942 views

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…

# Column name or number of supplied values does not match table definition when dealing with temp tables

by SQLDenis on Mar 03, 2013 in categories Database Programming, Microsoft SQL Server Admin, Microsoft SQL Server. Article views: 4633 views

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…