DBCC DBReindex

I ran into some ‘Message 823’ issues on my development DB, and upon running DBCC CheckDB() I saw that my indexes were the ones throwing the errors.
I got this handy cursor from SQL Server Performance, and decided to save it here for me, and for you, so that I don’t have to search for it over and over again. The script by the way is from the talented Brad McGehee, and I suggest checking out the article because it has more information and more cursors for failing indexes.

Without further ado:
USE DatabaseName –Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,’ ‘,90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor


Conclusion: you, too, can fix your indexes with a cursor now.

sp_msforeachtable

I will make this short and sweet for you.
If there is something you want to do to all tables in your database, there is a command for you.

I will demonstrate, watch my hands because I cheat. To add a column to every table:
exec sp_msforeachtable 'alter table ? add intGoat int NULL'
To delete a column from each table
exec sp_msforeachtable 'alter table ? drop column intGoat'

And this is how you use this undocumented little gem.

Small print: emphasis on undocumented, aka can go away or change in nature without any warning. Test it on your dev DB before using it.
Willy Wonka is telling you to watch with undocumented features.

The thing with cursors

So our aggregated databases have experienced some loading errors lately, and until that gets fixed, I have to collect the data straight from the field databases for each report.
That is, go in the database, pull the data, load into temp.
Go in the next database, pull the data, load into temp.
Go in next, pull, process. Go in next, pull, process.

Sounds familiar? That’s a classic cursor right there.

Sample:

CREATE TABLE pureHatred (
[DBName] varchar(50),
[text] varchar(max)
)
DECLARE @name VARCHAR(50)
DECLARE @sql VARCHAR(max)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
While @@FETCH_STATUS = 0
BEGIN
SET @sql = N'USE ['+@name+']
INSERT INTO pureHatred(DBName, [text])
SELECT DB_NAME(), v.name
from sys.[syscomments] sc
JOIN sys.views v ON v.object_id = sc.id
WHERE sc.text like ''%fill my sproc with hatred%'''
exec (@SQL)
FETCH NEXT FROM db_cursor INTO @name
END
Close db_cursor
DEALLOCATE db_cursor
select * from pureHatred
drop table pureHatred

Now you’ve heard a million times that cursors are a bad approach, and I won’t even deny that fact. They are. They lock rows and load everything into the memory. Cursors also include multiple steps, that is selecting the data, then processing the data, and the lock remains there for the duration of all steps. Also, the server has to execute the plan for each row, each database. (case in point: pull 10K orders each from 500 databases. Oh look! Large numbers.)

I have the luxury to do so in our environment, because we are loaded with memory already. My box still completes the 5 million pull-process cursors in less than 10 seconds, but imagine the peak in memory processes as it does so!* However, we could actually use that memory for other processes, so my solution is only for ad-hoc reports, and only until I fix the data imports. I slap my wrist every time I write up a cursor and hope that the lord of database administrators doesn’t find out about my sin.

But! I don’t know of any other way to jump databases. Feel free to suggest a solution, if you. If you have to repeat the same process in 500-1000-5000 databases, you need a cursor to repeat said process.

So again, should someone toss in the question on your next interview: cursors are bad for using up resources and locking rows.
There are times you can’t avoid them (jumping databases), but if you want to, read up on set-based approaches. I’ll write another article on the matter soon.

Conclusion: If you have to jump databases, you will use UNION ALL 5000 times, or you will load the data in a temp table with a cursor. In this one case I approve of using a cursor, else I you should avoid it.

*You know what? Let me run a query on sys.dm_exec_query_memory_grants next time I set up a mildly complicated cursor, and I tell you just how much memory it costs indeed to use it.

Cast datetime as varchar

I stumbled upon this great Q+A on Stack Overflow when I was looking for options to cast datetime for a report. (The customer didn’t like our standard mm/dd/yyyy format.)

I actually like the third answer the most, because it gives you a chart on all the styles. A pretty helpful shortcut, if you ask me.

union select convert(nvarchar(MAX), @now, 1), 1
union select convert(nvarchar(MAX), @now, 2), 2
union select convert(nvarchar(MAX), @now, 3), 3
union select convert(nvarchar(MAX), @now, 4), 4
union select convert(nvarchar(MAX), @now, 5), 5
output, style
Apr 28 2014 9:31AM, 0
04/28/14, 1
14.04.28, 2
28/04/14, 3
28.04.14, 4
28-04-14, 5

Helpful, if you ask me, if you want to depart from your usual convert(101) format. I don’t even know why the answer is not marked correct. Check out the article here, and bookmark it for future use.

Rochester Wing Theory

Today I learned that apparently the best wings in Rochester can be found at *drumroll* the Klassy Cat.

This was shocking news to me. Imagine the last place on Earth where you would look for wings, and you have the Klassy Cat. (As the name suggests, people come here for other sort of entertainment.) And now we know that they also have seriously good wings, thanks to a new local site, Rochester Wing Theory.

The admins are two guys who have sworn oath to review as many bars, pubs, restaurants, strip clubs, and other establishments in the area as possible to find THE best wings in town. If you’ve ever had good wings, you know that this is some serious pro bono effort for the public. We all will benefit from this.

Conclusion: The guys have an excellent style and great sense of humor, and they review chicken wings for you in Rochester. Head over to Rochester Wing Theory and follow their adventures, then go eat some wings. Wings are good for you.

Ebola

I have quite a few friends living in Liberia. They have been posting about Ebola for two months now.
Today a patient died in a hospital in Texas and the US is losing their shit. Mind you, in the meantime more than 2K people died in Liberia.

Fox News, of course, is creating the hype already. That Ebola could be smuggled from Mexico and be used as a bio weapon. That dead bodies are lying around on the streets of Sierra Leone. That it’s Obama’s fault. Or not Obama’s fault, I don’t fucking know, okay?

While you can prevent the whole outbreak if you wash your hands and stop getting into contact with those having Ebola. You will recognize them from the blood coming out of their… you know. Everywhere.

So, let me copy here what my Liberian friend said today, and maybe all of us can STFU for a second and be happy that we’re alive.

If it is not in their shoes, they don’t care.
People in the affected coutries die like animals. It is scary. U live like the next day u will be dead.

It is like a horror movie.