Archive for Tools

Catching Up With Mickey

IMG_0555I can’t believe the year is almost half way through. I keep trying to slow the days down, but it just isn’t working. This year I’ve already accomplished so much, and I still have a long list before the year ends. Here is a recap and some events to look forward too!

January

I started the year off with a bang by starting a brand new Business Intelligence chapter in Irvine called Business Intelligence Group, A PASS Community (AKA BIG PASS Community). We consistently have 15 people every month and I’m really happy to announce that I have speakers lined up for the rest of the year! (Yippee!)

I also had the opportunity to participate in Pragmatic Work’s Training on the T’s. This is a free webinar series they have every Tuesday and Thursday.  I was able to present my Scalable SSRS Reports Achieved Through the Powerful Tablix presentation. You can still go to their website and view it.

I also had the honor of presenting remotely to the LA SQL UG for their 10th anniversary!

February

This month was spent writing abstracts for the year…and still understanding my new user group. I was also being courted for what became my new job. You can read about it here.

March

March was extra special. I had the opportunity to present at the Silicon Valley SQL Saturday. It was extra special, because it marked my 1 year anniversary for speaking in the SQL community. I also had my largest class to date! 97 people! Here was my favorite tweet of the day too. (Thanks Glenn!)

April

This month was full of meetings for our local Huntington Beach SQL Saturday that I helped host at the end of April. It was great having SQL Family come out to my neck of the woods beach.

May

I didn’t speak anywhere this month, but I did spend time every weekend writing. (Actually, I write every month.) I really enjoy participating in the T-SQL Tuesday Blog Parties, writing for myself, and participating in #SQLCoOp with my friends Julie, Chris, and Jeffrey.

June

And here we are in June, where I decided I would do EVERYTHING. I’m writing, speaking, leading, writing, and participating in #SQLHangout. Oh, and I’m getting my first dog. (More on her in a moment.)

My friend Boris Hristov (b|t|f), from Bulgaria, invited me to participate in an “episode” of SQL Hangout. We hung out in our two countries with 10 time zones between us and chatted about data types. You might not think this is an exciting topic, but it is a cornerstone to all databases. We came up with some great reasons why all database professionals should care about the data types of every field in their tables. So grab some popcorn or a glass of whiskey and hang out with us for half an hour.

 

You can find out about up and coming SQL Hangouts by following #SQLHangout on twitter, and you can find the full list of recorded SQL Hangouts here.

This month, I’ve also been blessed with a co-leader for my (now our) BI user group. His name is Rob Hatton, and I’m really happy he asked to lead the group with me.

I also had the opportunity this month to drive out to Riverside to speak with the Inland Empire User Group. This is the third time they’ve had me present, but the first time I’ve actually presented in person. Riverside is not a quick drive from where I live, but my boss, Steven was happy to be a carpool buddy for me. It ended up being a perfect presentation for him to hear, since it was on source controlling your SQL scripts with Red Gates’ SQL Source Control.

Now we get to look into the future…

2014-06-15 22.26.14Well, not to far into the future. Tomorrow (Wednesday) I’m heading out to Kentucky for a week. One of the events on my vacation will be speaking at SQL Saturday #286, Louisville. I’m really looking forward to the event since I enjoyed it so much last year. My husband and I are also going whiskey tasting with friends, we’ll hopefully be visiting the Corvette factory, and we’ll be picking up this adorable Labradoodle puppy who we’ve named Lucy. She will be 10 weeks old, and I can’t wait to hold her.

Here is a list of other events that I’ll be speaking at this year. You can also go to my 2014 Speaking Engagements page for an updated list through out the rest of the year.

I’ve applied to a few other events, but the accepted speaker lists have not been sent out for those events yet.

I’ll also be attending PASS Summit 2014 in Seattle in Nov this year. I hope to see all of you there.

Data Models, SQL Server, SQLite, and PowerShell

The Fourth Mission

In this installment of SQLCoOp, we are sharing our views on something we’ve learned this month. While I learned quite a bit this month in order to create a new SQLite Database with test data that mapped back to my SQL Server database, I will limit this post to how my first PowerShell script aided me in creating a new SQLite Database from Embarcadero’s ER/Studio Data Architect.

MCJJ_ALL

The Project

I’ve been working on a project recently where we have a SQL Server backend on our server and SQLite databases on our client machines. The two databases have quite a few tables in common, but the two database platforms don’t share the same toolsets. Here are the hurdles I had to overcome.

  1. The SQL language that SQLite uses is similar to T-SQL, but not the same. This means scripting tables, constraints, and other SQL objects are slightly different.
  2. I use Embarcadero’s ER/Studio Data Architect (DA) to do all of my data modeling, but there isn’t a driver that will work with DA to compare my database model to a SQLite database so that I can keep them in sync.
  3. I needed to create test data in both databases, but I don’t have an environment where the databases can coexist so that I can write the proper insert scripts.

