Tag Archive for Julie Koesmarno

Transaction Log Backups for the Accidental DBA

The Next Quest

In this installment of SQLCoOp, we are writing about backups. I have chosen to focus on Transaction Log Backups. Why? Well, at one of my former jobs, I found myself in the role of Accidental DBA. The company did not have automated backups. They were manually and infrequently performed. I knew this needed to be changed. And this is where my post begins.

MCJJ_ALL

Manual and Frequent Backups

Since I wasn’t a DBA, I started small. I performed manual full backups of our databases often.  I made sure that our backups were stored on a different LUN than our data files. It was working great…until it wasn’t. One day everything stopped.

Lessons Learned

I learned several things that day.

1. The difference between the Full Recovery Model and the Simple Recovery Model is the Transaction Log being generated. We were using a Full Recovery Model because I wanted transactions to be logged.

2. Creating a full backup does not affect the transaction log at all. You have to also create a backup of the transaction log. If you don’t, it will continue to grow until it fills the entire drive. And then your database will stop.

3. If you’ve created a dedicated drive for your log files and they use all the space, then your database will stop. You can’t grow the drive without emptying the drive first.

4. After (finally) getting a backup of the transaction log, the Log file will not change in size, even though it is “empty”. You will need to shrink it.

You see, log files can grow as more space is needed. When a backup of the log file occurs, the log file will stay the same size, but the data inside will be truncated. Once the log file uses all the space again, it will need to grow again. This is normal. The log file only needs to be shrunk when it is taking up the entire drive. Read this article for the technical explanation of how log files work.

5. If you get stuck and no one in the office can help, then go to twitter to ask for help. Use the hashtag #SQLHelp with a short synopsis of why you need help. I spent three hours fixing the situation with the aid of a SQL Community member. Why three hours? Well the drive was full and there was no way to get more space.

Tools For Next Time

Now I am armed with some tools that I can use to help prevent finding my log file drive full and tools to fix the problem when it does happen again.

1. The drive location of the log files needs to be monitored and an alert needs to be setup to email/text you when the drive is almost full. Make sure to test the alert. It won’t do you any good if it can’t get to you.

2. Set the maximum log file size to a specific value and the value needs to be smaller than the drive the log file is residing in. Paul Randal (b|t) has a great article called Choosing Default Sizes for Your Data and Log Files that talks about how to choose log file sizes.

The database will still stop if that limit is reached, but providing the drive isn’t full, you’ll be able to increase the size of log file so that the database can function again. Then you can fix the problem that caused the log file to fill up.

3. Create a large unimportant file. Name it something like ‘DeleteMeInAnEmergency.txt’ and put it on the drive where your log files are stored. This will guarantee that you will have extra room on your drive when a log file fills the drive. You can then move this unimportant file to another drive which will free up space instantly. You can get your database running again and then fix the problem that caused the log file to fill up.

4. Bookmark, memorize, or get a tattoo of this link from Glenn Berry (b|t) called How to Diagnose and Correct a “Runaway” Transaction Log. It has the steps to fix the log file and make it happy again. While you are there, check out his diagnostic queries to learn more about your database servers.

5.  Setup an automated backup plan. This can easily be done with Ola Hallengren’s (b) Backup Maintenance Solution (and it’s free). It is really easy to setup and configure to meet your needs. While you are there, you can also check out his Index Maintenance Scripts. They are wonderful.

NOTE: Always test code from the internet in development and QA before deploying to production.
Don’t Stop Yet

Don’t forget to check out these blog posts by the rest of the SQL CoOp team on the subject of Backups:

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

Where in the SQL World is Mickey Stuewe

PASSFlagI’m in countdown mode right now. Countdown to Halloween. Not because I get to dress up this Friday, but because I’m leaving for my favorite time of yearPASS Summit. This will be my first year speaking at Summit, but I’m getting ahead of myself. Let’s start at the beginning.

JulieAndMickeyMentorsOne of the most amazing things about Summit is the networking opportunities. These opportunities have the potential to start lifelong friendships. And that is where my post really needs to start, with my friend Julie Koesmarno. Julie and I were at most of the same events at my first Summit, but we didn’t get to know each other until after the Summit. Now she has flown in from Australia to spend a week at my place before we head out to Summit together. Thursday, two more Australian friends will be joining us here in Orange County before we fly up to Oregon together on Friday for SQL Saturday in Portland, where I’ll be presenting my Changing Your Habits to Improve the Performance of Your T-SQL session and taking as many classes as I can.

Sunday we’ll be taking a leisurely ride on a train up to Seattle. It will be the last day of relaxation before the madness of Summit related activities start.

SiTCMickeyMonday I will be spending the day hanging out with my favorite tool company, Red Gate. They are putting on a free one day conference called SQL in the City. This is an amazing day to network, eat great food, see amazing presentations, and end the day English style with a pint of beer. You heard me correctly. Red Gate is a British Company, and they always end SQL in the City with a pint of beer. The beer bottles have customized labels. Last year’s said “Query Hoptimizer”.

This year I have two presentations at SQL in the City. My first presentation is called Customize your faux test data with SQL Data Generator and focuses on how to generate test data. My second presentation is a lightning talk. I’ll be presenting on called Finding the delta with SQL Compare and backups.

Monday night I will be attending Andy Warren and Steve Jone’s networking party. (I hope to see you all there.) This is a great way to start off on the right foot at the Summit. You get to sit down to share a meal and network. I was introduced to many new people the first time I went, and yes, I remember quite a few of them. Tuesday is all about me. Ok, almost all about me. I signed up for an all-day precon on the topic of SSIS. I do have to step out in the afternoon to attend a chapter leader’s meeting though.

Then the even hits with Summit’s opening night event followed closely with several other events that I will be attending. (Sleeping has been rescheduled for later in November.)

On Wednesday, I’ve been invited to be at the bloggers table for the Day 1 Keynote. I’m very excited about this. So, if you are unable to be at the Summit this year and you want to know what is going on during the first Keynote presentation Wednesday morning at 8:15 am, then come hang out on my blog. It will automatically update until 9:45 am. Stay tuned to my Twitter feed as well, I might “practice” by live blogging one of the sessions on Saturday or Monday too.

PASS_Summit_2013_WIT-2Thursday is my big day. I will be presenting for the fourth time in a week, but it will be my very first time speaking at the PASS Summit. My presentation is entitled Techniques for Dynamic SSRS Reports. The presentation will be held in room 2AB at 10:45 am. Since it is Thursday, I’ll most likely be presenting in my Kilt. Many attendees wear kilts to celebrate Women In Technology (WIT). After I’m done with my presentation, I’ll be running over to the WIT lunch. The event has sold out, so I hope I can find a seat. Kimberly Bryant, founder of Black Girls CODE will be giving a keynote at the event. Thursday afternoon at 3:30 to 4:30 you’ll find me hanging out at the community center. (I hope you come by and say hi.) I’ll be there to network, answer questions about community, SQL, SSRS, and whatever else comes up. (All questions I can’t answer will be forwarded to Grant Fritchey since he has nothing to do until January when his first term on the PASS board starts.)

WendyMartinMickeyJuliePaulThursday night I hope to celebrate by singing SQLKaroake style at Busch Gardens, but you never know where I’ll end up. The evenings at the Summit are like being caught in a strong current. You never know where the evening will take you. The point is to enjoy the ride and meet new people.

Friday I’ll be getting my final learning on and attending the Birds of a Feather lunch. This is an event where you can sit with others to talk about specific SQL related topics. So, bring your questions and share a meal with like minded individuals.

Saturday I’ll be heading back to Orange County to give hugs to my family and my puppy before I crash and catch up on all my sleep.

Going Beyond The INSERT Statement

The Seventh Mission

