You should explicitly name indexes also, so you can get a sense of the lineage of the key and avoid having the random numbers appended by SQL Server to your index and constraint names. An index name also should reflect whether the index is clustered or not, unique or not, and by including the referencing and referenced tables, whether it enforces a referential fact or not. As with triggers, indexes are dedicated, owned by a specific table, but because they are external structures, reflecting the owning table's name makes sense. You wind up with index names such as PK_cl_u_Customer_CustomerNumber (a clustered unique index on the primary key of Customers), FK_cl_CustomerCustomerNumber_OrderNumber (a compound clustered index on CustomerNumber, a foreign key into Orders and Order Number), or ncl_u_OrderDetailNumber_ItemItemNumber (a nonclustered unique index on an order detail and its item number). These would apply to the primary key for a table, a foreign key into a child table, or an indexed column in a single table.
You can make column names more memorable also by applying a naming convention. Some people prefer naming the primary key column ID in the base table, then prefixing the ID with its entity name once it migrates to a child as a foreign key. Once the Customer.ID migrates to Orders, it becomes Orders.CustomerID, giving the reader a clue as to the origin of the key.
I have seen systems that prefix column names with their data types on the assumption that knowing the column's data type makes stored procedure code writing easier. The reasoning is the data type of the column is carried over to the types of the variables used in the code. My own feeling is that the presence of the Object Browser makes this unnecessary, because you can find this information out just by looking at the Object Browser in Query Analyser.
A few other considerations exist for less commonly used database objects, such as functions, check clauses, defaults, and user-defined types. I use the same rules for naming these that I use for the more "visible" objects. If ownership is defaulted to some other object, I don't include it in the name and favor including a functional description instead.
Since functions are not owned by anything, I would name them fn_WhatEverItsSupposedToDo. Check clauses are owned, but they have scope in that they can apply to the entire table or to just a column. So including the scope in its name would be nice, as in ck_t_ColumnsInvolvedOneOfWhichIsNotNull or ck_c_HasToBeInARange.
Defaults are prefixed with def_ and user defined types with udt_. In practice, however, I avoid the user-defined types because they create more work than they are worth at maintenance time.
Finally, don't forget that some strings qualify as reserved words. These can have unintended effects on behavior because they cause errors that aren't immediately apparent and difficult to track down. The reserved words to avoid are listed in Books On Line.
What's in a Name? Almost Everything
To maintain understanding and readability, I take advantage of long names in my conventions, but I do downsize to either an alias name for an especially unwieldy column (using the AS keyword) or for a long view name in the From clause, (again, using the AS keyword) when I am actually using the long name in a query. To maintain readability, I use camel case in all my names to make them stand out and be more legible.
The thing to take away from this discussion of naming conventions is that adopting a naming convention helps you keep your thoughts together while you are working with your database. It really doesn't matter what rule or convention you use, as long as you use one consistently. Will you remember what you were talking about six months from now? Put yourself in your successors' shoes. Would they know what you were trying to convey? It's all in the name (well, mostly).