Skip to content

Instantly share code, notes, and snippets.

@jbnv
Created July 8, 2015 15:25
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jbnv/d16a5329f23ece122454 to your computer and use it in GitHub Desktop.
Save jbnv/d16a5329f23ece122454 to your computer and use it in GitHub Desktop.
Return all objects that contain a particular string. (SQL Server)
SELECT OBJECT_NAME(object_id) AS [Name],
CASE
WHEN OBJECTPROPERTY(object_id, 'IsProcedure') = 1 THEN 'procedure'
WHEN OBJECTPROPERTY(object_id, 'IsView') = 1 THEN 'view'
WHEN OBJECTPROPERTY(object_id, 'IsTable') = 1 THEN 'table'
WHEN OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 THEN 'function'
WHEN OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 THEN 'function'
WHEN OBJECTPROPERTY(object_id, 'IsTrigger') = 1 THEN 'trigger'
ELSE 'other'
END AS [Type]
FROM sys.sql_modules
WHERE [definition] LIKE '%STRING%'
ORDER BY 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment