Search for particular DB object type

use AdventureWorks2008; 

-- Search all stored procedures

select StoredProcedureName

=convert(sysname,SCHEMA_NAME(schema_id))+'.'+name

from sys.objects where type = 'P'

and PATINDEX ('%HumanResources%', OBJECT_DEFINITION (object_id)) > 0

go

             

Similarly: Replace the Type value with any of the below choices you wish to search

C  CHECK_CONSTRAINT
D  DEFAULT_CONSTRAINT
F  FOREIGN_KEY_CONSTRAINT
FN SQL_SCALAR_FUNCTION
IF SQL_INLINE_TABLE_VALUED_FUNCTION
IT INTERNAL_TABLE
P  SQL_STORED_PROCEDURE
PK PRIMARY_KEY_CONSTRAINT
S  SYSTEM_TABLE
SQ SERVICE_QUEUE
TF SQL_TABLE_VALUED_FUNCTION
TR SQL_TRIGGER
U  USER_TABLE
V  VIEW

List Indexes in DB:

SELECT

      SchemaName = schema_name(schema_id),

      TableName = object_name(o.object_id),

      IndexName = i.Name,

      IndexType = i.type_desc,

      IsUnique = case when is_unique=1 then 'UNIQUE' else '' end,

      IsPrimaryKey = case when is_primary_key=1 then 'PRIMARY KEY' else '' end,

      [FillFactor] = i.fill_factor

FROM sys.indexes i

INNER JOIN sys.objects o

ON i.object_id = o.object_id

WHERE i.Name is not null

and o.type = 'U'

ORDER BY IndexType, SchemaName, TableName, IndexName