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

Earlier this week I wrote a blog about Find Column Used in Stored Procedure – Search Stored Procedure for Column Name. I received plenty of comments on the subject. One of the statements which I used in the story (Time: Any Day – usually right before developer wants to go home) was very much liked by many developers. I guess this is because we are all like the same. We often get more work, when we are ready to go home. After reading the blog post many readers and SQL Server Experts have posted an enhanced T-SQL script to find column used in a stored procedure.

SQL Server Expert Imran Mohammed is a very good friend of mine. He posted a very interesting note that function used in the original script is going to be deprecated in future releases so better to use following scripts.

1) Search in All Objects

This script search stored procedures, views, functions as well other objects.
-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'BusinessEntityID' + '%'
GO

2) Search in Stored Procedure

This script search only stored procedure for specified column.
-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'BusinessEntityID' + '%'
GO

Thanks Imran for suggesting this follow up script. Btw, if you want to read a short story, I suggest you head to original blog post.

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

About these ads

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 – sp_describe_first_result_set New System Stored Procedure in SQL Server 2012

I might have said this earlier many times but I will say it again – SQL Server never stops to amaze me. Here is the example of it sp_describe_first_result_set. I stumbled upon it when I was looking for something else on BOL. This new system stored procedure did attract me to experiment with it. This SP does exactly what its names suggests – describes the first result set. Let us see very simple example of the same. Please note that this will work on only SQL Server 2012.

EXEC sp_describe_first_result_set
N'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail', NULL, 1
GO

Here is the partial resultset.

Now let us take this simple example to next level and learn one more interesting detail about this function.

First I will be creating a view and then we will use the same procedure over the view.

USE AdventureWorks
GO
CREATE VIEW dbo.MyView
AS
SELECT
[SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

Now let us execute above stored procedure with various options. You can notice I am changing the very last parameter which I am passing to the stored procedure.This option is known as for browse_information_mode.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 0;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 1;
GO
EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView'
, NULL, 2;
GO

Here is result of all the three queries together in single image for easier understanding regarding their difference.

You can see that when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table. When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

I found it really really interesting that there exists system stored procedure which now describes the resultset of the output.

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

SQL SERVER – Bad Practice of Using Keywords as an Object Name – Avoid Using Keywords as an Object

Madhivanan is SQL Server MVP and very talented SQL expert. Here is one of the nugget he shared on Just Learned.

He shared a tip where there were two interesting point to learn.

  1. Do not use keywords as an object name
  2. [read DHall's excellent comment below]

He has given excellent example how GO can be executed as stored procedure. Here is the extension of the tip. Create a small table and now just hit EXEC GO; and you will notice that there is row in the table.

Create Stored Procedure
CREATE PROCEDURE GO
AS
SELECT
1 AS NUMBER

Create Table
CREATE TABLE T1 (ID INT)

Now execute following code
INSERT INTO T1(ID)
EXEC GO;

Now when selecting from table it will give us following result:
SELECT *
FROM T1

Now see following resultset:

So without inserting any data we inserted the data, well indeed good puzzle but very bad practical practice. Every body should be aware of this gotcha and avoid it. Thanks Madhivanan for teaching this interesting learning.

Republishing here with authorization of Just Learned.

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

SQL SERVER – Powershell – Get a List of Fixed Hard Drive and Free Space on Server

Earlier I have written this article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server. I recently received excellent comment by MVP Ravikanth. He demonstrated that how the same can be done using Powershell. It is very sweet and quick solution.

Here is the powershell script. Run the same in your powershell windows.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Well, I ran this script in my powershell window, it gave me following result – very accurately and easily.

Get-WmiObject -Class Win32_LogicalDisk | Select -Property DeviceID, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } | Format-Table -AutoSize

Thanks Ravikanth one more time for excellent tip.

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

SQL SERVER – Get Directory Structure using Extended Stored Procedure xp_dirtree

Many years ago I wrote article SQL SERVER – Get a List of Fixed Hard Drive and Free Space on Server where I demonstrated using undocumented Stored Procedure to find the drive letter in local system and available free space. I received question in email from reader asking if there any way he can list directory structure within the T-SQL. When I inquired more he suggested that he needs this because he wanted set up backup of the data in certain structure.

Well, there is one undocumented stored procedure exists which can do the same. However, please be vary to use any undocumented procedures.

xp_dirtree 'C:\Windows'

Execution of the above stored procedure will give following result. If you prefer you can insert the data in the temptable and use the same for further use.

Here is the quick script which will insert the data into the temptable and retrieve from the same.

CREATE TABLE #TempTable (Subdirectory VARCHAR(512), Depth INT);
INSERT INTO #TempTable (Subdirectory, Depth)
EXEC xp_dirtree 'C:\Windows'
SELECT Subdirectory, Depth
FROM #TempTable;
DROP TABLE #TempTable;

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

SQL SERVER – Question to You – When to use Function and When to use Stored Procedure

This week has been very interesting week. I have asked few questions to users and have received remarkable participation on the subject.

Q1) SQL SERVER – Puzzle – SELECT * vs SELECT COUNT(*)

Q2) SQL SERVER – Puzzle – Statistics are not Updated but are Created Once

Keeping the same spirit up, I am asking the third question over here.

Q3) When to use User Defined Function and when to use Stored Procedure in your development?

Personally, I believe that they are both different things ‑ they cannot be compared. I can say, it will be like comparing apples and oranges. Each has its own unique use. However, they can be used interchangeably at many times and in real life (i.e., production environment). I have personally seen both of these being used interchangeably many times. This is the precise reason for asking this question.

When do you use Function and when do you use Stored Procedure? What are Pros and Cons of each of them when used instead of each other?

If you are going to answer that ‘To avoid repeating code, you use Function’ ‑ please think harder! Stored procedure can do the same. In SQL Server Denali, even the stored procedure can return the result just like Function in SELECT statement; so if you are going to answer with ‘Function can be used in SELECT, whereas Stored Procedure cannot be used’ ‑ again think harder! (link).

Now, what do you say? I will post the answers of all the three questions with due credit next week.

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