In this installment of SQLCoOp, we are writing about CRUD. No, this is not about the stuff you scrape off the bottom of your shoes after hiking. This is Create, Read, Update, and Delete. I’ll be focusing on create, which is done with the INSERT Statement, but I’m going to go beyond the basic INSERT statement.

MCJJ_ALL

Multiple Rows

Let’s start with a new feature of the basic INSERTVALUES statement that we all know and love. In SQL Server 2012, Microsoft improved the INSERTVALUES statement, by allowing you to include multiple lists of values in the same query. This allows you to insert multiple rows of data with one INSERT statement. I find this syntax to to be a wonderful feature when I’m creating small sets of data using Excel.

Here is an example of what the syntax looks like. The values for each row are enclosed in parenthesis and separated by commas. In this example, I’m entering student scores for tests the students took at the end of September. After this one query runs, there will be 12 rows in the table.


CREATE TABLE StudentScore
(
     StudentScoresID int IDENTITY PRIMARY KEY
     ,StudentID int
     ,TestingRoomID int
     ,Score tinyint
     ,TestDateTime datetime
);

 

INSERT INTO StudentScore
     (StudentID,TestingRoomID, Score, TestDateTime)
VALUES
     (35, 1, 110, '2014-09-30 17:00:00.00')
     ,(36, 1, 87, '2014-09-30 17:00:00.00')
     ,(42, 1, 94, '2014-09-30 17:00:00.00')
     ,(10, 1, 99, '2014-09-29 12:00:00.00')
     ,(35, 10, 90, '2014-09-29 17:00:00.00')
     ,(36, 10, 100, '2014-09-29 17:00:00.00')
     ,(42, 10, 105, '2014-09-29 17:00:00.00')
     ,(10, 10, 99, '2014-09-29 17:00:00.00')
     ,(35, 5, 115, '2014-09-28 17:00:00.00')
     ,(36, 5, 70, '2014-09-28 17:00:00.00')
     ,(42, 5, 90, '2014-09-28 17:00:00.00')
     ,(10, 5, 67, '2014-09-28 17:00:00.00');
Common Table Expressions (CTEs)

I love CTEs, sometimes a little too much. The reason I think they are so great is two fold. First, they can make complicated queries easier to read and maintain. Second, they can perform complicated functionality such as recursion without breaking a query up into multiple statements. An added benefit, is they can be used with all CRUD statements, including the INSERT statement.

Let’s take a look at an example.

I have a group of students that were given the same test multiple times in different classrooms on different dates. I want to only insert the row representing  the highest score a student had. I also want to make sure that I keep the first time they received this score and any other data that came with that record.

Below I’ve highlighted the rows that I want to insert into the new table.

SQLCoOp7_Image1

The first step is building the SELECT statement for the CTE that numbers the scores for each student. This is done by using the ROW_NUMBER function in conjunction with the OVER clause. The OVER clause will have two parts. The first part will use the PARTION BY statement. This is used to restart the numbering for each partition. In this case we will partition the data by StudentID. The second part will use the ORDER BY statement. This tells us how to order the data so that it will consistently come out in the same row number. Since we want the highest score first, we will order the data by Score in a descending order. Then we will order the duplicates by TestDateTime so that the first date the score was achieved will appear first in the order.

SELECT
     ss.StudentScoresID
     ,ss.StudentID
     ,ss.TestingRoomID
     ,ss.Score
     ,ss.TestDateTime
     ,ROW_NUMBER() OVER (PARTITION BY ss.StudentID
               ORDER BY ss.Score DESC, ss.TestDateTime) AS RowID
FROM
     dbo.StudentScore AS ss

This SQL statement above will be used in our CTE. We’ll then use all the rows that have a RowID of 1 for the INSERT. The final INSERT statement will look like this.

CREATE TABLE dbo.StudentScoreHigh
(
     StudentScoresID int PRIMARY KEY
     ,StudentID int
     ,TestingRoomID int
     ,Score tinyint
     ,TestDateTime datetime
);

 

