SQL Search Script

In our data warehouse environment we have over 10 terabytes of data (staging and production reportable), comprised of over 5000 tables populated by over 500 SQL Agent jobs. So, when I get a question about a particular table I don't always know the answer from memory. I refer often to my SQL Search Script. This lets me know which tables, views, stored procedures and jobs relate to a particular search string. The only thing missing is SQL embedded in DTS.

DECLARE @SQL varchar (500)

DECLARE @Search varchar (200)

--Enter your search key-word or table name

SET @Search = 'SearchString'

DECLARE @DBName varchar (30)

DECLARE Databases CURSOR FAST_FORWARD FOR

IF OBJECT_ID('tempdb.dbo.#FindObjects') IS NOT NULL DROP TABLE #FindObjects

CREATE TABLE #FindObjects

(

Database_Name varchar(128) Null,

Obj_Name varchar(128) Null,

dbType char(1) Null

)

OPEN Databases

FETCH Databases INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'INSERT INTO #FindObjects '

SET @SQL = @SQL + 'SELECT DISTINCT ''' + @DBName + ''', [Name], a.Type '

SET @SQL = @SQL + 'FROM ' + @DBName + '.dbo.sysobjects a '

SET @SQL = @SQL + 'LEFT JOIN ' + @DBName + '.dbo.syscomments b ON a.ID = b.ID '

SET @SQL = @SQL + 'WHERE a.Type IN (''P'',''U'',''V'')'

SET @SQL = @SQL + 'AND (b.Text LIKE ' + '''' + '%' + @Search + '%' + ''''

SET @SQL = @SQL + 'OR a.Name LIKE ' + '''' + '%' + @Search + '%' + ''')'

EXEC(@SQL)

FETCH Databases INTO @DBName

END

CLOSE Databases

DEALLOCATE Databases

-- Objects Results

SELECT * FROM #FindObjects order by Database_Name, dbType, Obj_Name

-- Check Jobs

SELECT Job_Name = b.name, a.step_id, a.Step_Name, a.subSystem, a.command, a.database_name, a.output_file_name

FROM msdb.dbo.sysJobsteps a

Join msdb.dbo.sysjobs b on a.Job_id = b.Job_id

Where Step_name like '%' + @Search + '%'

or a.command like '%' + @Search + '%'

Dimensional Modeling

Why is it that the people you most want at a meeting are the ones who inevitably not show up? Such was the case when after 2 years of trying we finally received company financing to get the Kimball group to do on-site dimensional modeling training. The training was great, the benefits of adoption obvious and new energy was pumped into the staff to do things "the best way." The problem? Only one out of the five managers and directors who over-see the staff attended and never really catch the vision of adoption.

So, to keep the vision alive, I created the following power-point (Dimensional Modeling Powerpoint). I've used it a few times over the past year with BI staff and managers who missed the training and with new-hires. Maybe someone looking for a refresher course may find it useful as well.

Dribbles

My wife is not into blogging. Reading blogs, yes. Writing blog, no. However, she recently sent along this story via email that I'm taking the liberty to post here. Enjoy!

Yesterday we were visiting with Mrs. Barbara, my youngest son's preschool teacher. She was telling us that over the years of working with lots of little boys at the preschool/daycare they have learned to ask the boys to “clean up your dribbles” after a trip to the bathroom. So last week during preschool as a little boy came out of the bathroom she asked him if he’d checked his dribbles and he pops up with, “My brother, has a dribble!” Mrs. Barbara was a bit surprised, but she is used to this kind of “announcement” so she made some comment like: “Well, I’m sure he takes care of it” and started to go on with class when my son says, “Yep, he keeps it in his room and feeds it and his name is Matthias.” Mrs Barbara, was a little stunned until she realized he was talking about the GERBIL!! Mrs. Barbara thought it was hilarious!!!

The Crying and the Moaning and the Screaming...

If you have small kids then you have to watch this one.

Custom SSIS Slowly Changing Dimension Type 2

Unfortunately when you use the SCD wizard to create a type 2 slowly changing dimension you have to choose between using a current indicator or a valid date range technique. You can not have both, at least through the wizard. To add both you will have to edit the package after the wizard is complete.

Complete the Slowly Changing Dimension wizard. I prefer choosing the Single Column / Current Flag option for Historic Attribute Changes during the wizard.

Modify the components in the SDC update branch

1. Add a new derived column with an expression like (DT_DBTIMESTAMP) GETDATE()
2. Modify the OLEDB Command Update statement, i.e.:UPDATE [dbo].[tbl_Cost_Center_Dim] SET [Current_Flag] = ? ,Row_End_Date = ?WHERE [Cost_Center_Code] = ? AND [Current_Flag] = '1'
3. Modify the OLEDB Command, Column Mapping tab, to reflect the re-alignment the new parameter(s) added.

Modify the components in the SDC Insert path. These are the components after the Union All component.