In this post, I’ll focus on the first two hurdles. The third hurdle was solved using Red Gate’s SQL DataGenerator, Python, and SSIS. I will save this story for another time since it is worthy of a blog post all on its own.

Hurdle 1: Understanding SQLite

The first thing I had to learn was the ins and outs of SQLite. I acquired a great book, Using SQLite and started reading. I also searched the depths of the internet. What I found was that SQLite likes to store almost everything as integer, numeric, and text, BUT there are data types that we are familiar with like varchar, datetime, etc. They just get “interpreted” as integers, numeric, and text depending on what the data types are. This is a good reference for understanding how the more familiar data types are actually stored in the SQLite database.

When I was first learning about SQLite, I mistook the storage classes as actual data types. I thought SQLite was going to use a type of implicit conversion, but SQLite is not the same as SQL Server. There is also a big benefit to using the more familiar data types instead of sticking to the storage classes that SQLite uses. The benefit has to do with the ADO.NET class and making life easier for your application developer who has to take data from SQL Server and get it into SQLite.

NOTE: You REALLY need to understand how dates work in SQLite. Depending on how the date and time are stored in the underlying database (string, integer, numeric) will determine what “zero” is equivalent to. Read this to find out more.

Hurdle 2: Creating SQL Scripts for Schema Changes From ONE Environment

There are several software packages out in the world that will allow you to get visual data models of SQLite tables, but I already use Embarcadero’s ER/Studio Data Architect (DA) and I didn’t want to learn another one. I also didn’t want to have my SQL Server data models in one software package and my SQLite data models in a different one. So I had to figure out how to get around this hurdle…and I used PowerShell to do it.

The first thing I did, was to create my data models. My first SQLite data model was based on my initial research which implied I should store all my data in integer and text data types since that is what the engine will be using to store the data. This was a poor assumption on my part. Since I had both my SQL Server and my SQLite database models in DA, I was able to easily update my data types in my SQLite database with their SQL Server counter parts. I did have to inspect each field manually since not all attributes were the same between the two databases. For instance, I have to use CURRENT_TIMESTAMP instead of getdate() and I had to model auto incrementing columns differently.

Note: This is a sample database

SQLCoOp_Image3

SQL Server database

SQLCoOp4_Image2

First iteration of my SQLite database

NOTE: I was able to use the Compare and Merge Utility by selecting the option to compare against another model.

SQLCoOp4_Image1

 

SQLCoOp_Image4

Second iteration of my SQLite database

Making a SQLite Script from ER/Studio Data Architect

Before we go down this rabbit hole, there is another piece of information that you need to know about SQLite. You can’t add foreign key constraints after the table is created. This is very frustrating for me since I know of some existing tables that need foreign key constraints and it will be a bigger task than it would be in SQL Server to get foreign key constraints applied. For this project, it means I need to script the foreign keys as well as the primary keys in the table creation script.

There are two operations that need to be done in order to generate a SQLite script from DA. The first is to use the Generate Database Wizard to generate a whole database instead of using the Compare and Merge Utility to compare the database model with the actual database. This operation is outlined below in steps 1 though 6. The second operation is to use a simple PowerShell script to make the necessary changes to the script so that it will no longer be a T-SQL script made to run in SQL Server, but a SQL script ready to run in SQLite. This is outlined in step 7 below.

Here are the steps you’ll need to do:

Step 1

Make sure that all auto incrementing fields are not set up as primary keys in the database model. The reason for this, is all auto incrementing fields are primary keys, so the syntax for creating an auto incrementing field is very specific.

Take a look at the EmployeeAward table in DM SQLite above. It is setup as an identity column, but not as a primary key. In order for me to have a visual cue that the field is a primary key, I used an object called a Domain. Domains are templates that can be used throughout the database model so that data types, defaults, and even definitions remain consistent and can easily be changed throughout an entire database model or across all database models if the Repository feature of DA is being used.

Step 2

Right click the physical model that you will be working with, and select Generate Database. You will be walked through a wizard. At the end you are going to want to save your settings so that you don’t have to select them again.

SQLCoOp_Image6

Step 3

On Page 1 of the wizard, you can create individual scripts or a single long script. I’ve been doing both so that I can select a single table later on if I need to regenerate it. You will also need to select the folder that you would like the scripts to be created in. I created 3 folders for my project.

  • MultiScripts (This will hold scripts for each object separately)
  • SingleScript (This will hold a single script that contains all the objects.)
  • Standardize (This holds the PowerShell script called StandardizeSQL.ps1 which will be explained in step 7.)
Step 3

I don’t do anything on page 2 of the wizard since this script will not run in SQL Server.

Step 4

Page 3 of the wizard is where the fun starts. Set the following options for the tables you want to include in the script. These options are found on the General Options tab. All the other tabs don’t need to have their options modified…unless you used features like sparse columns that can’t be used in SQLite. Then you might need to fiddle with some of the other settings.

 

SQLCoOp_Image5

Step 5

Save your settings on page 4 of the wizard so that you can quickly run through the wizard next time.

Step 6

If you are asked to open your newly created script, then say no. We’re not done yet.

Step 7

Run the following PowerShell script. You can do this by right clicking the file containing the PowerShell script and selecting Run with PowerShell. Note: The ability to run PowerShell scripts needs to be enabled first.

The script will go through all the *.sql files in the indicated folder and change the T-SQL specific syntax to the SQLite syntax. Here are the syntactical changes.

  • Int will be changed to integer.
  • Identity columns will be changed to primary key autoincrement columns.
  • The words nonclustered and clustered will be removed from primary and foreign key constraints since all indexes are nonclustered and those keywords are not supported in SQLite.

Before you run the script below, you’ll need to change the path to your scripts on line 8.

Note: there is a space before the asterisk on line 8 and there are spaces on either side of “int” on line 12.

# To Run this in the Powershell it would be written as ./StandardizeSQL.ps1
# Note: nonclustered needs to come before clustered since clustered is in the word nonclustered.

$wshell = New-Object -ComObject Wscript.Shell -ErrorAction Stop

Try
{
$configFiles=get-childitem -Recurse C:\SQLiteScripts *.sql

foreach ($file in $configFiles)
{
(Get-Content $file.PSPath) |
Foreach-Object {$_ -replace " int ", " integer "} |
Foreach-Object {$_ -replace "IDENTITY\(1,1\)", "PRIMARY KEY AUTOINCREMENT"} |
Foreach-Object {$_ -replace "nonclustered", ""} |
Foreach-Object {$_ -replace "clustered", ""} |
Set-Content $file.PSPath

}

$wshell.Popup("Success! And there was much rejoicing!", 0,"Done.",64)
}
Catch
{

$wshell.Popup($_.Exception.Message,0, "Oops! An error occurred.",48)
Break
}

After you are done running the PowerShell script, you’ll have a SQLite script that you can run in your favorite SQLite querying tool.

Here is the before and after scripts for two of the tables from my example.

CREATE TABLE Employee(
EmployeeID int NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID int IDENTITY(1,1),
EmployeeID int NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

Before

CREATE TABLE Employee(
EmployeeID integer NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
isActive bit DEFAULT 0 NOT NULL,
InsertedDTS datetime CONSTRAINT [DF_Employee_InsertedDTS] DEFAULT CURRENT_TIMESTAMP NOT NULL,
ModifiedDTS datetime NULL,
CONSTRAINT PK_Employee PRIMARY KEY (EmployeeID)
)
;

CREATE TABLE EmployeeAward(
EmployeeAwardID integer PRIMARY KEY AUTOINCREMENT,
EmployeeID integer NOT NULL,
AwardName varchar(100) NOT NULL,
AwardDate datetime NULL,
CONSTRAINT FK_EmployeeAward_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(EmployeeID)
)
;

After

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next time!!

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest

T-SQL Tuesday #54 – How LinkedIn and Red Gate Landed Me My Job

My friend Boris Hristov (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of interviewing and hiring. This happens to be an apropos topic for me, since I have a new job.

It was a dark and stormy night…

OK, it was 8:30 am on a Monday morning and it was a beautiful January morning. It is Southern California after all…

I was checking my email like I always do after I get my morning coffee. There was yet another LinkedIn request. I’ve gotten to the point in my career where I’m picky about who I connect with. Basically, I always keep my LinkedIn account up to date, I’ve stopped connecting with people whom I couldn’t possibly help because our professions are so different, and I don’t connect with recruiters unless they are on my good side. This particular LinkedIn request was from a CEO named Richard at a company called DeskSite. Little did I know it would change my professional life.

A String of Events

Let’s step back a few days. I was already at a relatively new job. I had been there for six months, but I had also been frustrated. It was not the position I was expecting. I decided to pray about it the whole weekend and figure out if I wanted to start interviewing again or if I wanted to stick it out for another six months. By Monday I had decided to start looking.

So, we’re back to the LinkedIn request sitting in my email box on a Monday morning asking if I want to connect. Normally I would have said no. “He’s a CEO. He’s not a SQL person. We have no common connection.”, I would tell myself. But I was in a good mood. I thought, “Sure. Why not.”

Within an hour, I had an email from Richard, and it said:
Hey, we’re looking for a Database Architect. I was wondering if you could spend 10 minutes on the phone with me to see if you could help us find one.

I happen to be a chapter leader of a local BI user group called BIG PASS Community. While our group is BI focused, I know we have database professionals that cover the board, so I agreed to speak with him to see if I could find a match.

Side Story: I went down to my car to speak with Richard so that my colleagues wouldn’t know. I didn’t want them to think I was looking for a new job. Unfortunately, I have a blue tooth speaker at my desk and it was close enough to my car that my phone started transmitting through it. (Face Palm) So, I had to move my car. Problem solved.

Richard painted a picture for me of his startup company, DeskSite. He then told me where it currently stood technically and where he wanted to take it over the next three years. He then asked if I knew anyone who might be interested in joining his team or if he could possibly lure me away from my current company.

I kind of stumbled over my words, “I…I’m available. I just decided…literally, this weekend to start looking for a new position.” I cannot tell you how happy I made him. By time we got off the phone, DSC_3187I had an in-person interview scheduled for the next night at their office.

Side Story: When Richard called to verify that I could still make it, he told me that he had seen my SQL earrings that I wore in one of my Avatars. He loved them! So I wore them to the interview.

When I arrived at their office, I was greeted with a lot of enthusiasm. They were so happy to meet me. You see, they had Googled me. They knew that I’m heavily involved in the PASS Community. They had even seen the YouTube video that Red Gate published of me speaking at one of their SQL in the City events in 2013. They absolutely loved my enthusiasm and my obsession with SQL. They had already decided they wanted me on their team. They just had to convince me that I wanted to be on their team.  (You see, I was at a startup when the dot com bubble burst. It makes me leery of startups.)

My half hour interview ended up being three hours. At some point I was offered a job. Richard then wanted to know what it would take to have me on his team. Normally I don’t bring up the fact I like to speak and attend conferences in the first interview, but I’m also not normally sought after. So I asked if they would send me to conferences. Richard didn’t even blink. He gave me an allotment of days AND a budget. Wow. I did tell him I needed it in writing. I learned the hard way by taking something like that on good faith.

I was not prepared to have left with a verbal offer in hand. I was definitely thinking this offer was too good to be true. So like a good data professional, I started researching.

Over the next week or so, I asked many of my SQL friends what they thought. I sent Richard quite a few questions about the position, the company, the stock options, the offer, and even the culture of the company. (If you have never worked at a startup, they are VERY different than a mid-size or larger company.) I also made two more visits to their office.

The first trip was at lunch. You see, Richard’s ideal company is more like a family and families eat together. For those of you who don’t know me, I’m a people person. I despise eating by myself. It depresses me. Now that I have a Kindle, I have gotten more used to it, but I still prefer to eat with people. The bottom line is, I loved the culture of the company and Richard was one step closer to getting me on his team.

The second trip was a technical trip. They wanted me to meet with one of the consultants they use, to make sure I knew what I said I knew, and to talk deeper about the technical environment I would be working in. This one hour interview ended up being three hours. I think the technical part was only an hour, the other two hours was about the company… And my acceptance of their offer. (Aaacckkkk!)

Side note. I don’t do anything without talking to my awesome husband. I did take a bathroom break and talked to him on the phone for a bit and he was completely supportive of me taking the position.

The money dance

This is the part that has always been hard for me. Making sure I get paid fairly. I was asked what I wanted for a salary. I spent several hours researching what my salary should be. I have a wonderful friend who I got to talk real numbers with. I knew that I had been undervalued 2 jobs ago, but that had to do with the growth I experienced at that company. I grew professionally so fast when I first got involved with PASS that my salary soon became disproportionate to my knowledge, but because of red tape, my salary could not be fixed.

Anyhow, I finally came up with a number and sent it in. They made me an offer based on that number. It was made up of cash and options in the company. Unfortunately the cash portion was much too low. I was crest fallen. I know I could make a lot of money when the company goes public, and I really do think it will, but I have a daughter who will be heading to college in two years. I can’t risk her education or her younger sister’s education.

So what did I do? I talked to my friend who was also crest fallen for me. He offered some great advice and helped me devise a counter offer. I thought for sure it wouldn’t happen.  A week went by with no word. I prepared to start interviewing with other companies.

Then the clouds broke

I then received an email apologizing for the delay. They were in the processes of acquiring a larger office space do to the growth of the company and it had taken up much of their time. They really wanted me on their team. They understood my financial needs, but they had to discuss my counter offer.

In the end we came to an agreement and I became a member of an amazing team. I have been at DeskSite for a month and a half as of this writing. I’m very excited about the challenges ahead of me and I’m happy about being part of an amazing team.

So you see, I owe my awesome job to LinkedIn and Red Gate. I suppose Google should be added to the list since it was used to find my SQL in The City video on YouTube. So, YouTube should be added to the list as well……………

Thanks for all the fish

Thanks go out to Boris Hristov for hosting this month’s T-SQL Tuesday blog party. I always love and appreciate Boris’ enthusiasm about participating in T-SQL Tuesday, so please visit his website at http://borishristov.com/.

New and Improved SQL Search By Red Gate

MCJJ_ALL

The Third Mission

In this installment of SQLCoOp, we are sharing our views on the newly released SQL Search 2.0 product by Red Gate. This amazing product is free to the public and can be downloaded here.

 


 

SQL Search is one of those amazing products that you just can’t believe they give away. While I always find it useful, I found it the most useful this year when I was getting to know a new (to me) database. Why? Because I could quickly and easily search all the databases for particular keywords. Below I’ve included some of my favorite Use Cases for using SQL Search.

Use Case 1: Where did I leave my TODO list?

One of the things I love to do is leave notes for myself in my SQL objects, but I’m not always good at remembering where those notes are. With SQL Search, I can find them easily by searching for the keyword TODO. Now I can go a step further and look for all the TODO’s that ALSO have my name.

 

I do this by using the AND operator to search for the two keywords todo and mstuewe. There are some special rules to note.

1. AND and it’s counter part OR are both case sensitive.

2. You can only use one at a time. I can have todo AND mtuewe AND 2014, but  I can’t have todo AND (mstuewe OR mickey). (Maybe in a future version – hint, hint.)

 

SNAGHTML341ae0

Use Case 2: How many databases do I need to look through?

George is new to the company and he needs to get to know how orders are stored in the database. He decides to use SQL Search to look at all the tables that have Order in their name. He is a bit shocked when he find out that there are hundreds of databases on the server and he is not sure which ones he should start with. George, luckily got some help from one of his co-workers Sarah. She told him he should start with three specific databases.

 

In previous versions of SQL Search, you could look through one database or all the databases. This could lead to a very long list of values if you have quite a few databases on your server. This is now a problem of the past. (And there was much rejoicing.)  Now you have the option of selecting only the database you are interested. You can even select the system tables which are all listed at the bottom  of the pull down window.

 

image

 

Use Case 3: Rolling the dice for a job.

imageLiz needs to find all the SQL objects that use the SalesOrder  table, but she isn’t interested in searching through tables and views. She does, however want to search through all the jobs first, and then she’ll take a look at triggers, stored procedures and functions.

 

I found this next new feature a huge bonus. Jobs are not something I’m looking through often, but now I have a way to search through them without any trouble. Simply, select Jobs.

 

I’m also very happy to be able to select ONLY the database objects I want to search through. For me this means I can ignore tables and views. (Yippee!)

Use Case 4: Getting to know your neighbors.

There have been some implicit conversion problems with some of the date fields in the database. Sarah has been tasked to verify that all fields that end in “DTS” are actually datetime data types and not date data types.

 

Red Gate has added the ability to see table definitions in the results pane. It’s a simple view of the table structure, but it beats having to go back to the Object Explorer to see the entire table structure. Now when you’re researching field data types, you can do it quickly and efficiently.

 

image

Bonus Tips

Here are a few bonus tips that make SQL Search even more useful:

1. You can highlight all the rows in the grid, copy it, and paste it directly into Excel for further reference. This can be very helpful if you are changing all the objects in the list and you want to keep track of the original list. (This isn’t a new feature, but it is an awesome one to know about.)

2. They have improved the indexing of the database objects. This is GREAT  news. In the past, if you were expecting a modified object to appear on the list and it didn’t, you would have to close SSMS and restart it in order for the SQL objects to be properly re-indexed. You no longer need to do that. Just go to the Index menu item, and select Reindex.

3. Each of the columns in the grid can be sorted. This can be very handy if you want to deal with all the tables at one time, or a particular schema first.

 

image

Don’t Stop Yet

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

 

See you next time!!

 

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

 

Original Post: On a SQL Collaboration Quest

On a SQL Quest Using SQL Search by Red Gate

OLYMPUS DIGITAL CAMERA

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra – Australia, and Jeffrey Verheul from Rotterdam – The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Original Post: On a SQL Collaboration Quest 

The First Mission

For our first collaborative entry in this blog series, we have chosen to investigate Red Gate’s FREE tool, SQL Search. The question we posed to each other was: What is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

The Search Begins

Right now I’m in retro-fit mode at work. I have an application that I recently inherited that needs a new feature. This requires me to add new columns to tables in the database(s), as well as find all the objects (Stored procedures, triggers, etc.) that reference the tables I need to modify to make sure they will still work and to add logic to support the new feature.

Red Gate has a whole suite of tools for me to use on my current adventure at work, but the tool I used the most this past week was their FREE tool, SQL Search. This tool will work with SQL 2005 and up.

Instead of writing about how to use SQL Search, I want to highlight some shortcut keystrokes that will improve the speed of using this awesome tool. Did I mention it is a FREE awesome tool?

The Task

I have a set of tables that I modified and I need to look at the code for all the objects that reference that set of tables to not only make sure they still worked properly, but to add code to support the new feature that was added. I’ll use the Adventureworks2008R2 database in my example below.

After I found the list of objects I needed to inspect, I did the following:

  1. Scripted out the object as an ALTER statement.
  2. Quickly searched for the table in the script (some of the scripts were 1000+ rows long).

Here is my favorite part

If you double-click one of the objects in the SQL Search list of found objects, that object will be found and selected in the in the Object Explorer. Unfortunately, if that object is a view, it takes several mouse clicks to get an ALTER script created, soooo

With the help of Mark Holmes (b|t), we figured out what the hot key combination is to script out the highlighted object as an ALTER statement. You ready? SHIFT  F10 + S+A+N. Yes, that is a lot of keys, but if you are like me, keystrokes are faster than mouse clicks.

Note: I remember it as SHIFT F10 + SAN

The CTRL + F shortcut will take you into the Find dialog box where you can search for the name within the script. If you had been searching  for the same value prior to this script, then F3 is your best friend and it will take you directly to the search term in the ALTER script.

But wait! There’s more

If you are a Red Gate junkie like myself and have SQL Prompt installed, then there is another shortcut you can add between SHIFT + F10 + SAN and F3.  If you use CTRL K + CTRL Y then you can have the alter script formatted to your taste. (aka, you can read it faster. Win!)

Note: I remember it as CTRL + KY

image

Scenario Time

So here is what it would look like if you were using the Adventureworks2008R2 database and looking for the Person table.

  1. Enter Person into the search field
  2. Select the “Exact Match” option. (Optional)
  3. Select the “All Objects” from the list of objects
  4. Select “All Databases” from the list of databases (The search term is automatically searched. Hitting ENTER is not required.)
  5. Click on one of the objects listed
  6. SHIFT  F10 + S + A + N to create an ALTER script
  7. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  8. CTRL  F to pull up the SSMS search window to search within the script
  9. Type in Person and click enter. You’ll be taken to the first instance of Person
  10. Use F3 to find the other instances of Person in the script

Now to look at a second script. You already have the list of objects, so you don’t have to search for them again.

  1. Click on one of the objects listed.
  2. SHIFT  F10 + S + A + N to create an ALTER script
  3. CTRL K + CTRL Y to format it (this step requires SQL Prompt)
  4. Use F3 to find the other instances of Person in the script

Repeat these four steps for each script you need to look at. Win!

Don’t Stop Yet

If you want to read more about SQL Search, don’t forget to check out these blog posts:

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

Tribal Award Winner for Best New Community Voice

2013 was an amazing year for me and winning this award was the cherry on top. I want to not only thank the people who voted for me, but for the people who nominated me.

Thank You!

I appreciate your belief in me and for accepting me into the SQL Family.

While we can’t all clap together, we can tweet our approval. Please help me recognize the four others who were nominated with me. It’s a big planet, and they have contributed in their countries and abroad to be noticed and nominated too.

  • Koen Verbeeck (b|t) from Belgium
  • William Durkin (b|t) from Germany
  • Rob Sewell (b|t) from United Kingdom
  • Mark S. Rasmussen (b|t) from Denmark

Very Sincerely,
Mickey Stuewe

Tribal Awards – Nominated For Best New Community Voice

In December Red Gate introduced the Tribal Awards. Some of the awards are fun like “SQL Karaoke Performer of the Year” and others are more serious like “Blog of the Year”. They collected nominations and I found out that I was selected for “Best New Community Voice”. I’m very honored to be included with the other four gentlemen… all from Europe.

  • Koen Verbeek from Belgium
  • William Durkin from Germany
  • Rob Sewell from United Kingdom
  • Mark S. Rasmussen from Denmark

Now that the nominees have been selected, the community can now vote for the winners of the Tribal Awards, but be quick. The voting ends on January 3rd with the winners being announced on January 7th.

SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

AdventCalendar17First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Oh where, oh where did my trigger go?

On Day 8, Day 9, and Day 10 I wrote about using 4 Change tracking fields in tables. Two of the fields, ModifiedBy and ModifiedDTS are updated in a trigger. If you are slowly making changes to many tables, you can loose track of which tables have the triggers you need.

This little snippet for SQL Prompt, called FindTrig, will get two result sets for you. The first result set is a comma delimited list of tables that have the ModifiedDTS field, but do not have a trigger with the pattern trg__u. The second result set will show you a the same list of tables and the triggers they already have. This will be helpful when the code to update ModifiedBy and ModifiedDTS was added to a different trigger than expected. You definitely don’t want two triggers updating the same two fields. That will cause an infinite loop of updates.

USE <DBName,string, $DBNAME$>
GO
/* ***********************************************************************************
Purpose:    Creates a comma delimited list of tables that may need a trigger to populate
			the ModifiedBy and ModifiedDTS fields
Author:     mstuewe
Date:       12/17/2013
************************************************************************************ */
DECLARE @Tables AS nvarchar(max) 

	SELECT
		@Tables = ISNULL(@Tables + ',', '') + t.name
    FROM
        sys.objects AS t
        JOIN sys.all_columns AS c ON c.object_id = t.object_id
        LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
                                                 OR tr.name = 'trg_' + t.name + '_u'
    WHERE
        t.type = 'U'
        AND c.NAME = 'ModifiedDTS'
		AND tr.name IS NULL
	ORDER BY 
		t.name

	IF @Tables IS NULL
		PRINT 'All tables have a trigger.'
	ELSE
	BEGIN
		SELECT @Tables AS TableNames

		SELECT
			t.NAME AS TableName
			,tr.name AS ExistingTrigger
		FROM
			(
			SELECT
				t.object_id
				,t.name
			FROM
				sys.objects AS t
				JOIN sys.all_columns AS c ON c.object_id = t.object_id
				LEFT JOIN sys.Triggers AS Tr ON tr.name = 'trg_' + t.name + '_iu'
														 OR tr.name = 'trg_' + t.name + '_u'
			WHERE
				t.type = 'U'
				AND c.NAME = 'ModifiedDTS'
				AND tr.name IS NULL
			) AS t
			LEFT JOIN sys.Triggers AS Tr ON Tr.parent_id = t.object_id
		ORDER BY 
			t.name
	END
GO

As an example, I created three tables with the four changing tracking fields, Employee, Phone, and PhoneType. I added the trigger to Employee and Phone, but the trigger for Phone had it’s name change. After looking at the results returned, I would know that I only need to create a trigger script for the PhoneType table.

 

AdventCalendar2013Day17_Img1

 

Note: You can download this snippet from my Script Library under Resources. It’s called FindTrig.

 

Previous Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

AdventCalendar16First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Three French Hens, I mean User Defined Functions

There are three kinds of User Defined Functions that you can create in SQL Server.

  1. Scalar Functions
  2. Multi-line Table Functions
  3. In-Line Table Functions

They all have a purpose but unfortunately the first two listed above are misunderstood and misused the most. Below are a couple of articles on the differences and the performance problems that can be caused by Scalar and Multi-line Table Functions.

I created this snippet called NewFun for SQL Prompt when I was first learning the differences between these function types, and I still use to today. Why? It saves key strokes.

USE <DatabaseName, string,$DBNAME$>
GO
--Use Shift + Ctrl + M to set Template Parameters
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'<schemaowner,string,dbo>.<sprocname,string,>') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
GO
/* ***********************************************************************************
Purpose:    [Brief Description]
Notes:      
Sample:    
            EXEC <schemaowner,string,dbo>.<sprocname,string,> [Parameters]
Author:     $USER$
Date:       $DATE$

Revision History
(Change Date)	(Author)		(Description of Change)
-----------------------------------------------------------------
************************************************************************************ */
CREATE FUNCTION <schemaowner,string,dbo>.<sprocname,string,>
(
	--Insert Parameters
)
RETURNS Table
AS 

	RETURN 
	(
		$CURSOR$
	)

GO

 

Note: You can download this snippet from my Script Library under Resources. It’s called NewFun.

Previous Post In Series: SQL Advent Calendar Day -15 – Naming Default Constraints

Next Post In Series: SQL Advent Calendar – Day 17 – Now, Which Tables Still Need a Trigger?

 

SQL Advent Calendar Day -15 – Naming Default Constraints

AdventCalendarDay15First Post in this series: SQL Advent Calendar 2013-Day 1- Placeholders in SQL Prompt Snippets

As I was hanging our family Advent Calendar up, I thought I would make one for you, my readers. I’ll be sharing my scripts from two of my favorite products as well as tips on how to create your own.

Standardization

I truly believe in having coding standards. I think the best standards that a team can have, are the ones that they are willing to adhere to. If no one agrees that commas belong on the left rather than the right, then why does your standards say they have to be on the left?

One standard that I really like to have, and is supper easy to do using macros,  is having decent names for indexes and constraints. DF_Mytable_1837489274 does absolutely nothing for me. But DF_Mytable_isActive, is helpful when looking at all the constraints on my table.

ER Studio DA has three macros to help with these naming standards. Name foreign Key Constraints, Name Primary Key Constraints, and Index Naming. When creating default constraints in ER Studio Data Architect ( ER Studio DA), I found that I was getting random numbers on my default names in SQL Server. This was especially true if I created my defaults from Domains. (Domains are templates for fields. I wrote about Domains here.)So I created an additional macro called Name Default Constraints-Selected to round out this collection of macros.

My particular standard for naming defaults is as follows: DF__. If you have different standards that can be derived from information in the field, then you can change the pattern on line 42. Take a look at lines 36 and 37 too. These two lines are necessary when working with Domains. They make it possible to edit the default name for a field that is tied to a domain.

This macro will perform two different actions. It will create a name for defaults that don’t have one, and it will ask the user if they want to change a name that does not adhere to the standards. After the macro has completed, a dialog box is displayed showing how many defaults were named and how many had their name updated.

'----------------------------------------------------------------------------
' PURPOSE:  	This macro will create and update Default Constraint names on
'				Attributes For All selected Entities. If the attribute is
'				bound, it will overwrite the bound default and name the default.
' Author:   	Mickey Stuewe
' Date:     	12/15/2013
'----------------------------------------------------------------------------
Option Explicit

Sub Main

	Dim objModel As Model
	Dim objSubModel As SubModel
	Dim objSelObject As SelectedObject
	Dim objAttrib As AttributeObj
	Dim objEntity As Entity
	Dim iCounter As Integer
	Dim iNCcounter As Integer
	Dim iunBoundCounter As Integer
	Dim strDefaultName As String

	Set objModel = DiagramManager.ActiveDiagram.ActiveModel
	Set objSubModel = objModel.ActiveSubModel

	If Not objModel.Logical Then

		' Iterate through all the selected objects in the current
		' model.
		For Each objSelObject In objSubModel.SelectedObjects
			If objSelObject.Type = 1 Then  'Entities
				Set objEntity = objModel.Entities.Item(objSelObject.ID)
				For Each objAttrib In objEntity.Attributes
					If Len(objAttrib.DeclaredDefault) > 0 Then
						'if the attribute default is bound, then unbind it so that it can be named.
						If objAttrib.DomainId > 0 Then
							'You have to override the bound default value and the default text (name).
							objAttrib.EnableOverride (1,True)
							objAttrib.EnableOverride (2,True)
							iunBoundCounter = iunBoundCounter + 1
						End If

						strDefaultName = "DF_" & objEntity.EntityName & "_" & objAttrib.AttributeName
						If Len(objAttrib.DefaultName) = 0 Then
							objAttrib.DefaultName = strDefaultName
							iCounter = iCounter + 1
						ElseIf objAttrib.DefaultName <> strDefaultName Then
							If MsgBox("The name of the Default Constraint does not match our naming standards. Would you like to replace it?" & vbCrLf & _
								vbCrLf & vbCrLf & "Attribute: " & objAttrib.AttributeName & " - Default Name: " & objAttrib.DefaultName & " - Value: " & _
								objAttrib.DeclaredDefault & vbCrLf & " Standardized Name: " & strDefaultName, _
								vbYesNo + vbQuestion, "Stadardize Name?") = vbYes Then
									objAttrib.DefaultName = strDefaultName
									iCounter = iCounter + 1
							End If
						End If
					Else
						iNCcounter = iNCcounter + 1
					End If
				Next objAttrib
			End If
		Next objSelObject

		MsgBox iunBoundCounter & " had their defaults unbound so that they could be named." & vbCrLf & vbCrLf & _
				iCounter & " Default Constraints had their name created or updated." & vbCrLf & vbCrLf & _
				iNCcounter & " Attributes were not modified.", vbOkOnly + vbInformation, "Default Constraint Name(s) Created or Updated"

	Else
		MsgBox "You are in a Logical model. Move to a Physical model to name of Default Constraints.", vbOkOnly + vbCritical, "Need Physical Model"
	End If
End Sub

 

Note: You can download this macro from my Script Library under Resources. It’s called Name Default Constraints – Selected.

 

Previous Post In Series: SQL Advent Calendar – Day 14 – Working With Forms in a Macro in ER Studio Data Architect

Next Post In Series: SQL Advent Calendar – Day 16 – Snippet For In-Line Table Functions

%d bloggers like this: