Cause I am the opposite of amnesia you can search for procs and views containing…

Recently I helped with a data center cut over.  Moving databases and making sure all the procedures and views inside them still worked was a high priority.  But how do you find all the Procedures and Views in a database that reference things outside the database?  I needed some kind of keyword search, like bingle for my database.  I found one that used the Information_Schema.Routines and improved on it so that it doesn’t cut off at 4000 characters. Here is the procedure that needs to be created:

CREATE PROC FindProcContaining
    @search VARCHAR(100) = ''
SET @search = '%' + @search + '%'
SELECT name, definition, type_desc
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE definition LIKE @search
ORDER BY definition 

This is what your execute will look like:

EXEC FindProcContaining 'SearchCriteria'

It will return a list of the View or Procedure Name, the Syntax if it is not encrypted and the type of either View or Stored Procedure. I was super excited about it.

In the music the moment you own it and can decode your database!

Yesterday I had the awesome opportunity to present at Big Mountain Data and SQL Saturday Salt Lake City.  I was super nervous, but I think it went well over all.  Huge thank you to the kind friends that sat in the audience to help build my confidence and for everyone that attended.  Here are the scripts that I promised to post.  If you would like the slide deck, it is posted on the Utah Geek Events website here:

The first script is the one that gets the row counts on each table so you can see what tables you want to look at and what tables you want to skip.

-- Shows all user tables and row counts for the current database
-- Remove is_ms_shipped = 0 check to include system objects
-- i.index_id < 2 indicates clustered index (1) or hash table (0)
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY ddps.row_count DESC

This next part is the second demo I did about digging through the database.

--What columns are in the Sales Tables?
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE LIKE '%Sales%'

--Column called "Order" something with amount?
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE LIKE '%Order%'

--OrderQty is the column I am looking for...
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE LIKE '%OrderQty%'

--How do I know for sure it is the table I want?
SELECT 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
--c.scale ,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('Sales.SalesOrderDetail')

This is the code from the third demo where I was looking for the foreign keys. I got this off stack overflow and it has been very helpful.

SELECT AS FK_NAME, AS [schema_name], AS [table], AS [column], AS [referenced_table], AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
WHERE = 'SalesOrderDetail'

The other demos that I did were opening Views and stored procedures and a walk through of how to use the Database Diagram feature.

Hope you all had a wonderful time at the event like I did!

It was the coldest night of the year a query with a lot of columns had me in tears…

Yesterday I was working with Jason so super big thank you to him for this script.  We were writing an insert statement and it had a lot of columns.  I was getting ready to script out the table when he showed me this little bit of code.  If you enter your database name and table name it will give you all your columns with commas.  You can even toss your alias in the query so you don’t have to spend a bunch of time adding it.

Declare @DBName as varchar(100)
Declare @tablename as varchar (100)

Set @DBName = 'MyDatabaseName'
Set @tablename = 'MyTableName'

, Stuff(
Select ', ' + C.COLUMN_NAME
For Xml Path('')
), 1, 2, '') As Columns
and T.TABLE_NAME=@tablename

I hope you enjoy it too!

Welcome to the new Deja Vu, you can populate a column from another database….

Greetings and other Salutations!
Yesterday I was working with someone new to T-SQL who needed to get data from one table in a database to another table in a different database. He was stumped and spent hours trying to find some way to do it. I was able to help him and realized this might be a common problem for people new to T-SQL.

We have two databases on our Instance SuperHero and Marvel and each database has a table Superhero.dbo.Character and Marvel.dbo.Bio.

First, prep the table you want to put data in. You won’t be using an insert statement for this task because in our scenario we already have data in our table. To prep my table I am adding a column at the end of my table and allowing it to have nulls.

CharacterID, Name, Universe, RealName(This is our new column that is null)
1, Spider-man, Marvel,
2, Batman, DC,
3, Iron Man, Marvel,

BioID, Name, CharacterID, RealName(This is where we will update our new column from)
1, Spider-man, 1, Peter Parker
2, Iron Man, 3, Tony Stark

Next, we join across databases. As you can see these two tables would join on CharacterID so:

SELECT C.CharacterID, C.Name, C.Universe, C.RealName, B.BioID, B.Name, B.CharacterID, B.RealName
FROM Superhero.dbo.Character C
INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID

The “INNER JOIN” also acts as my where clause so that it will only update records that match in both. Please always build and test your SELECT statement first to make sure it is going to update what you expect.
Now we build our update:

UPDATE dbo.Character
SET RealName = B.RealName
FROM Superhero.dbo.Character C
INNER JOIN Marvel.dbo.Bio B ON C.CharacterID = B.CharacterID

Be sure you are running on the Superhero database for your update.
Who knew bringing super heroes together would only take a query?

I can’t live without you, I can’t live without you SQL

By now you know I love Microsoft SQL Server. I get a lot of questions about how I got into database and why I got into database. One of the new people I am working with suggested I add the story to my blog. So here goes…

Life should be fun and money is a big part of life. I wanted to be able to work hard so I could play hard, but I also wanted to love what I did. I use the phrase all the time “When it stops being fun, it is time to be done.” Basically, when I don’t love what I do, I am miserable.

My first real job was as an Administrative assistant. I was terrible at it. I didn’t type well, hated writing memos, and dictation was a nightmare for me. The only think I really loved was when I got to do some mail merges (it was tables, I just didn’t know it yet) and changing the copier toner. You should have seen the day I opened the toner wrong and it went all over the mail room. It was such a mess. I would come home crying everyday because I knew I wasn’t good at it. I didn’t have fun with it. I finally decided that if I couldn’t love my job, I would get a dumb job to so I could go play more and call in sick when I needed a break.

I went to work at a call center selling long distance. I was good at it, and it was fun, but still not perfect. The fun part was the computer program we got to use. I learned it super fast and would help the IT guys test new upgrades. They were super nice to me, and one day suggested that I help train new people on the software we had been testing for roll out. After the training, working on the phone was so boring! The IT guys suggested I come work with them as a Business Analyst. I had no idea what that was or what they did, but it was a new adventure so I went for it. They had me learn Access and when I started pointing Access as the production database (because no one had heard of a snapshot reporting databases back then) it would slow the databases down. The awesome IT guys asked me to learn T-SQL. They gave me new tools and showed me a few things, pointed me to a few websites and it was like I had found the best chocolate ever! I continued to learn working as an Operations Admin and really loved learning SQL Server. I even started to dream in T-SQL. You can imagine my disappointment when I would wake up and my house hadn’t been cleaned by a T-SQL Query. Truncate Table dbo.Dust anyone?

Life changes and so did my opportunities, I worked as an Information Manager, Reporting Manager, and finally I got a gig as a DBA. It was one of the happiest days of my life. I felt like I had finally arrived. Little did I know that just having the title, wouldn’t give me all the answers. There have been a lot of moments of joy and may times my sweet husband has found me crying in my closet because I felt like I wasn’t good enough. I can be loved by 99 out of 100 people in a room and will see that 1 person that doesn’t like me or thinks I am not good enough. I will focus on them and try to change their opinion. When that happens, I go back to what my dear friend Tara said when I was first starting out: “Andrea if they don’t like you, it is because they don’t know you.” I think that is true for so many people.

Today as I watched Brent Ozar, Kendra Little and Doug Lane answer questions about presenting, I realized that many of us don’t feel like we are good enough to teach someone else. We don’t have every perfect answer, but that is part of the fun of SQL Server. There is always more to learn, a different way to do something and someone you can help. There will be people who are mean along the way, but if we focus on our passion and forget the rest we can find joy in the work we do. As Pat Wright says: “When you do what you love, you won’t work a day in your life.” My hope for you is that you find the thing that you love and make it work for you, so you never have to work a day again.

May you feel joy in the work you do today!

I got 99 problems but Autogrowth ain’t one because of CMS

I recently took over a new environment and have had my hands full exploring and setting up all my checks.  When I attended users group a few weeks ago I realized I was doing it the hard way.  I was connecting to each server one at a time to run my install t-sql scripts and checks.  Mike Tutor gave a fantastic presentation on CMS (Central Management Servers) in SQL Server and how to get started using it.  Today I had another issue with a database logfile that had autogrown to an unruly size.  I started to do the math and realized that if I didn’t learn how to use CMS, I would be fixing autogrowth settings all week.  So let’s begin.  This article assumes that you have already registered your Instances into Groups within CMS.  Right-click on your group and select New Query.  Run this query to see where you are at:

SELECT AS DBname, AS Filename, smf.size, smf.max_size, smf.growth, smf.is_percent_growth
FROM sys.master_files smf
INNER JOIN sys.databases sd ON smf.database_id = sd.database_id

This will give you both the Server Name and the database name on all your files if you are running in CMS but will also work if you are just on one local server.

Next, I am going to use Policy Based Management to Apply a Default Value across all my databases. On a test server, you want to create a new condition:

Whatever you set that @Growth value to is the value that will be set on all your files that you apply this policy on, please use caution and plan a value that will fit your growth needs.

Then pull it into a policy:

Right Click on the Policy and Evaluate the policy to make sure it will do what you are expecting it to do. The green ones were already matching what your policy would do. The red ones are the ones the policy would update. Under Details select View to see what the values are now. You can test out how it is going to work by checking a check box or two and selecting Apply.

Export the policy by right clicking on it and selecting “Export Policy”.

Then go back to your CMS Group and right click to Import Policy. Right click on the group again and select Evaluate Policies. Find the one you just imported and check the box for it and run it. This is the same as before, select the check boxes of the ones you want to update and Apply.

Next, I want to be able to control the rate each system database will grow, this is just an example. Please plan your growth and modify as needed. Right click on your CMS group and select New Query and paste this in then modify as needed. (You could also do this with a more specialized policy, but I wanted to use both.)





Many Chocolate Covered Gummy Bears gave their lives to bring you this information.

Please don’t stop the music…while I add the last day of the month to a table


I have this table that Finance uses to tell what the last day of any given month is in their reports. Through people coming and going, the keeper of this table didn’t keep it and the updates to it stopped.  Eventually, reports that used it stop moving forward as the dates didn’t exist.  I decided to create a job to update this table so it wouldn’t matter if someone forgot to update it.  It automatically will update each month (thank you SQLAgent).  But I was still left with the small problem of determining the last day of the month.  I found that the table had the last day of the month before it, so I am using that table to get the my next month end date.  The current table has two columns, the MonthEndDate and the Date that entry was created. I turned this select into an insert and ta-da we have the last date of the next month.

I add a day to the month end day so I get the first day of the next month.  Then I add a month to that and subtract to the day before the first of that month.

For Example:

MAX(MonthEndDate) = ‘5/30/2014′

Add a day and you get ‘6/1/2014′

Add a month and you get ‘7/1/2014′

Subtract a day and you get ‘6/30/2014′

SELECT DATEADD(day,-1,(DATEADD(m,1,(DATEADD(day, 1,MAX(MonthEndDate)))))),GETDATE()

FROM [dbo].[MonthEndDates]

*Update* Todd commented and showed me that you can just do this in SQL 2012:


That is super awesome!  Thank you for sharing Todd!