Needless to say, I’m a SQL n00b. For the longest time I drank the Microsoft Kool-aide and believed “you shouldn’t touch the SQL side of SharePoint.” And when I say “shouldn’t touch” I mean absolutely no touching or querying of any kind. Well, I’ve wised up. It’s not that you can’t touch anything, just be mindful of what you can and can’t do. Querying – as long as you do it with no lock – will not adversely impact anything. And above all else, you must have a healthy respect for what you’re doing lest you decide to anger the SharePoint Gods. This post is for all my peers out there that do a little SP Admin work on the side, but – for a variety of reasons – don’t get into the SQL side of things much.
SQL Management Studio is the best (and only?) tool used to query SQL. It’s a pretty straightforward, easy-to-use product. Open it up, connect to a database, and click the “New Query” button:
Pretty simple. From here you can query SQL (i.e. “ask” SQL things):
USE [database name] select * from [database table]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
The Use statement dictates which database you want to query. There is a dropdown near the “New Query” button, but the Use statement is so much easier to use. Plus, I’ve – on many occasions – forgot to select the correct database from the dropdown. I just use the Use statement from now on. “Select *” means you want to return all the columns in that table. This can be helpful to use when you just want to poke around and learn about SharePoint’s innards.
Use [database name] select * from [database table] with(nolock)
“with(nolock)” is important because it doesn’t lock up the table while you’re querying it. I again learned this one the hard way. Locking up tables is a bad thing, meaning no one else can use said table until the query is done. Don’t do what I did and lock up the content database. Does not make for good job security. Furthermore, it’s generally a good idea to run just about any query (at least in the case of SharePoint) with no lock on it.
One last query nugget of wisdom. Replace the * in the select statement with column names (comma separated). So for example:
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Use [database name] select LastName,FirstName,Address from [database table] with(nolock)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Your query will return the results with just the Last Name, First Name, and Address columns. Doing so will help clear the query results up and remove any extraneous data.
These 3 basic queries – while n00b’ish – are very valuable to know and will hopefully help you when working with Management Studio.
Adventures in Tech 