WITH cte_Scores
AS
(
     SELECT
          ss.StudentScoresID
          ,ss.StudentID
          ,ss.TestingRoomID
          ,ss.Score
          ,ss.TestDateTime
          ,ROW_NUMBER() OVER (PARTITION BY ss.StudentID
               ORDER BY ss.Score DESC, ss.TestDateTime) AS RowID
     FROM
          dbo.StudentScore AS ss
)
INSERT INTO dbo.StudentScoreHigh
(
     StudentScoresID
     ,StudentID
     ,TestingRoomID
     ,Score
     ,TestDateTime
)
SELECT
     StudentScoresID
     ,StudentID
     ,TestingRoomID
     ,Score
     ,TestDateTime
FROM
     cte_Scores
WHERE
     RowID = 1;
MERGE

A different way to approach this same problem, would be to use the MERGE statement. The MERGE statement, is like CRUD on steroids. It will look at all the data you specify that is coming in and compare to all the existing data. Then you can determine what will happen. Do you want to insert, update, delete, or ignore the data based on what does and does not match.

For this example, we’ll assume we are constantly updating the StudentScoreHigh table with the students highest score. We’ll compare what is already in the StudentScoreHigh table (the target) with what is in the StudentScore table (the Source) each night. If there is a record already in the table and the student achieved a higher score today, than the existing record will be updated. If the student does not exist in the table, then the student will be inserted into the table. Note: for this example, we’ll assume that the student could only take the test once in a day.

The MERGE statement will look like this.


MERGE dbo.StudentScoreHigh AS target
USING
(
     SELECT
          StudentScoresID
          ,StudentID
          ,TestingRoomID
          ,Score
          ,TestDateTime
     FROM
          dbo.StudentScore AS ss
     WHERE
          TestDateTime >= DATEADD(d, -1, GETDATE())
) AS Source ON Target.StudentID = Source.StudentID
WHEN MATCHED AND Source.Score > Target.Score THEN
UPDATE SET
     StudentScoresID = Source.StudentScoresID
     ,StudentID = Source.StudentID
     ,TestingRoomID = Source.TestingRoomID
     ,Score = Source.Score
     ,TestDateTime = Source.TestDateTime
WHEN NOT MATCHED THEN
INSERT
(
     StudentScoresID
     ,StudentID
     ,TestingRoomID
     ,Score
     ,TestDateTime
)
VALUES
(
     Source.StudentScoresID
     ,Source.StudentID
     ,Source.TestingRoomID
     ,Source.Score
     ,Source.TestDateTime
);
BONUS

If you have the latest Red Gate’s SQL Prompt installed (ver 6.4), then you will have an even easier time writing your favorite INSERT statement. They added a new feature that will highlight the field of the value you are modifying and vice versa.  Below you can see that my cursor is on line 61 and line 54 is highlighted. If I were to put my cursor on line 54, then line 61 would be highlighted.

 

SQLCoOp7_Image2

Don’t Stop Yet

Don’t forget to check out these blog posts by the rest of the SQL CoOp team on the subject of CRUD:

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

SQL Server Data Transferred to a SQLite Database Using SSIS

The Sixth Mission

In this installment of SQLCoOp, we are writing about SSIS. For me, this happens to tie closely with my the June SQLCoOp post I wrote, called Data Models, SQL Server, SQLite, and PowerShell. In that post I walked through how to create a data model in ER/Studio Data Architect and have it end up in a SQLite database. In this post I’ll show how to use SSIS to get master data from a SQL Server database and insert it into a newly created SQLite database that can then be deployed with client applications.

MCJJ_ALL

The Project

I needed to create a consistent way of pre-populating an empty SQLite database to be used in new builds of our application, so I turned my development SSIS package that moved test data from SQL Server into the SQLite database into a production SSIS package that pre-populated master data into a SQLite database.

Before you get started trying this example out, make sure that you have an ODBC Driver for the flavor of SQLite that you are using installed on your computer. Here is a great resource for ODBC Drivers. After the installation is finished, setup an ODBC connection to the SQLite database that you’ll be populating.

Step One

The first step I did, was run a DELETE statement for each of the tables that I’ll be populating. This allowed me to not worry about the destination database being populated from last time I ran the package. I used an Execute SQL task for each table. I set the Connection property to my ODBC connection I created and I set the SQLStatement property to the corresponding DELETE Statement.

SQLCoop6_Image1

 

SQLCoOp6_Image2

Step Two

For the next step I created a Data Flow task for each table. Inside each Data Flow task I created an ADO.Net Source and ADO.Net Destination tasks.

For each of the tables I wanted to populate, I created a unique stored procedure in a utility database called SSISMigration. This allowed me to customize the needs of each of the stored procedures without worrying about the needs of the applications using similar stored procedures in the source database.

I set the  ADO.Net Source connection to the SSISMigration database. I set the Data Access Mode to “SQL Command” and the SQL Command text to an execute statement such as “EXEC MSSL. GetAddressType”.

Side Note: If you want to pass a parameter to the stored procedure in a generic form, then check out the Expressions property of the parent Data Flow task.

 

SQLCoOp6_Image3

For the ADO.Net Destination task, set the Connection Manager property and select the table that you would like to have populated. Then click on the Mappings menu item to verify that the columns are mapped correctly between the Source and Destination tables.

SQLCoOp6_Image4

Step Three

Now I can connect each of my Execute SQL Tasks with their corresponding Data Flow tasks. This will ensure that the delete process occurs before the insert process.

SQLCoOp6_Image5

Step Four

The first time I built this process, I had all the statements connected in one big line. This made the processing an asynchronous process. I then learned that I could have multiple processes happening at the same time. But what if I have one last process that needs to occur after all other processes are finished? This is where the awesome Sequence Container comes into play. By placing all my Delete/Insert processes into the Sequence container, I can have them run synchronously, and still have a final process occur after they have all completed.

Step Five

Finally, I need to clean up after myself. Since I did some deleting and inserting, I want to make sure that my brand new SQLite database is as small as possible. SQLite has a command called VACUUM. This command rebuilds the database to remove any fragmentation caused by CUD operations. To run the VACUUM command, I use another Execute SQL Task. I set it up like I did in step one, with the SQL Statement set to “VACUUM;”.

Now I have three processes that occur synchronously and one process that happens after the first three are completed.

SQLCoOp6_Image6

Step 6

Run the package. If I’m only making this “new” master copy of the database for development, then I might just run the package from Visual Studio on an as needed basis. If I want this to be an automated process, then I can set the package up on SQL Server to run on a schedule.

But Wait, There’s More

My example here was for populating a new SQLite database with master data from SQL Server. I could use this same process for creating a subset of data for testing needs or for creating new SQL Server databases that need to be deployed in new environments.

Don’t Stop Yet

Don’t forget to check out these blog posts by the rest of the SQL CoOp team on the subject of SSIS:

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

Becoming a SQL Prompt Power User

MCJJ_ALLFour 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. This month we are cooperatively blogging about SQL Prompt by Red Gate.

The Next Mission

What makes up a SQL Prompt power user? Well, they use the SQL prompt commands like they were extensions of SSMS. They know the shortcuts, and they’ve memorized the key words to call up their favorite scripts. Let’s take a look what these short cuts are and how to integrate them seamlessly into your every day SQL Life.

Playing The Ebony Keys

A power user of any software is one who uses the hot keys in lieu of the menus. SQL prompt has a plethora of hot key combination.

Before I use <ctrl> + e to execute my code, I always use <ctrl> + k, <ctrl> + y. This hot key combo will structure your code based on the styles you have set in SQL prompt. This is my most heavily used hot key combo right after <ctrl> + e and <ctrl> + r (hiding the results window).

There is a new hot key combination that is coming out soon in version 6.4, and I’m very excited about it. It adds a semicolon at the end of each statement! I’ve been trying to add the habit of using semicolons for 2 years now, and it never sticks. Now I can use <ctrl> + b, <ctrl> + c and away I go.

