Interview Question of the Week #005 – List All the Tables with Name Containing Specific String

The other day, I received an email from a blog reader. When he appeared for an interview, he was asked to retrieve all the tables where the name of the table contained the words ‘tbls_’. This was a very interesting scenario. As the organization had earlier adopted a specific naming convention where they were writing the string ‘tbls_’ before any table name. However, as the new manager joined the organization, he decided to remove this static prefix and wanted the list of the all the tables where the name of the table contained ‘tbls_’ anywhere in the table. I think this is a very common question and we often see this particular requirement in the industry.

Question: How do you list all the tables in the database where the name of the table contains specific strings like ‘tbls_’ anywhere in the name of the table.

Answer: Here is a simple script which can list all the tables with specific string in the name of the table.

DECLARE @SearchString VARCHAR(256)
SET @SearchString = 'tbls_%' -- Change SearchString
SELECT name FROM sysobjects
AND crdate <= DATEADD(m,-1,GETDATE())
name LIKE '%'+@SearchString+'%'

Remember this script is using LIKE keyword with a % sign before and after searching string, hence it may be a bit slower in performance, but will for sure return accurate results. You can change the like condition based on your business need.

Here is the similar script which I had written a few years ago with the help of the cursor SQL SERVER – Simple Cursor to Select Tables in Database with Static Prefix and Date Created.

Reference: Pinal Dave (

SQL SERVER – 8 Performance Related Articles on Logical Reads

Earlier this year, I have written few articles which are related to SQL Server Performance and they are published on site of Logical Reads. Here are my personal favorite 8 various articles on the subject of SQL Server Performance. I have really enjoyed writing them and I am very confident that you will like that as well. Let me know which one is your favorite article by leaving note in the comment area.

How SQL Server DMVs Can Be a Savior in SQL Azure, Too

Be it SQL Server or the improved version of SQL Server on the cloud called as SQL Azure, when it comes to monitoring, there is nothing as powerful as DMVs (Dynamic Management Views). For a primer, Dynamic Management Views (DMVs) are a set of virtual views which expose the internal states of SQL Server deployment. […]

Troubleshooting High CPU issues in SQL Server (Part 2)

If you ever ask a SQL Server DBA about most frequently faced performance issue in SQL Server, they would most likely say blocking or high CPU. A smart DBA would first confirm that high CPU is caused by SQL Server by looking at performance monitor. Once it is confirmed who is consuming high amount of […]

SQL Server Memory Buffer Pools: Understand the Basics

How many times you have heard this question: “Why is SQL Server consuming so much memory?” If you ask a DBA who knows the functionality of SQL Server, the answer would be, “That is an expected behavior! Limit max server memory of SQL Server if you are concerned.” One might then wonder why SQL Server doesn’t release […]

SQL Server Performance Tip: In-Memory OLTP

SQL Server 2014 introduced the new database engine enhancement called In-Memory OLTP. This feature uses new data structures which are optimized for in-memory access of table. People normally call it In-Memory database. In reality, we would like to call it partially in-memory database because SQL Server allows us to have few hot tables in-memory and […]

Troubleshooting High CPU Issues in SQL Server (Part 1)

Performance troubleshooting is one of the unique challenges every developer and DBA has to face. One of the major complaints about performance troubleshooting is utilization of resources. Few organizations resort to putting muscle power to scale up the system without analyzing the root cause of the performance problem. For example, a typical scenario might involve a SQL Server instance with memory […]

Understanding Recompiling Statements with SQL Server

Recently, I heard a story that was quite compelling. A retired naval officer used to get to the top of a bridge and keep waving his hand everyday around 8 PM. The passer-by used to think the elderly man had gone nuts and, after awhile, ignored his actions. One fine day, a stranger saw this and was […]

Two Options for Query Optimization with SQL Server

Working with SQL Server is always a challenge. As developers try to troubleshoot SQL Server performance problems, the first step that take is to look at the queries. This is the most basic step and most important step for most developers. Developers love these challenges of optimization because they can get the maximum visible performance improvements in their […]

3 Reasons You Should Move to Cloud

Cloud is the next buzz word in the industry. If we were to quiz any CIO on what are some of the big bets they want to make in the next 6-12 months–using cloud will surely be one of them. Why has this new concept has caught on like forest fire? Why are organizations exploring […]

Reference: Pinal Dave (

SQL SERVER – FIX – Error: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device. Operating system error 3(The system cannot find the path specified.)

This blog is in continuation to using the default values. Earlier this week I wrote a blog which talked about the failure of Create Database Command.

While working on the reproduction of the issue, I changed the parameters and later realized that the backup database command was also failing. If you rely on the default values for folders that don’t exist, this will raise an error message as shown below. Here is the simple command which I have run:


The command was failing with below error.

Here is the text of the message shown in image:
Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘E:\InvalidPath\master.bak’. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

If you notice the error message is pretty clear. The backup is going to path “E:\InvalidPath” which is an invalid folder on my system. Important point to note here is that I have NOT given any file path in the command, I have just given file name master.bak.

The question is, from where the path is picked? If you have read my previous blog, it’s picked from “BackupDirectory” value in registry location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer

The highlighted portion might change based on SQL Version and Instance ID. For me it’s MSSQL12 because this is SQL Server 2014 instance. And my instance name is SQL2014:

SQL Version Folder Name
SQL Server 2008 MSSQL10
SQL Server 2008 R2 MSSQL10_50
SQL Server 2012 MSSQL11
SQL Server 2014 MSSQL12

For example, if you have SQL Server 2012 default instance, it would be MSSQL11.MSSQLServer.

Here is the registry value

We can change it via SQL Server Management Studio too. You can right click on server node and go to properties. Then choose “Database Settings” tab.

If you want to use T-SQL then here is the script.

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'BackupDirectory', REG_SZ, N'E:\InvalidPath'

NOTE: Once you have changed the value, SQL Service restart is needed so that SQL can pick-up changed values.

Now that we have seen a couple of examples where using the default values have got us into trouble. As readers, I would like to know if you have got into any errors because of using default values in your coding stints. Let me know via your comments.

Reference: Pinal Dave (

Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 3 of 3)

I am very fortunate that I know John Sonmez in person. He is one of those individuals who understands the young mind of developers and depth of software development industry. I am a regular follower of his books, videos and ideas. I have personally implemented many of his ideas in my personal life and I have seen dramatic improvement. Earlier this month, I requested him to share his views and suggestions with all of us on this blog. He kindly accepted the request and returned with three amazing blog posts and a plethora of giveaways.

Let us read the John Sonmez in his own words in this third and final part of a three part series.

This post is the last post in my three-part series of tips for landing that perfect job, from my new book “Soft Skills: The Software Developer’s Life Manual.”

If you missed the first two posts in the series, go here to start at the beginning. You can find part one of the series here and part two of the series here.

In this post, I am going to give you one of my most effective—yet most controversial—tips.

Tip #3: Hire a professional resume writer

Most technical resumes aren’t very good.

It’s not your fault. You aren’t a professional resume writer. You write code or work with SQL databases.

But, a resume is a very important tool in landing a good, high paying job, so you should be willing to spend a little bit of extra money to get your resume written professionally or to at least spend considerable time making it as effective as possible.

Yes, I know it can be expensive. But, having your resume professionally written is an excellent investment in your career. I know plenty of developers who have had their resume professionally written and then landed jobs paying 20%-50% higher than what they were making before.

Think about it this way: The CEO of your company doesn’t write software or mess with databases. Instead, he or she hires you to do that.


Because you are the expert. The CEO’s time is better spent running the business or… playing golf. Not writing code or tuning SQL queries.

Why then do you insist on doing the job of a resume writer?

Unless you have considerable skill in writing resumes, leave it to the professional.

I highly recommend hiring a professional resume writer to write you resume, but if you don’t feel comfortable doing that or can’t afford it, at least spend some time making your resume highly targeted for the job you are applying for.

If you custom tailor your resume to match the description of the job you are applying for, you can greatly increase your chances of getting an interview.

Sure, it takes a little extra time, but spending that extra time is almost always worth it.

Would you rather apply for 50 different jobs with one generic resume and perhaps get 1 or 2 interviews or apply for 10 jobs with specifically tailored resumes and get 6-8 invitations to interview?

Most job applicants will not tailor their resume to fit the job. If you do, you will stand out and be noticed.

Didn’t you say there was some kind of a contest?

Well, I’m glad you asked.

You’ve probably noticed that I mentioned that these tips came from my new book “Soft Skills: The Software Developer’s Life Manual.”

Pinal was kind enough to allow me to write this three part series in order to help promote the launch of this book.

Now, I know the book will be extremely valuable to you all by itself—it just launched at the beginning of this year and already has 114 five-star reviews on Amazon—but, I want to give you some extra value, so here is what I am going to do:

First, I’m going to give you a completely free, bonus chapter from the book.

All you have to do, to get the bonus chapter, is sign up here.

Then, I’m going to give one person who signs up to get the free bonus chapter How To Market Yourself as a Software Developer.

Of course, I’d like it if you bought the book, but you don’t even have to buy the book to win. All you have to do is sign up to get the bonus chapter and you’ll be automatically entered to win the grand prize.

But, wait, I can do more.

I’ll make the deal even better.

Because I want to see this book hit #1 in the software development books on Amazon (it’s currently at #3), if you do buy “Soft Skills” in the next 48 hours, I’ll give you a copy of my $97 course: “10 Steps to Learn Anything Quickly” 100% completely free. (Just email me at after you purchase the book.)

I’ll even do it on the honor system, since I trust you. You don’t even need to send me a receipt. If you told me you purchased the book, I’ll send you the course.

Yes, I know it’s a bit crazy to give away a $97 course, if you purchase a $28 book, but what can I say. I really want as many people as possible to get this book, because I truly believe it will be one of the best investments you make in your career.

In fact, I’ll go one more step further.

If you don’t like the book, I’ll buy it back from you.

Have you ever heard an author make that promise?

Thanks again to Pinal for helping me promote Soft Skills and for the opportunity to write here on SQLAuthority. I sincerely hope you found these tips useful.

Reference: Pinal Dave (

SQL SERVER – The Basics of the SSIS Script Task – Notes from the Field #065

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the basics of the SSIS script task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.

Learning how to configure and use the SQL Server Integration Services (SSIS) Script Task gives you a great starting point for learning how to use SSIS Control Flow tasks. You’ll find that SQL Server Control Flow tasks provide powerful development capabilities that may surprise you. Let there be no mistake, SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

This article will focus on a basic example of configuring the SSIS Script Task, shown in Figure 1. People often think the SSIS Script Task is just for generating log messages, but let’s look at some of its more powerful capabilities.

Figure 1: SSIS Script Task

The Script Task supports Microsoft Visual C# and Microsoft Visual Basic languages. You can choose which language you will use prior to clicking the Edit Script button. Once you’ve clicked the Edit Script button, you cannot change the Script Language property.

At the heart of the Script Task is the ability to read a variable value from the SSIS package into the script and then write a message out of the Script Task. Because the Script Task can interact with SSIS variables, you can use .Net code to manipulate and respond to variable values.

For the purpose of our demonstration, we will add code to the Script Task to create a message for SSIS logging.

Messaging is a key component of enterprise architecture. Messages are generated (published, raised, produced, etc.) by Senders and received (subscribed, consumed, requested, etc.) by Listeners. Messaging is a complex topic. Entire books are dedicated to the topic.

Add variables to the ReadOnlyVariables and ReadWriteVariables properties by clicking the ellipsis inside the value textbox for each property. When you click the ellipsis, a Select Variables dialog appears, as you see in Figure 2.

Figure 2: Adding Read Only Variables to the Script Task

SSIS variables use a two-part naming convention: <Namespace>::<VariableName>. I added the SSIS variables System::TaskName and System::PackageName. The selected variables are in the System namespace and contain the name of the task and package, respectively.

Click the Edit Script button to open the script editing window as shown in Figure 3:

Figure 3: The Script Task Script Editor

Add the following code beneath the instruction to Add your code here:

Dim sPackageName As String = _ Dts.Variables("System::PackageName").Value.ToString
Dim sMsg As String = "I am " & sPackageName
Dts.Events.Fireinformation(1001, sPackageName, sMsg, "", 0, True)

Listing 1: VB.Net Code

The first line of the code in Listing 1 creates a VB.Net variable named sPackageName and initializes the value of this variable, setting it to the value of the SSIS Variable named System::PackageName. This is one way to read SSIS Variable values into a Script Task’s script.

The second line of the code in Listing 1 creates a VB.Net variable named sMsg and initializes the value of this variable with the text, “I am” plus the value contained in the sPackageName VB.Net variable.

The third line of the code shown in Listing 1 calls the FireInformation method in the Dts.Events assembly. FireInformation accepts six arguments:

  1. InformationCode – an integer you can use to identify custom messages sent from the SSIS package.
  2. SubComponent – a string you can use to populate the source of the message.
  3. Description – a string containing the message you wish to send.
  4. HelpFile – a string containing the location of a Help file (should a help file exist).
  5. HelpContext – an integer pointing to the help context (should a help context exist).
  6. FireAgain – a Boolean value indicating whether the event should subsequently fire.

My VB code includes a reference to the FireAgain argument. Note: This MSDN post states: “Because firing of an event may be expensive, the run-time engine provides a mechanism for suppressing events that you are not interested in. Every event firing method has a FireAgain parameter. If the value of this variable is false, after the method returns, the caller will not fire this event again for the duration of the current execution.” I understand this to mean setting FireAgain to False would suppress future FireInformation events in my Script Task, but testing indicates this is not correct. This is important for you to know because you may opt to use the FireAgain argument to manage when you raise events. I do not use the FireAgain argument for this purpose. I typically manage the decision to raise events in conditional logic surrounding the Event-raising code.

A test execution of the package reveals an Information message recorded on the Progress / Execution Results tab as shown in Figure 4.

Figure 4: the Information Message on the Progress / Execution Results Tab

Information messages generated from Script Tasks will appear in SSIS logs and the SSIS Catalog. With this information, you can monitor the progress of SSIS execution while the package is running, or view the historical status of variable values or package messages after the package execution has completed.

The SSIS Script Task can accomplish much more than generating log messages. This brief introduction and basic example have demonstrated how to get started configuring and using the SSIS Script Task. As you can see, SSIS Script Tasks give you development capabilities you may not have been aware of.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (

Developer – 3 Tips Every SQL Expert Needs to Know to Land the Perfect Job (Part 2 of 3)

I am very fortunate that I know John Sonmez in person. He is one of those individuals who understands the young mind of developers and depth of software development industry. I am a regular follower of his books, videos and ideas. I have personally implemented many of his ideas in my personal life and I have seen dramatic improvement. Earlier this month, I requested him to share his views and suggestions with all of us on this blog. He kindly accepted the request and returned with three amazing blog posts and a plethora of giveaways.

Let us read the John Sonmez in his own words in this second part of a three part series. You can read the first part of this series over here.

Welcome back, this is the second post in my three-part series where I am bringing you three of my best tips, from my new book, “Soft Skills: The Software Developer’s Life Manual”, to help you land your dream job.

You can find part one of the series here.

Oh, and don’t forget the giveaway for How To Market Yourself as a Software Developer. I’ll be doing at the end of the next post. You won’t want to miss that, so bookmark this page and check back tomorrow.

Tip #2: Get a personal referral

Sure, getting a personal referral is easy enough if you already know someone at the company you are applying for, but what if you don’t?

It can still be done. You just might have to work a little harder at it.

Start with who you know that might know someone at the company you are applying for. Ask them to introduce you and offer to buy them a cup of coffee—or even a nice lunch.

But, do yourself a favor, don’t try and “sell them” or “network,” instead just have a friendly chat and try to get to know them a little better.

A little known secret to the interview process is that interviewers almost always hire people they like. Yes, technical skills are important, but often the deciding factor in who gets the offer and who gets the rejection letter has completely to do with who the interviewer liked more.

Now, the person who refers you for a job might not be the same person who interviews you, but if you want to get a good referral, the same principle applies.

What if you don’t know someone who knows someone at the company?

If you don’t have someone that can make the introduction for you, make it yourself.

Try and find a few employees at the company you are applying at and see if they have blogs or a twitter account. Comment on their blogs or start interacting with them on social media.

There is almost always a way to reach someone at a company that you want to work at.

Today, the internet makes it very easy for you to build relationships with people at companies before you even apply there.

If you are serious about landing your dream job and increasing your salary, you should never apply for a job without a personal referral.

It might take some time to build up a relationship, but getting that personal referral can make it so you are not only a lot more likely to get the job, but also more likely to get a higher offer.

I talk about how I used this technique to land a six-figure job, working from home in “Soft Skills”, so if you are interested in getting more detail on this technique, go check it out.

Reference: Pinal Dave (

SQL SERVER – How to Know Backup History of Current Database?

Some blog post and scripts are like ever green references. One such blog has been Get Database Backup History for a Single Database. A number of you have pinged me and for amendments to that script with various ideas. Since that post was written more than 4+ years now, I thought it would be worthwhile to change it to current trends of SQL Server 2014 additions. What are the changes made to the previous script? The script will answer few questions like:

  • What were the different backups taken on the current DB? Remove the condition mentioned below and it can be generic enough for all DBs on your server.
  • Which user was involved in taking the backup?
  • What is the type of backup we are talking about?
  • Recovery Model and Database compatibility level of the DB at the time the backup was taken. I remember using this for a user, who used to change the DB compatibility in their script before a backup. It was basically a copy-paste problem from an internet script. This data helped there.
  • Size of backup – Both compressed and uncompressed.
  • If the Backup was password protected.
  • Finally, when the backups were taken.

So let us look at the script next. Feel free to modify the same as you wish.

-- Recent backup history for the current DB
SELECT s.database_name 'Database',
s.recovery_model 'Recovery Model',
s.USER_NAME 'Backup by Username',
'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
END 'Backup Type',
CONVERT(VARCHAR(20), s.backup_finish_date, 13) 'Backup Completed',
CAST(mf.physical_device_name AS VARCHAR(100)) 'Physical device name',
DATEDIFF(minute, s.backup_start_date, s.backup_finish_date) 'Duration Min',
CAST(ROUND(s.backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Size in MB',
CAST(ROUND(s.compressed_backup_size * 1.0 / ( 1024 * 1024 ), 2) AS NUMERIC(10, 2)) 'Compressed Size in MB',
CASE WHEN LEFT(mf.physical_device_name, 1) = '{' THEN 'SQL VSS Writer'
WHEN LEFT(mf.physical_device_name, 3) LIKE '[A-Za-z]:\%' THEN 'SQL Backup'
WHEN LEFT(mf.physical_device_name, 2) LIKE '\\' THEN 'SQL Backup'
ELSE mf.physical_device_name
END 'Backup tool',
s.is_force_offline /* for WITH NORECOVERY option */
FROM   msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily mf ON s.media_set_id = mf.media_set_id
WHERE  s.database_name = DB_NAME() AND  -- remove this condition if you want all DBs
s.backup_finish_date > DATEADD(MONTH, -3, GETDATE()) -- Get data for past 3 months
ORDER BY s.backup_finish_date DESC;

A sample output would look like:

There can be more additional fields added to this script like: If encryption is enabled, Collation information, LSN information, if backup has checksum etc. Do let me know which additional information do you use in your environment to know your backups better.

Reference: Pinal Dave (