How to find column name within stored procedures in sql server

Posted By  dotnetfish On 27 Oct 2010 08:10:24
emailbookmarkadd commentsprint
No of Views:4792
Bookmarked:0 times
Votes:0 times

Introduction

Sometime it's very useful if we can search word in Stored Procedure for debugging purposes.Last week, i need to find a column name within stored procedures, then i do the goggling, but i could not find any useful information to find.so i try to compose a simple query to find the script. scripts is simple and small but result is effective and most useful script for developers and database administrators.

Sql script

Use below script to search with stored procedure using table called "tblLookup"

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tblLookup%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC

Now i would like to make standard stored procedure to seach any column names in stored procedures.here is that for you.

create proc spFindWordsInSP
@word nvarchar(100)
as
BEGIN

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@word+'%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC
END

Now i have run created procedures to find the userID columns in all my procedures,

exec spFindWordsInSP 'userID'

Output

 

Image Loading

In above result, we can get the name of procedure and script of composed procedure.i hope this is help and save lots times.thank you for reading.

Sign Up to vote for this article
Other popular Tips/Tricks
    In this tips, I will explain how to ALTER or ADD two or more columns in to table by t-sql script.
    Published Date : 25/Mar/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    A very important feature of SQL Server 2008 is that we can enable CDC(Change Data capture) on database or table.We can track the database had CDC enabled by querying IS_CDC_ENABLED column
    Published Date : 17/Jan/2011
    This is the third tips date related function in sql server. In this I going to show you how to use DATEDIFF() function in sql server. The DATEDIFF () is useful to get the specified date part between two dates
    Published Date : 16/Dec/2010
    The DATEADD function is a powerful built-in function sql server to add dates in different way and types. So I would like to explore in this tip how we can use the DATEADD function for different purpose.
    Published Date : 12/Dec/2010
Comments
By:woodbaseDate Of Posted:8/21/2012 1:16:55 AM
Nice!
Nice one - so small and simple and yet soooo(!) useful!
By:DavidDate Of Posted:3/24/2011 2:53:17 PM
Useful
Thank you for you SQL Script. Quick, easy and really useful
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top