Home » PlItems » Nach Inhalt in Functions/Stored Procedures suchen (T-SQL)

Nach Inhalt in Functions/Stored Procedures suchen

ID60
Link ReferenceNot set
Link CreditsNot set
Link Technical DocumentationNot set
PlCodelangT-SQL
PlGroupDatenbank
PlItemTitleNach Inhalt in Functions/Stored Procedures suchen
Code-- Durchsucht alle Functions, Stored Procedures, Views nach dem enthaltenen Begriff dbo.table1:
DECLARE @suchbegriff VarChar(50) = '%dbo.table1%'

SELECT
*
FROM
(
SELECT
ROUTINE_SCHEMA AS [Schema]
, ROUTINE_NAME AS [Objectname]
, ROUTINE_DEFINITION AS [Objectdefinition]
, ROUTINE_TYPE AS [Objecttype]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @suchbegriff
AND (ROUTINE_TYPE='PROCEDURE' OR ROUTINE_TYPE='FUNCTION')
UNION ALL
SELECT
TABLE_SCHEMA AS [Schema]
, TABLE_NAME AS [Objectname]
, VIEW_DEFINITION AS [Objectdefinition]
, 'VIEW' AS [Objecttype]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE @suchbegriff
) subQuery1
ORDER BY subQuery1.[Objectname]

-- gefunden bei: http://www.mssqltips.com/tip.asp?tip=1419
-- erweitert um View-Definition
Result Example