[SQL Server] Search Text in Database Objects

We can easily search the data that we store in our database tables through queries, but what if we want to search the database objects, like stored procedure, trigger, views, user-defined functions, etc. For instance, if we want to know if any specific table is used in any database object, or want to search any special comment mentioned in any of these DB objects. Here is what we do to get it done:

SELECT O.[name] AS [ObjectName]
, O.[type_desc] AS [ObjectType]
, O.[modify_date] AS [ModifiedDate]
, C.text
FROM sys.syscomments C
INNER JOIN sys.objects O ON C.[id] = O.[object_id]
WHERE C.[text] LIKE '%<text-to-search>%'
AND O.[type] in ('V', 'FN', 'TR', 'P')

Solution is just like Aladin's Lamp...! Enjoy.

[SQL Server] Search Table using Column Name

In high pressure professional environment, we sometime require to know which tables have some xyz column, and its quite tedious to search entire database. Here is the magic-wand query for this problem:

SELECT * FROM sys.tables WHERE object_id IN
(SELECT object_id FROM sys.columns WHERE name LIKE '%<column-name>%')

Amazing... isn't it! :)