Here is a table of the hot key combinations I use the most, plus the shortcut way I remember them.

Hot Key Combination Shortcut Hot Key Combo What It Does
<ctrl> + b,
<ctrl> + w
<ctrl> + bw Expands ALL of the wild cards.
<ctrl> + b,
<ctrl> + q
+ bq Qualifies all the SQL Objects and fixes the capitalization as well.
in beta <ctrl> + b,
<ctrl> + c
<ctrl> + bc Adds semicolons to the end of each statement. (Available soon in 6.4)
<ctrl> + k,
<ctrl> + y
<ctrl> + ky Standardizes the code based on the formatting styles that were set in SQL Prompt.

As a power user, my key strokes will look like this when I use them all at once:

<ctrl> + bw, bq, bc, ky and finally <ctrl> + e to execute my code. Here is an example of how an ugly duckling turns into a beautiful swan by applying all of these hot keys at one time.

Before


alter procedure [dbo].[salesteamassignment_rptugly](@group as varchar(1000), @startdate as date, @enddate as date)as
set nocount on
declare @uselessvar as varchar(max)
select
p.businessentityid ,firstname,lastname,s.name as store,[group] as TerritoryGroup,cr.name as CountryRegion,st.name as Territory,customercount,s.*
from
adventureworks2008r2.sales.salesperson as sp join adventureworks2008r2.person.person as p on sp.businessentityid = p.businessentityid
join adventureworks2008r2.sales.salesterritory as st on sp.territoryid = st.territoryid join [adventureworks2008r2].[person].[countryregion] as cr on st.countryregioncode = cr.countryregioncode
join adventureworks2008r2.sales.store as s on s.salespersonid = sp.businessentityid join dbo.fn_split(',', @group) as grps on st.[group] = convert(nvarchar(50), grps.separatedvalue)
outer apply (select count(*) as customercount from adventureworks2008r2.sales.customer as c where s.businessentityid = c.storeid ) as cc

After

ALTER PROCEDURE dbo.SalesTeamAssignment_rptUGLY
(
@group AS varchar(1000)
,@startdate AS date
,@enddate AS date
)
AS
SET NOCOUNT ON;
DECLARE @uselessvar AS varchar(MAX);
SELECT
p.BusinessEntityID
,p.FirstName
,p.LastName
,s.Name AS store
,st.[Group] AS TerritoryGroup
,cr.Name AS CountryRegion
,st.Name AS Territory
,cc.customercount
,s.BusinessEntityID
,s.Name
,s.SalesPersonID
,s.Demographics
,s.rowguid
,s.ModifiedDate
FROM
AdventureWorks2008R2.Sales.SalesPerson AS sp
JOIN AdventureWorks2008R2.Person.Person AS p ON sp.BusinessEntityID = p.BusinessEntityID
JOIN AdventureWorks2008R2.Sales.SalesTerritory AS st ON sp.TerritoryID = st.TerritoryID
JOIN AdventureWorks2008R2.Person.CountryRegion AS cr ON st.CountryRegionCode = cr.CountryRegionCode
JOIN AdventureWorks2008R2.Sales.Store AS s ON s.SalesPersonID = sp.BusinessEntityID
JOIN dbo.fn_Split(',', @group) AS grps ON st.[Group] = CONVERT(nvarchar(50), grps.SeparatedValue)
OUTER APPLY (
SELECT
COUNT(*) AS customercount
FROM
AdventureWorks2008R2.Sales.Customer AS c
WHERE
s.BusinessEntityID = c.StoreID
) AS cc;

But Wait, There’s More

Formatting your code is only half of the features in SQL Prompt. There are three other features that every power user should use. (Ok, the truth be told, there are more than 3, but my article shouldn’t be the length of a book.)

Feature 1:

SQL Prompt offers the ability to save snippets of code. This is really useful for repetitive tasks. Here is a list of articles that I’ve written talking about specific snippets that I find handy.

