I generally spend part of each day looking through http://www.stackoverflow.com, http://dba.stackexchange.com or http://www.sqlservercentral.com. I read through questions that have already been answered if they look interesting and answer questions where I can. This is a great way to not only keep up my basic skills but to collect… Read more

This is an uncommon task but one that does turn up every once in awhile. A SQL login has to be moved from a development server to a test server, test to prod etc. Or maybe a lateral move to a new server. And frequently the DBA doesn’t and/or shouldn’t… Read more

Templates are one of those really handy tools that most DBAs I know of have either never heard of or just don’t use much. I have to admit I fall into the second category myself. I will break out templates occasionally when I’m working with something I don’t do much.… Read more

While I was at the 2011 Pass Summit I was given the following piece of code to create a comma delimited list.

 SELECT files.database_id, db.name AS DatabaseName,
		STUFF((SELECT ', ' + names.name
				FROM sys.master_files names
				WHERE names.database_id = files.database_id
				FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
			, 1, 2, '') AS NameList
FROM sys.master_files…

Read more

If you are lucky you’ve never seen an error along the lines of:

The SELECT permission was denied on the object 'sysobjects', database 'mssqlsystemresource', schema 'sys'. 

But let’s face it, luck runs out. So you may want to know what on earth this mysterious fifth system database is. As of… Read more

I was scanning http://dba.stackexchange.com and ran across the following question:

http://dba.stackexchange.com/questions/31478/sql-server-script-to-delete-accounts-no-longer-in-active-directory

Basically the OP wanted to know how to get rid of “Windows” or AD logins. Mike Fal answered with a very cool script I just had to blog about. Basically it scan’s through the Windows logins in sys.server_principals and… Read more

The INSERT command has two distinct ways to load data into a table.

INSERT INTO Table (field1, field2) VALUES ('string1','string2')

And

INSERT INTO Table (field1, field2)
SELECT field1, field2 FROM Table2

Both of these are very handy and have been around as far back as I can remember. Historically you… Read more

When writing a script to change a piece of code, say a stored procedure or view, there are 3 basic options.  I’ll start at the outset by saying I use all 3 and that they each have their pluses and minuses.  I’m not trying to advocate one or another, just… Read more

Anyone who has been reading my blog for a while should realize that several divisions in my company have been merging together.  This means that there are lots of new tasks and processes to learn.  It also means that I’m finding uses for several features of SQL and SSMS that… Read more

For those who don’t know, the stored procedure sp_rename allows you to change the name of a user-created object. Basically when you use sp_rename it changes the name of an object in the catalog tables. Unfortunately on a stored procedure, function or view this isn’t the only place the name… Read more

The other day one of the developers I work with gave me a script similar to this:

BEGIN TRAN
sp_rename 'tablename.columnname', 'newcolumnname'
COMMIT

He was getting the error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'tablename.columnname'.

Now most of you probably realize that had he just… Read more

I recently got the following email:

“I just keyed in two rows into a table with all identical column values. How do I get rid of just one of them?”

Now even way back when in the dark ages when I started as a database developer I ran into this… Read more

We got a call last week about an application that was running slowly. The server was showing about 80% CPU utilization consistently, IO was through the roof etc. It was taking something like 15-20 seconds to log in (doesn’t seem like much unless you are used to 4 or 5).… Read more

Most DBAs have at least some idea what the system databases are for. Master has the list of databases & logins, msdb is jobs and backups, tempdb is for temp tables and sorting. Obviously there is a lot more to each of these, but that is the absolute basics, and… Read more

I just wanted to do a brief highlight of a handy little object property. For those of you who aren’t aware there are a handful of “property” functions available that can give you all sorts of information about your object, database etc. For example DATABASEPROPERTY, DATABASEPROPERTYEX, OBJECTPROPERTY, OBJECTPROPERTYEX, FILEPROPERTY etc. Read more

Problem: You’ve added columns to the base table of one of your views, but the view isn’t reflecting the change.

Over the years I’ve seen lot’s of views created similar to this one.

CREATE VIEW vw_TableView AS
	SELECT * FROM TableName

Generally the argument is that if I put “SELECT… Read more

This isn’t really one of those features of SSMS that I’ve used a great deal. At least didn’t until recently. For those that don’t know, if you right-click on the tab of a query window you will get, among other things, options for a new tab group.

If you pick… Read more

I just had the interesting task of finding the port number that one of the instances I deal with is using. Normally this is a trivial task. I log on to the server, open up SQL Server Configuration Manager and check out the TCP/IP properties.

In this case however it’s… Read more

Recently I received a security request and realized I wasn’t comfortable in my ability to script out the t-sql commands I needed. By “comfortable” I mean I couldn’t create a login, then create the user, then add that user to a couple of roles and grant it permissions to some… Read more

Most DBAs have seen those nice clear red errors in the results pane. And I’m sure most DBAs have noticed the line number clearly displayed after the state.

Here is a fun test to try. Execute the following script:

PRINT 'test'
SELECT TOP 10 * FROM sys.databases
SELECT @@VERSION
DECLARE…