In this article, you will learn how to find columns or text in Store procedures, Functions, Views, and Triggers.
Suppose there are multiple numbers of Store procedures, Functions, Views, and Triggers that contain a particular column or specific text and you have to find it. Here are the solutions for it.
Now let’s get those records using SQL queries.
The below code will give you all the Store procedures, Functions, Views, and Triggers that contain the column which you mention.
FIND COLUMN NAME
SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%' + 'Your Column Name' + '%'
SELECT DISTINCT OBJECT_NAME(OBJECT_ID), OBJECT_DEFINITION(OBJECT_ID) FROM sys.Procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'Your Column Name' + '%'
The below code will give you all the Store procedures, Functions, Views, and Triggers that contain specific Text which you mention in the place of @FindString.
FIND STRING IN ALL PROCEDURES
SELECT OBJECT_NAME(OBJECT_ID) SP_Name, OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'FindString' + '%'
FIND STRING IN ALL VIEWS
SELECT OBJECT_NAME(OBJECT_ID) View_Name, OBJECT_DEFINITION(OBJECT_ID) View_Definition FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%' + 'FindString' + '%'
FIND STRING IN ALL FUNCTION
SELECT ROUTINE_NAME Function_Name, ROUTINE_DEFINITION Function_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%' + 'FindString' + '%' AND ROUTINE_TYPE = 'FUNCTION' ORDER BY ROUTINE_NAME
FIND STRING IN ALL TABLES OF DATABASE.
SELECT t.name AS Table_Name, c.name AS COLUMN_NAME FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%' + 'FindString' + '%' ORDER BY Table_Name
if you want to know about shortcut keys in SQL then please refer to this Shortcut Keys For SQL Server
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular
In this article, we have to show Create and Used PIPE in angular
View Comments
Exactly what I was looking for. Works great. Thank you!
It's my pleasure.