-- To search in MSSQL programmable objects (stored procedures, functions, views, triggers)
CREATE PROCEDURE SearchInAllDatabaseObjects
@SearchText NVARCHAR(255)
AS
BEGIN
SET NOCOUNT ON;
-- Search in programmable objects (stored procedures, functions, views, triggers)
SELECT
SCHEMA_NAME(o.schema_id) + '.' + o.name AS FullObjectName,
CASE o.type
WHEN 'P' THEN 'Stored Procedure'
WHEN 'FN' THEN 'Scalar Function'
WHEN 'IF' THEN 'Inline Table Function'
WHEN 'TF' THEN 'Table Function'
WHEN 'TR' THEN 'Trigger'
WHEN 'V' THEN 'View'
END AS ObjectType,
o.type AS TypeCode,
o.create_date AS CreatedDate,
o.modify_date AS ModifiedDate
FROM
sys.sql_modules m
INNER JOIN
sys.objects o ON m.object_id = o.object_id
WHERE
m.definition LIKE '%' + @SearchText + '%'
AND o.type IN ('P', 'FN', 'IF', 'TF', 'TR', 'V')
UNION ALL
-- Search in table/column names
SELECT
SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullObjectName,
CASE t.type
WHEN 'U' THEN 'Table (User)'
WHEN 'S' THEN 'Table (System)'
END AS ObjectType,
t.type AS TypeCode,
t.create_date AS CreatedDate,
t.modify_date AS ModifiedDate
FROM
sys.tables t
WHERE
t.name LIKE '%' + @SearchText + '%'
OR EXISTS (
SELECT 1
FROM sys.columns c
WHERE c.object_id = t.object_id
AND c.name LIKE '%' + @SearchText + '%'
)
ORDER BY
ObjectType, FullObjectName;
END
GO
