When there is a requirement to search a column reference or text reference inside a SQL Server database, we can use below SQL.
Search in Stored Procedure Only
Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID) As [Name],
object_definition(OBJECT_ID) As Content
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'text to be searched' + '%'
order by [Name]
GO
Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID) As [Name],
definition As Content
FROM sys.sql_modules
WHERE definition LIKE '%' + 'text to be searched' + '%'
order by [Name]
GO
Search in Tables Only
SELECT c.name AS 'ColumnName' ,t.name AS 'TableName'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%' + 'text to be searched' + '%'
ORDER BY TableName ,ColumnName;
Search in Tables and Views
SELECT COLUMN_NAME AS 'ColumnName' ,TABLE_NAME AS 'TableName'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%' + 'text to be searched' + '%'
ORDER BY TableName, ColumnName;
redgate SQL Search
redgate SQL Search is a free add-on we can install on SQL Server or Visual Studio.
Download redgate SQL Search
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%' + 'text to be searched' + '%'
ORDER BY TableName, ColumnName;
redgate SQL Search
redgate SQL Search is a free add-on we can install on SQL Server or Visual Studio.
Download redgate SQL Search
Add a comment