Find Any Text within a Stored Procedure

Published 6/8/2015 by KDub in SQL
Tags: ,

Use text matching within a asystem proc to find some text within your own stored procedures.



FROM sys.procedures

WHERE OBJECT_DEFINITION(object_id) LIKE '%year%' --change this to something you know is in the SP's t-sql


Find Any Column Name in a Table or View

Published 6/8/2015 by KDub in SQL
Tags: ,

I use this little script daily to find fields to join on, fields to include, fields to reference, and sometimes I know I have even called something similar in a view, but can't quite remember what I named it.

This uses 'like' logic to go through tables and view to find anything similar to what you are asking for. Give it a try and see if it helps you find things and save time.


SET @cname = 'weight' -- change this for table or view column name you are looking for


--Find column name from all tables in a db

SELECT SCHEMA_NAME(t.schema_id) AS schema_name

       ,t.NAME AS table_name

       ,c.NAME AS column_name

       ,dt.NAME AS DataType


FROM sys.tables AS t


INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id

WHERE c.NAME LIKE '%' + @cname + '%'

       AND dt.NAME <> 'sysname'




--Get the same from all the views

SELECT SCHEMA_NAME(v.schema_id) AS schema_name

       ,v.NAME AS View_Name

       ,c.NAME AS column_name

       ,dt.NAME AS DataType

       ,c.max_length AS Size



INNER JOIN sys.types dt ON c.system_type_id = dt.system_type_id

WHERE c.NAME LIKE '%' + @cname + '%'

       AND dt.NAME <> 'sysname'

Hat Tip to Vivek Johari

Published 1/8/2015 by KDub

My friend Vivek Johari recently published a post on his blog that references a snippet I helped him with. Thanks for the mention, Vivek!

SQL Script to find the last executed commands on the SQl Server

This T-SQL Tuesday post comes to you courtesy of Jason Brimhall (B | T | G+) regarding standing firm in something you did or some action you took in your career.

From his blog:

The idea for this theme is to start with a little self reflection. Then to come up with a story relating to one of these words: resolve, resolution, or resolute. Here are some examples of how these stories may be portrayed.
  • Resolve: A system outage occurred and you “resolved” it. 
  • Resolute: You made an executive decision and did not waver from it.
  • Resolution: You discovered a bug and documented a work-a-round resolution for it.
  • Resolution: You have discovered certain T-SQL skills are fuzzy and want to sharpen your ability in that area. 
  • Resolute: You are determined to improve performance in your application.
All of these words are very closely related. It is up to you to determine how you would like to apply them to your T-SQL world. Your experiences and stories can be loosely or tightly coupled to T-SQL, it is up to you.

So, having set this up, I would like to tell how I Resolved to change my career form Jack-of-All-Trades in the IT realm to something more than just an Accidental DBA.


Having been in IT for 16 years, I found myself in a position of being able to handle just about anything that walked in the door. From DOS commands and scripting to big server hardware to networking, routing and switching all the way through to VoIP. I started in the UNIX world, and that having all but faded away, pushed Linux as far as was acceptable in our Enterprise and I still did not find any single technology that could hold my passion as well as UNIX could.


Then came SQL. Finally, a technology deep enough to give me a challenge. I still held on to all my other jobs because it paid the bills, but now things seemed different. When I was working on SQL, other things that came in and needed my attention now seemed to annoy me. I was starting to view them all as distractions. I was an accidental DBA, but somehow that was taking over my focus. My wake up call came 9 years into a stint with my employer (yes, I know, unheard of), when company layoffs and cutbacks started happening. I was not too worried as it would take them hiring about 3-4 heads just to cover my load, but still, I yearned for a more singular purpose. I talked with a few colleagues and they all told me to go for it. I made an Executive Decision: I stripped my resume of all items that were not SQL related. Yeah, it looked rather scary. I had not spent close to two decades building a skillset just to omit 90% of it for my next job. But it had to be done.

And the rest, as they say, is history. This past year has been a dream. All SQL, all the time. I am even doing some Data Architecting now, working with the Dev team writing some new in-house apps.


The moral of the story: Don't be afraid to chase your passions. Even if it seems like you are going against the grain to get there, sometimes that is exactly what it takes. Let nothing stand in your way - be Resolute.

One of the things I love about #SQLFamily is that no one is afraid to give out information for free in order to help someone else. The Twitter hash tag #SQLHelp is the single quickest way I know of to get the greatest minds in the SQL community to respond with answers to any question you may have regarding SQL. Fixing problems, asking How-To questions, they all flow freely from this community with no strings attached.


I asked a colleague of mine, Grant Fritchey (B|T), when I was quite young in the SQL world, how I could give back to the community that had just recently helped me get through a major problem I was having. I told him I couldn't believe how rich the sense of community was and how everyone was so helpful and generous. In fact, it weighed on my conscience so much that I felt like I was practically demanding he tell me how I could give back and feel like I was a part of this awesome society. Grant told me the usual things: Answer questions on, watch and answer questions on #SLQHelp on Twitter, volunteer for #SQLSaturdays, and rallies, blog, get involved in my local SQL community functions, and mentor colleagues when the opportunity presents itself. Essentially, give back in any way that I could. For some reason, the mentoring idea struck a note with me.


Mentoring is something I have always done. I have not always known how to do it, but I have perfected my approach over the years and have always tried to give of my knowledge without fear of being replaced in an organization. When I started out, I had a very hard time breaking into IT. It was my second career. I was in the Coatings industry before and decided to go back to school, get my degree in IT and make a new start. I did all that, and the job never came. I spent 6 years waiting for a break in IT. When I finally got my first job, I was in over my head, untrained, unskilled and floundering. A mentor would have been a Godsend. I spent the first 7 years of my new vocation becoming a UNIX expert and learning enough about all the peripheral technologies to get me by. A mentor could have helped with that. All the while, UNIX was a dying horse and Windows was making its way into the Data Center in a big way. A Mentor could have seen that coming and saved me a bunch of heartache.


See a common thread here? A Mentor can fast-track your career and your life. Become a mentor. Pay it forward. Benjamin Franklin, in his letter to Benjamin Webb, understood this concept.


A Mentor (an influential senior sponsor or supporter) can shorten the time it takes for a person to reach a certain point in his/her career. The time taken to learn new skills can be shortened, skills that are learned from experience and not taught in schools can be passed to mentees without the pain of having had to learn them through trial and error. Mistakes can be avoided, errors can be averted, time can be saved, productivity can soar. Become. A. Mentor.


Additionally, you will not believe the feeling that you will get when your mentee acknowledges your efforts, when you finally have evidence that you have done some amount of good in the world. Sometimes it is a subtle awareness, but other times it comes in the form of outright appreciation, completely unexpected and out of the blue:

This was in the form of an instant message from someone I had not talked to in half a year.


A Mentor can fast-track your career and your life.

And you may never know the impact you are having, or see the fruits of your labor (Grant). DO IT ANYWAY. Have faith that you are making a difference. Your evidence will come. Be cunning, and be not a knave. Make the most of a little. It's Everyone's Job.

Welcome to #TSql2sday issue #35, this time hosted by Nick Haslam. The topic is Soylent Green, named after the movie, all about horrible things. The challenge is to not only make a post, but to make a post about SQL that makes one wince, and have it contain T-SQL about the incident.



In working at my current company, there are several things I did when I walked in the door, one of which was to run the fabulous sp_BLITZ – SQL Server Takeover Script from Brent Ozar (B / T) which told me most everything about my environment that was not 'just right'. I found a significant amount of things, but of note were excessive use of sa, and production ERP databases still at SQL 2000 version.

People: it's 2012. Time to move along.

Do you have any idea how accustomed to DMVs I have become? They are useless here.

So, I hope I have hit 'Green'  and not 'Red' or 'Yellow' with this tiny little blog post for #TSql2sday. If not, go here and get you some.

Thanks to Adam Machanic (b|t) for making it possible!



Some of you may have a need to publish Reporting data over the web. I have looked into several ways to do this and this is the most 'Microsoft' way I could find to do it.

Assuming you have a web server running IIS that is capable of serving ASP.NET pages, open Visual Studio and create an ASP.NET page. Add the ScriptManager and ReportViewer controls from the toolbox to the design surface.

Once you have added these your page should look like this:

Now, add your Report Server URL (Inside address) and the Report Path to the report directory to the ReportViewer Control, as shown below:


Deploy this ASP.NET page to your web server.

Your web server MUST be able to communicate with your SSRS server, both over some network path and with credentials to run the report. Your SSRS server does NOT need to be open to the internet. Set this up as a Web server on the DMZ with everything else staying inside the network and this works great -  standard web serving configuration.


The cool thing about this is all of your SQL calls go from the embedded controls to the SSRS server, not out over the internet. And your existing Parameters in your reports work just like they do locally. You may want to lock this down with Microsoft Membership and Roles via ASP.NET web services. Make sure you create one ASP.NET page like this for every SSRS report you want to serve publicly.


Now that I am developing more often, I have discovered a little SQL quirk that took me a while to figure out. If you are a 'real' developer, this may be common knowledge, but to me, it was quite an annoyance.

When I add a new table into my database and then try any DML on it, IntelliSense would tell me that the table, and any of its members, did not exist.

No matter what I tried, the editor did not know anything about my new objects.

Enter the IntelliSense Cache. It seems that it holds on to items for quick retrieval, but does not refresh very often.


There are two ways to update this cache:

  1. Use menu items, EDIT > IntelliSense > Refresh Local Cache.
  2. Simply press Cntrl+Shift+R.


Life is good again.

This may not mean much to you, but hey, let me marvel in my small discoveries!

Well, actually defrag, as in "should I defragment my indexes?" But, what I am really talking about is the method you use to defragment.

There are several schools of thought on this, and numerous scripts that you can snag from various places on the web that will help you do it, but ultimately it comes down to what works for you. I am talking about DBCC INDEXDEFRAG vs. DBCC DBREINDEX (SQL2005) or ALTER INDEX REBUILD vs. REORGANIZE (SQL2008+). Basically, a REORG vs. a REINDEX. Everyone will cite "Rules of Thumb", but a rule of thumb is what works for most cases. Most cases do not necessarily include my case, so I immediatly start to suspect these baseline rules - until proven. In my view,  a "Rule of Thumb" is considered a Myth until proven true. Some of my fellow SQL bloggers have based entire blog series' on busting SQL Myths. For me, I am learning to consider "Rules of Thumb" as potential myths, begging to be debunked. And this topic is no different, so on to the task at hand.

Whether your version of SQL requires the DBCC or ALTER syntax, the decision is the same: Do I rip all my indexes off and rebuild them, or do they just need tidying up? Both of these operations are expensive, but the requirements are different. A REINDEX requires that your database be out of service, as in no one using it. It is an OFFLINE operation. If you have a large database with many tables, or one with very large tables, or both, you may find yourself in a position that requires extended downtime. What if you don't have that time? Wouldn't it be nice if you didn't have to interrupt production at all? Well, you don't have to, just keep reading.


A REORGANIZE, according to Microsoft, is an online operation and can be done while the database is accepting transactions.

REORGANIZE Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.

Of course there will be a performance hit while this operation is running, but some temporary system slowness is better than hours of maintenance downtime. Time is money. If the db ain't runnin', you ain't makin' money. (Sorry for that, it's how we say it in North Carolina)

Now comes the part where all the senior DBAs and MVPs will probably chime in and say I am wrong. I say Bring it. Numbers don't lie, and unless someone can prove to me I am doing damage, all I can say is that these numbers look good to me. The "Rule of Thumb" here is fragmentation threshold, and to a lesser extent, number of index pages. For example, the "Rule" will say, an index with < 30% fragmentation is a good candidate for a REORG: anything greater and its a REINDEX. I say do a REORG anyway. I have seen indexes with 99% fragmentation respond well to a REORG, sometimes falling back into single digit percentages. This is where number of pages becomes important. An index with a low number of data pages, say, below 100, will always contain some level of fragmentation after very little use. So, in this regard, it does not make sense to worry about indexes with very little data in them.

I have a script here that does all tables in a single database. This script is a compilation of 2 or three scripts, and some of my own handy work, but mainly from the one provided by Microsoft on index defragmentation. This script runs DBCC SHOWCONTIG and places the results in a temp table, runs the REORG using frag and page threshold parameters, then runs DBCC SHOWCONTIG again and compares the before and after fragmentation percentages so you can see how well it performed.

--DISCLAIMER - as with all code gleaned from the internets, test it thoroughly on non-production systems first. I am not responsible for any damage caused by use of this script.

Again, if there are any that disagree with my approach, feel free to speak up - I will listen to any and all responses. Until I see a reason not to use this method, I see no reason to cause a system outage over fragmented indexes.

Crap Code: Total Disclosure

Published 8/10/2011 by KDub in SQL

This post comes as part of a challenge from the community, namely Adam Machanic [Blog] who is hosting #21 this month, centered around the T-SQL Tuesday (#TSQL2SDAY) theme. The topic for T-SQL Tuesday #21 is Crap Code. Little snippets of code we use when we are in a hurry, don't have time to 'do it right', or just simply don't know how to do it and can't take the time to learn it. It is temporary, just make it work...for now. I will go back and fix it later.

As Adam states, "As professionals, we must strive to rid ourselves of bad habits. And the only way to learn the difference is to see lots, and lots, and lots of examples." By accepting this challenge, hundreds of SQL bloggers will be exposing their crap code via independent blog posts. Once all the posts are tallied, there will be plenty of examples for us to avoid, thereby making this theme complete. So now, join me as I offer up one of my own examples for your coding displeasure.

I am a DBA for a large manufacturing company that uses Dynamics AX as their ERP. We have several developers constantly working in either the code or on reporting. This one dev is constantly cutting corners because 'he just needs to get it done now, quick and dirty'.  He's a dot Net guy, and not terribly compelling with SQL. Well, more often than not, I catch some of this quick and dirty before it makes it into my databases. (Some of you may know that AX allows the user to do just about anything to the database right through the GUI, including adding columns and even entire tables, without the DBA's knowledge or assistance) Control is nearly impossible. My expertise is usually only called upon when rescue is needed (as in "cleanup on aisle 3!").

On this particular day he was running some T-SQL to repopulate a 'custom' column in a native table that had somehow gotten populated with some NULL values and was screwing up his report (yes, he does reporting and has access to DML). And yes, it is a derived column, and yes I know about normalization. It's control I have a problem with (see above). For some strange reason, the database was denying him access to run his UPDATE statement in this DB (LOL) and he asked me to run it for him. He sent me the T-SQL in email, and this is what I got:


--- updates recs with correct producttype
UPDATE salesbookhist
SET producttype = (
		SELECT producttype
		FROM inventtable
		WHERE salesbookhist.itemid = inventtable.itemid
			AND inventtable.dataareaid = '004'
		SELECT producttype
		FROM inventtable
		WHERE salesbookhist.itemid = inventtable.itemid
			AND inventtable.dataareaid = '004'


There are 1.82M rows of data in this table and he was trying to update one column. I asked him how long it ran on the DEV server and he replied, "about 3 hours". He did not believe me when I said it should take no more than a few minutes. I then proceeded to point out the flaw in his code. Again I was met with disbelief. I showed him some examples of simple JOIN clauses, but he remained adamant that his code was sound (I am not going to write it for him). I have seen similar code from him with nested SELECT statements going 5 and 6 levels deep. For those, I had mentioned using a CTE, just for readability (I hate trying to decipher someone else's crap code. The least they could do is make it readable). On this issue, however, he returned with a nice shiny CTE, but he said it still ran like a dog. Again, I suggested a simple JOIN.

An hour later he came back with a reasonably well composed statement that contained adequate JOIN syntax. It ran for 34 seconds.

I hope this post sheds some light on why we should never write (or allow in our databases) crap code.