Some useful SQL statements

Here are a couple of SQL statements I seem to use on a regular basis.

1. Find all objects that contain specific text:
   

   SELECT DISTINCT b.name + '.' + a.[name]
   FROM sys.objects a
   INNER JOIN sys.schemas b ON a.schema_id = b.schema_id
   INNER JOIN syscomments c ON a.object_id = c.id
   WHERE c.[text] LIKE '%CPRS%' 
   -- AND a.[type] = 'P' -- stored procedure
   -- AND a.[type] = 'V' -- view 
   -- AND a.[type] = 'FN' -- function etc.
   

2. Find all foreign keys for a specific table column:
   

   SELECT 
       f.name AS ForeignKey,
       OBJECT_NAME(f.parent_object_id) AS TableName,
       COL_NAME(fc.parent_object_id,
       fc.parent_column_id) AS ColumnName,
       OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
       COL_NAME(fc.referenced_object_id,
       fc.referenced_column_id) AS ReferenceColumnName,
       COL_NAME(fc.parent_object_id, fc.parent_column_id)
   FROM 
       sys.foreign_keys AS f
       INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
   WHERE
    OBJECT_NAME (f.referenced_object_id) = '[TABLE_NAME]' 
   AND 
    COL_NAME(fc.parent_object_id, fc.parent_column_id) = '[COLUMN_NAME]'