Placeholders are one of the most helpful features that SQL Prompt provides for you to work with snippets. The placeholders are reserved words that are variables for specific pieces of information or represent actions that can be applied to your snippet. They are always completely capitalized and are surrounded by dollar signs ($). You can find the current list for SQL Prompt 6 here.

One of the new placeholders is prefect for the power user. It allows you to insert a snippet and have part of it highlighted. Here is an example of how I like to use it. This particular snippen, which I named “st1”, will add SELECT TOP 100 * FROM in front of the table name that I”ve highlighted and copied to the copy buffer.

$SELECTIONSTART$SELECT TOP 100 *
FROM $PASTE$$SELECTIONEND$

The $PASTE$ placeholder will be replaced with whatever is in my copy buffer. The $SELECTIONSTART$ and $SELECTIONEND$ placeholders will cause the code within the placeholders to be highlighted.

To use it, I follow these steps.

1. I highlight a table I want to use and hit <ctrl> + c to get the table  into my copy buffer.

SQLCoop5_image1

 

2. I type st1 + to get the snippet. I now have a highlighted SELECT statement ready to execute, so I hit + e to execute it.

SQLCoop5_Image2

 

3. Finally, I type <ctrl> + z twice to undo the changes I’ve made.

SQLCoop5_image1

 

This allows me to quickly see some sample data from the table without ever leaving my current query window.

Feature 2:

This is a hidden gem. This feature finds all the invalid objects in your database. This is a great feature for those of you who need to perform code reviews and want to make sure that any DDL changes have not broken any existing SQL Objects.

In this example I have a table with a trigger that uses the field ModifiedDTS. I deleted the field and ran the command. It listed all the SQL Objects that are now broken due to the deleted field.

SQLCoop5_Image3

This feature does not have a Hot Key, but it is definitely worthy of a power user.

Feature 3:

This is a great “clean up” feature, especially when you are working with stored procedures with 200 + lines that you have inherited. You know the ones, they are filled with old code and have never been cleaned up. Well, this feature is perfect for it, because it finds all the unused variables and parameters. Simply use the hot key combination + b, + f. SQL Prompt, will then underline in green all the unused variables and parameters for you to evaluate for usefulness.

SQLCoop5_Image4

 

I encourage you to spend 10 minutes going through the SQL Prompt menu to find out what other goodies exist for you to leverage. There are also new features coming soon in version 6.4. Here is a great reference to all the release notes for each version, plus the release notes for the current beta version.

Don’t Stop Yet

If you want to read more about how to work with SQL Prompt, then 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 month!!

Original Post: On a SQL Collaboration Quest

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

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

A Date At The End of The Month

MCJJ_ALLFour 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.

The Second Mission

This month our team is tackling windows functions. My particular take on the subject is near and dear to my reporting heart. How to get date ranges based on full months.

End of the Month

Dates tend to be a critical component of most metrics. They define the boundaries of the data. They create groups of data that can help with trending patterns and forecasting. The downside has always been the slicing and dicing of the date. For instance, if you need to group all the data by the last day of the month. You need to create an equation like this to determine the last day of the month:

DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + ‘-01-‘ + CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))

Or this:

DATEADD(month,1,DATEADD(DAY,-DATEPART(DAY,GETDATE()) – 1,GETDATE()))

In SQL2012 the EOMONTH() windows function was introduced. Now you just need to type this:

EOMONTH(SomeDate)

Performance Perks

Not only are you able to write a simpler equation that is easier to maintain and understand, but you get  some performance perks too.

I ran a query using both the “old way” and the “windows function way”  against a table I created with a million rows of data. The data set returns 561 rows of aggregated data. I aggregated all the data by using the last day of the month. I then looked at the execution plans and the statistics for these queries.

SELECT
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))
     ,COUNT(SomeDate)
FROM
     DemoProgramming.dbo.TestData AS td
GROUP BY
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))
ORDER BY
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))

SELECT
     EOMONTH(SomeDate)
     ,COUNT(SomeDate)
FROM
     DemoProgramming.dbo.TestData AS td
