SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

Place: Any Developer Shop

Scenario: A developer wants to drop a column from a table

Time: Any Day – usually right before developer wants to go home

The developer rushes to the manager and following conversation begins:

Developer: I want to drop  a column from one of the tables.

Manager: Sure, just document it where all the places it is used in our application and come back to me.

Developer: We only use stored procedures.

Manager: Sure, then documented how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.

Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. Here quickly opened it and run the code.

SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'

Above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to manager to office to inform his promptness and realized that the manager had left for the day just few moments before.

Moral of the story: Work life balanced can be maintained if we work smart!


Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012 database we want to find the BusinessEntityID column in all the stored procedure. We can use run following T-SQL code in SSMS Query Editor and find the name of all the stored procedure.

USE AdventureWorks2012
GO
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%'
AND TYPE = 'P'
GO

Above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.

While we are discussing this subject here are a couple of other additional related blog post which may interesting.

A question to you: Is there any better way to find column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Example of Width Sensitive and Width Insensitive Collation

I had a great time writing blog post SQL SERVER – Effect of Case Sensitive Collation on Resultset. It was interesting to see lots of questions related to collation based on this blog post. However, one of the question, I find very interesting and though to share today here.

Question: What is a width sensitive collation? Can you explain it with an example?

I indeed found this question interesting as I see very little awareness of the subject of collation.  I have talked with many and seen very little awareness on width sensitive collation.

Let me explain the same with a very simple example.

Width Sensitive Collation: A single-byte character (half-width) represented as single-byte and the same character represented as a double-byte character (full-width) are when compared are not equal the collation is width sensitive.

If above definition is not clear, let us try to understand by example. We will use the most famous equation in the world E=mc². I am a big fan of Albert Einstein and there is no better example than his famous example. In 1921, Einstein was awarded the Nobel Prize in Physics for his explanation of the photoelectric effect, however many still believes that he was awarded Nobel Prize because of relativity theory (which is incorrect).

Well, let us try to create a small example. In this example we have one table with two columns. One column has a collation of width sensitive and the second column has a collation of width insensitive. Now we will insert exactly the same data in both the column and try to retrieve the data from the column using the LIKE statement. However, instead of using E=mc² we will use E=mc2 in where condition and see how Width Sensitive and Width Insensitive Collation behaves.

USE TempDB
GO
CREATE TABLE ColTable
(ID1 NVARCHAR(100) COLLATE Latin1_General_CI_AI,
ID2WS NVARCHAR(100) COLLATE Latin1_General_CI_AI_WS)
GO
INSERT INTO ColTable (ID1, ID2WS)
VALUES ('E=mc² Albert Einstein',
'E=mc² Albert Einstein')
GO
SELECT *
FROM ColTable
WHERE ID1 LIKE 'E=mc2%'
GO
SELECT *
FROM ColTable
WHERE ID2WS LIKE 'E=mc2%'
GO
DROP TABLE ColTable
GO

Now let us check results.

Results are very interesting. You can see that when there is width insensitive collation E=mc² and E=mc2 are evaluated as equal and the query return result. In case of width sensitive collation comparison E=mc² and E=mc2 are evaluated as false returning no results. I hope this simple example clears the difference between Width Sensitive and Width Insensitive Collation.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Switch Between Two Parenthesis using Shortcut CTRL+]

Earlier this week I wrote a blog post about SQL SERVER – CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis, I received quite a lot of positive feedback from readers. If you are a regular reader of the blog post, you must be aware that I appreciate the learning shared by readers.

Here is another interesting shortcut shared by another SQL Server Expert – Suvendu. He has suggested that using shortcut CTRL+] one can jump between two parenthesis in the code. This is indeed interesting. You can try it out using the following script.

SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM Sales.Currency)a)b)c

Now, move your mouse right behind b (which is highlighted).

SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM Sales.Currency)a)b)c

Now press CTRL+] and you will see that it jumps from b to the opening parenthesis.

SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM Sales.Currency)a)b)c

You can once again press CTRL+] and it will jump back to parenthesis after b.

SELECT *
FROM (SELECT *
FROM (SELECT *
FROM (SELECT *
FROM Sales.Currency)a)b)c

Small but very useful tip. Try it out and let me know if there is any other important shorcut I should highlight on the blog.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Effect of Case Sensitive Collation on Resultset

Collation is a very interesting concept but I quite often see it is heavily neglected. I have seen developer and DBA looking for a workaround to fix collation error rather than understanding if the side effect of the workaround. Collation is a very deep subject. Earlier I wrote an article how one can resolve the collation error when different collation values are compared. Today in most simple way I would like to explain that different collation can return different result. Without understanding business needs (and sensitivity) one should not change the collation of the columns or database.

Let us see a simple example. I am going to create a table with two columns. Both the columns have different collation. One collation is case sensitive (CS) and another one is case insensitive (CI). You can see that col1 and col2 both have exactly the same data.

CREATE TABLE ColTable
(Col1 VARCHAR(15) COLLATE Latin1_General_CI_AS,
Col2 VARCHAR(14) COLLATE Latin1_General_CS_AS) ;
INSERT ColTable(Col1, Col2)
VALUES ('Apple','Apple'),
(
'apple','apple'),
(
'pineapple','pineapple'),
(
'Pineapple','Pineapple');
GO

-- Retrieve Data
SELECT *
FROM ColTable
GO

Now let us run two queries and compared its result set. In the first query col1 is used in order by clause and in second query col2 is used in the order by clause.

-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col1
GO
-- Retrieve Data
SELECT *
FROM ColTable
ORDER BY Col2
GO

Technically both the columns have exactly the same data. When either of the columns used in order by it should give exactly the same result. However, in our case it is returning us different result. The reason is simple – collation of the column is different. As mentioned earlier one of the column has a case sensitive collation and another column has a case insensitive collation. When table is ordered by Col2 which is case sensitive leading to lowercase ‘apple’ row before upper case ‘apple’ row.

Let us clean up.
-- Clean up
DROP TABLE ColTable
GO

As mentioned collation is a very important concept. It should be properly understood and explored before taking it granted or easy.

Checkout following video on the subject:

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Remove Debug Button in SSMS – SQL in Sixty Seconds #020 – Video

SQL in Sixty Seconds is indeed tremendous fun to do. Every week, we try to come up with some new learning which we can share in Sixty Seconds. In this busy world, we all have sixty seconds to learn something new – no matter how much busy we are. In this episode of the series, we talk about another interesting feature of SQL Server Management Studio. In SQL Server Management Studio (SSMS) we have two button side by side. 1) Execute (!) and 2) Debug (>). It is quite confusing to a few developers. The debug button which looks like a play button encourages developers to click on the same thinking it will execute the code. Also developer with a Visual Studio background often click it because of their habit. However, Debug button is not the same as Execute button. In most of the cases developers want to click on Execute to run the query but by mistake they click on Debug and it wastes their valuable time.

It is very easy to fix this. If developers are not frequently using a debug feature in SQL Server they should hide it from the toolbar itself. This will reduce the chances to incorrectly click on the debug button greatly as well save lots of time for developer as invoking debug processes and turning it off takes a few extra moments.

In this Sixty second video we will discuss how one can hide the debug button and avoid confusion regarding execution button. I personally use function key F5 to execute the T-SQL code so I do not face this problem that often.

More on Removing Debug Button in SSMS:

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

If we like your idea we promise to share with you educational material.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority Guest Post – Lessons from Life and Work by Srini Chandra (Author of 3 Lives, in search of bliss)

Work and life are confusing terms together. How can one consider work outside of life. Work should be part of life or are we considering ourselves dead when we are at work. I have often seen developers and DBA complaining and confused about their job, work and life. Complaining is easy and everyone can do. I have heard quite often expression – “I do not have any other option.” I requested Srini Chandra (renowned author of Amazon Best Seller 3 Lives, in search of bliss (Amazon | Flipkart) to write a guest post on this subject which developer can read and appreciate. Let us see Srini’s thoughts in his own words.


Each of us who works in the technology industry carries an especially heavy burden nowadays. For, fate has placed in our hands an awesome power to shape our society and its consciousness. For that reason, we must pay more and more attention to issues of professionalism, social responsibility and ethics. Equally importantly, the responsibility lies in our hands to ensure that we view our work and career as an opportunity to enlighten and lift ourselves up.

Story: A Prisoner, 20 years and a Wheel

Many years ago, I heard this story from a professor when I was a student at Carnegie Mellon. A man was sentenced to 20 years in prison. During his time in prison, he was asked to turn a wheel every day. So, every day he turned the wheel. At times, when he was tired or puzzled and stopped turning the wheel, he would be flogged with a whip. The man did not know anything about the wheel other than that it was placed outside his jail somewhere. He wondered if the wheel crushed corn or if it ground wheat or something similar. He wondered if turning the wheel was useful to anyone. At the end of his jail term, he rushed out to see what the wheel was doing. To his disappointment, he found that the wheel was not connected to anything. All these years, he had been toiling for nothing. He gave a loud, frustrated shout and dropped dead.

How many of us are turning wheels wondering what it is connected to? How many of us have unstated, uncaring attitudes towards our careers? How many of us view work as drudgery, as no more than a way to earn that next paycheck? How many of us have wondered about the spiritually uplifting aspect of work?

Can a workforce that views work as merely a chore, be ethical? Can it produce truly life enhancing technology? Can it make positive contributions to the quality of life of a society? I think not.

Thanks to Pinal and you, his readers, for giving me this opportunity to share my thoughts in a series of guest posts. I’d like to present a few ways over the next few weeks, in which we can tap into the liberating potential of work and make our lives better in the process.

Now, please allow me to tell you another version of the story that the good professor shared with us in the classroom that day.

Story: A Prisoner, 20 years, a Wheel and the LIFE

A man was sentenced to 20 years in prison. During his time in prison, he was asked to turn a wheel every day. So, every day he turned the wheel. At first, his whole body and mind rebelled against his predicament. So, his limbs grew weary and his mind became numb and confused. And then, his self-awareness began to grow. He began to wonder how he came to be in the prison in the first place. He looked around and saw all his fellow prisoners also turning the wheel. His wife, his parents, his friends and his children – they were all in the prison too, and turning their own wheels! He began to wonder how this came about. As he wondered more and more, he began to focus less on his physical drudgery and boredom. And he began to clearly see his inner spirit which guided him in ways that allowed him to see the world with a universal view. His inner spirit guided him towards the source of eternal wisdom and happiness. He began to see the source of happiness in everything around him – his prison bound relationships, even his jailers and in his wheel. He became a source of light to those around him. His wheel jokes and humor infected them with joy and happiness. Finally, the day came for his release from jail. He walked calmly outside the jail and laughed aloud when he saw that the wheel was not connected to anything. He knelt down, kissed it and thanked it for the wisdom it taught him.

Life is the prison. The wheel is your work. Both are sacred. Both have enormous powers to teach us wisdom and bring us happiness. Whether we allow them to do so, is a choice we have to make.

Over the next few weeks, I hope to share with you a few lessons that I have learnt at the wheel in my two decades of my career (prison). Thank you for reading, and do let me know what you think.

Reference: Srini Chandra (3 Lives, in search of bliss)Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Monday Morning Puzzle – Query Returns Results Sometimes but Not Always

The amount of email I receive sometime it is impossible for me to answer every email. Nonetheless I try to answer pretty much every email I receive. However, quite often I receive such questions in email that I have no answer to them because either emails are not complete or they are out of my domain expertise. In recent times I received one email which had only one or two lines but indeed attracted my attention to it. The question was bit vague but it indeed made me think. The answer was not straightforward so I had to keep on writing the answer as I remember it. However, after writing the answer I do not feel satisfied. Let me put this question in front of you and see if we all can come up with a comprehensive answer.

Question: I am beginner with SQL Server. I have one query, it sometime returns a result and sometime it does not return me the result. Where should I start looking for a solution and what kind of information I should send to you so you can help me with solving. I have no clue, please guide me.

Well, if you read the question, it is indeed incomplete and it does not contain much of the information at all. I decided to help him and here is the answer, which I started to compose.

Answer: As there are not much information in the original question, I am not confident what will solve your problem. However, here are the few things which you can try to look at and see if that solves your problem.

  • Check parameter which is passed to the query. Is the parameter changing at various executions?
  • Check connection string – is there some kind of logic around it?
  • Do you have a non-deterministic component in your query logic? (In other words – does your result is based on current date time or any other time based function?)
  • Are you facing time out while running your query?
  • Is there any error in error log?
  • What is the business logic in your query?
  • Do you have all the valid permissions to all the objects used in the query? Are permissions changing or query accessing a different object in various executions?
  • (Add your suggestions here)

Meanwhile, have you ever faced this situation? If yes, do share your experience in the comment area. I will send a copy of my book SQL Server Interview Questions and Answers to one of the most interesting comment. The winner will be announced by next Monday. 

Reference: Pinal Dave (http://blog.sqlauthority.com)