1. Add new derived columns for begin and end effective dates. The end effective date should be a conformed standard future date such as 6/6/2079.
2. Add the new columns to the mapping of the OLEDB Insert Destination component.

In addition to using this process for Row Effective dates it is also useful for using Row Created and Row Updated dates.

Brain Ache

No, not head ache but brain ache. I started my on my MBA this semester. I attend classes every other Friday and Saturday. That is my excuse for not blogging lately. After 10 hours of Accounting and Finance I literally had a brain ache, it hurt to think.

SQL Round To Quarter

This is a reminder to myself how to round to the nearest fraction, in this case a quarter.
Next time I won't spend 15 minutes trying to recall the rounding formula. I'll just look it up here.

DECLARE @target smallmoney
SET @target = 597.80

--round to quarter hour
SELECT ROUND( @Target/25.0, 2) * 25

-- Result = 597.75

Broadband?

At home I connect with a Verizon air card, I don't know if it is better than satellite or not for where I live. Here is how I measure up.



And this is what I've got on vacation on the Oregon Coast.

Real Life

I came across this thought today as I was reading my usual blog feeds. It captures my daily struggle between a successful life and a devoted life.

Humans are apt to treat God the way the first type of sports fan treats their favorite team. We expect that if we give our allegiance to God that He will be good to us. But that is not the sort of relationship that God wants with us. He doesn't need our allegiance. He is not an insecure demagogue trying to rally support. He calls us to worship Him not because He needs our worship, but because He knows there could be nothing better for us than to get caught up in his glory. --Betsy Childs

To be successful is temporary, to be devoted is ethereal and eternal. To get that promotion I've been bucking for only satisfies for a time. Then I will want more. To make that the center and measure of my life will always leave me wanting more. Jesus said "Whoever wants to save his life will lose it, but whoever loses his life for me will find it" (Matthew 16:25). And in another place John say of Jesus "In him was life, and that life was the light of men" (John 1:4). Real life is not found in the measure of our possessions, title or popularity but in knowing and being know by the creator.

A Great Morning

Like a friend of mind, I often think of things to blog about but never do. Today is the exception. Its been too long since my last post. Life has been too busy since Spring arrived in North Idaho.
I love being outside. Now that the sun lights up the morning at 5:30 AM, I'll get up most days and do something outside. Since I live on five acres with a large-ish garden I'll usually be out in it pretending that everything will grow better if I tweak this or that.

Today, instead of the garden, I was in the front yard planting trees. A neighbor gifted us some Larch and Ponderosa tree seedlings. Being a person who can't let "good" things die irresponsibly, I felt guilted into planting my seedlings somewhere. With Larch being "more good" then Ponderosa, I planted those first and in the best places. I was able to plant one of the Ponderosa before my internal clock told me I was going to be late for work. The others will have to wait until tomorrow. That was my first good treat of the morning. I planted something that will (hopefully) grow and bring pleasure later in life.

I finished getting ready for work and made my usually latte, flavored with vanilla. I capped off my cup, ready for the morning commute, hmmm, that tastes excellent. The second treat of the morning.

Grabbing my laptop bag and with coffee in hand I approach the truck and see that I left the seedlings on the hood. I've got to put those someplace safe before I leave, back to the house. On the way back out of the house I pass the strawberry patch by our front door. The patch is doing great this year, it is bursting with red berries. It is such a good morning, I stop and pick a generous handful. Back in the house for my third trip, I wash the strawberries, divvy some out to my hungry four-year-old, and finally leave the house. All the way to the highway I'm thinking how great it is to eat fresh berries and drink good coffee on a beautiful June morning.

Thank you God for such a good morning!

Karma vs. Grace - Bono's View

I ran into this quote today and thought it was perceptive. What make it even more interesting is that Bono of the band U2 said it.

“You see, at the center of all religions is the idea of Karma. You know, what you put out comes back to you: an eye for an eye, a tooth for a tooth, or in physics—in physical laws—every action is met by an equal or an opposite one. It’s clear to me that Karma is at the very heart of the Universe. I’m absolutely sure of it. And yet, along comes this idea called Grace to upend all that ‘As you reap, so will you sow’ stuff. Grace defies reason and logic. Love interrupts, if you like, the consequences of your actions, which in my case is very good news indeed, because I’ve done a lot of stupid stuff. . . . I’d be in big trouble if Karma was going to finally be my judge. It doesn’t excuse my mistakes, but I’m holding out for Grace. I’m holding out that Jesus took my sins onto the Cross, because I know who I am, and I hope I don’t have to depend on my own religiosity.”

Read the whole article Rock Star as Theologian at Breakpoint.

Some (more) Inconvenient Truths

I am not an environmentalist, but rather a conservationist. I believe we need to set aside areas and manage them to the best of our abilities. As a hunter, I understand the balance that needs to be struck between over-harvest and over-population of species and habitat. I gladly obey the laws that have been established so I can continue to enjoy the beauty of nature, the challenge of the hunt and a hope of passing both appreciations down to my children.

I see in nature and the environment a deeply intricate design of God which man has only understood a fraction of its complexity. Because my Worldview is so oriented I am skeptical of the broad "scientific" claims made in the name of global warming. The truth probably lies somewhere in the middle. Here are some quotes that I appreciated in a recent article I read.

"From the technical perspective, if anthropogenic [man-made] warming is real, then why is Earth’s closest neighbor warming? According to recent studies, the Martian climate is not only undergoing a dramatic change, its caps are melting “at an alarming rate.” Could it be the naysayers who claim that sun spot cycles and solar winds are major drivers in this phenomenon are onto something?"

"It’s also important to realize that every group has its special interests, be they financial, political, or spiritual. Mr. Gore noted, "You can't make somebody understand something if their salary depends upon them not understanding it." That’s well said; but it applies equally to him and his camp as it does to his critics."

"Having said all this, let me add that I agree with many of the recommendations in An Inconvenient Truth. Recycling more, driving less, adjusting our thermostats, reducing consumables, planting trees, and the like, are the right things to do. All are in keeping with biblical stewardship, regardless of global warming. "

Read the entire article Some Inconvenient Truths by Regis Nicoll at Breakpoint.

Find SQL Objects

I am constantly playing detective with our Data Warehouse. We have so many reports using so many tables that without a good data dictionary we have trouble knowing what processes touch what destination tables. The following SQL helps me find most of the Tables, Stored Procedures, Views and SQL Agent Jobs based upon my Search parameter. The exception to this is SQL tied up within a DTS package.

-- Search Criteria
DECLARE @Search varchar (200)
SET @Search = 'seach_keyword'

-- Variables
DECLARE @SQL varchar (500)
DECLARE @DBName varchar (30)
DECLARE Databases CURSOR FAST_FORWARD FOR
SELECT name
FROM master.dbo.sysdatabases

-- Temp table for clean presentation
IF OBJECT_ID('tempdb.dbo.#FindObjects') IS NOT NULL DROP TABLE #FindObjects
CREATE TABLE #FindObjects
(
Database_Name varchar(128) Null,
Obj_Name varchar(128) Null,
dbType char(1) Null
)

-- Find SQL Objects
OPEN Databases
FETCH Databases INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'INSERT INTO #FindObjects '
SET @SQL = @SQL + 'SELECT DISTINCT ''' + @DBName + ''', [Name], a.Type '
SET @SQL = @SQL + 'FROM ' + @DBName + '.dbo.sysobjects a '
SET @SQL = @SQL + 'LEFT JOIN ' + @DBName + '.dbo.syscomments b ON a.ID = b.ID '
SET @SQL = @SQL + 'WHERE a.Type IN (''P'',''U'',''V'')'
SET @SQL = @SQL + 'AND (b.Text LIKE ' + '''' + '%' + @Search + '%' + ''''
SET @SQL = @SQL + 'OR a.Name LIKE ' + '''' + '%' + @Search + '%' + ''')'
EXEC(@SQL)
FETCH Databases INTO @DBName
END
CLOSE Databases
DEALLOCATE Databases

-- Objects Results
SELECT * FROM #FindObjects order by Database_Name, dbType, Obj_Name

-- Find SQL Agent Jobs
SELECT Job_Name = b.name, a.step_id, a.Step_Name, a.subSystem, a.command, a.database_name, a.output_file_name
FROM msdb.dbo.sysJobsteps a
Join msdb.dbo.sysjobs b on a.Job_id = b.Job_id
Where Step_name like '%' + @Search + '%'
or a.command like '%' + @Search + '%'
ORDER BY b.name, a.step_id

Chew, Chew, Breath

There is a nasty head cold running though our family. I was stuck with it this past week and ended up taking two miserable days off of work. I optimistically stated in an e-mail to my boss that I would check e-mail occasionally. The problem with this was two-fold. One, my pounding, stuffy, dizzy head mixed with a good dose of mind-altering medication would have prevented me from making sense of anything I read or wrote. Two, had I been clear headed enough, the constant dripping from my over-stuffed head would probably short-circuit the laptop.

It was in this circumstance it stuck me as funny that I was having difficulty with dinner. I had been looking forward to dinner in a "comfort food" kind-of-way. As I sat down and started eating I noticed that I couldn't really taste the great meal my wife had cooked*. On top of that, I was surprised how often I had to stop chewing and breath. It was literally "Chew, chew and breath." What do you do when you can't eat and breath at the same time? You end up chewing faster then you're used to and trying to swallow before you are ready. It is a wonder I didn't choke on a large piece of chicken. So much for "comfort-food."

*Legal disclaimer: Taste and smell are linked therefore my perception of the savoriness of the meal was a result of my illness and not the cook’s ability.