by ssi
8. November 2013 07:41
/*
Name: p_Find_DbObjects
Parameters:
@string - String/Part of string for DB Object you want to search.
Description: This procedure will allow you to find any string in any DB Object with its name or part of its name.
It will also allow you to find the column name belongs to which tables, TV functions and Views.
*/
CREATE PROCEDURE [dbo].[sp_ssiFind_DbObjects]
@string NVARCHAR(MAX) = ''
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
-- Returns list of DB objects containing the string in their definition and/or in their name.
SELECT [DB_ObjName] = OBJECT_SCHEMA_NAME(sm.[object_id]) + '.' + OBJECT_NAME(sm.[object_id]), so.type_desc [DB_ObjType],
(LEN(sm.[definition]) - LEN(REPLACE(sm.[definition], CHAR(10), ''))) Lines_of_Code
FROM SYS.SQL_MODULES sm
INNER JOIN SYS.OBJECTS so
ON sm.[OBJECT_ID] = so.[OBJECT_ID]
WHERE sm.[definition] LIKE N'%' + @string + '%'
OR so.[name] like N'%' + @string + '%'
GROUP BY sm.[object_id], so.type_desc,sm.[definition]
ORDER BY [DB_ObjName], [DB_ObjType];
SET NOCOUNT OFF
SET ANSI_WARNINGS ON
END
--exec [sp_ssiFind_DbObjects] 'vu_ssi_metrics'
5467bcd7-257c-469c-87d3-2b0feca0b943|0|.0|96d5b379-7e1d-4dac-a6ba-1e50db561b04
Tags: sql
SQL