GROUP BY
     EOMONTH(SomeDate)
ORDER BY
     EOMONTH(SomeDate)

Findings

1. When breaking up a date to reconstruct it, you need to go back and forth between data types. The optimizer isn’t very fond of that. In fact you get a warning about the possibility of your cardinality being questionable. The EOMONTH() windows function doesn’t have this problem.

 

SQLCoop2_ExecutionPlan

 

2. The EOMONTH() windows function takes less CPU and can return values faster. For this particular execution, I received a 5.25 times faster CPU time and 3.5 times faster execution time.

I used the following code to turn on Statistics:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

 SQLCoop2_Statistics

 

But Wait! There’s More

This particular function has an additional (optional) parameter. The parameter will allow you to add/subtract months from the end of the month. Which means you can easily find the beginning of the month using this equation:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) as boMonth

This also allows you to easily create a one year range for the last 12 full months using these two equations:
SELECT
DATEADD(DAY, 1, EOMONTH(GETDATE(), -13)) AS StartDate
,EOMONTH(GETDATE(),-1) AS EndDate

Outside the Box

You are probably telling yourself that you can do everything I just showed you by grouping data by the Month() function. Yes, that is true, but you need to think outside the box for the application. What about those wonderful companies who think Months are not contstrained by Day 1 and Day 31? (You know who you are. They have business rules like, “the 5th day of the month”. Ah! Now we got something we can work with.

SELECT DATEADD(DAY, 5, EOMONTH(GETDATE(), -1)) as FifthDayOfMonth

Don’t Stop Yet

If you want to read more about Windows Functions, 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 month!!

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.

On a SQL Collaboration Quest

Announcer: Twelve SQL Professionals started on a quest.

Director: No, no. They were FOUR SQL Professionals.

Announcer: Four Professionals started on a quest to find the sequel to the Holy Grail.

Director: No, no. They were SQL Professionals and they were on a quest for knowledge.

Announcer: Oh, right, right.
Four SQL Professionals started on a quest for knowledge and not for the Holy Grail. All though it could be said, that knowledge is the ultimate Holy Grail and it is always sought by the wise. The four SQL Professionals, Mickey Stuewe, Chris YatesJeffrey Verheul, and Julie Koesmarno (MCJJ for short) gathered from the four corners of the world: California – USA, Kentucky  – USA, Rotterdam – The Netherlands, and Canberra – Australia to meet at a local Skype tavern to determine the direction they would take on their quest for knowledge, and not for the Holy Grail.

After much eating and drinking, they rejoiced (Yeah!!!!). They had found the path for salvation.

Director: No, no. They found the path for their quest of knowledge.

Announcer: Right, right.
After much more eating of chocolate and drinking of whiskey, they rejoiced (Yeeeaaah!!!). They had found their path for knowledge. They decided they would pose questions to each other and respond as a group through their blogs at  mickeystuewe.com, chrisyatessql.wordpress.comdevjef.wordpress.com, and mssqlgirl.com. They decided they would also ask other SQL professionals they encountered along their way on their quest.

So now here, their quest begins at the Bridge of Death… (I hope they know the answers to the questions, or this collaborative series will be very short.)

BridgeBridgekeeper: Stop. Who would cross the Bridge of Death must answer me these questions three, ere the other side he or she see.

Lady Mickey: Ask me the questions, bridgekeeper. I am not afraid.

Bridgekeeper: What… is your quest?

Lady Mickey: To seek knowledge about the SQL world and not to seek the Holy Grail.

Bridgekeeper: What… is your favorite color?

Lady Mickey: Red.

Bridgekeeper: What… is the purpose of Red Gate’s SQL Search and how do you use it in your daily work life?

Lady Mickey: Funny you should ask that. I have this post that I’m publishing tomorrow. In fact, we each have a unique answer to that question. ButYou’ll have to wait until 2pm GMT tomorrow.

Stay Tuned

Please visit the following links to see the unique views of my collaborators.

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

%d bloggers like this: