Raul Gonzales has a script which provides useful information for tables and columns:
Useful information it provides at table level:
- tableType, to identify HEAP tables
- row_count, to identify tables with plenty of rows or now rows at all
- TotalSpaceMB, to identify big tables in size
- LastUserAccess, to identify tables that are not used
- TotalUserAccess, to identify tables that are heavily used
- TableTriggers, to identify tables that have triggers
Useful information it provides at column level:
DataType-Size, to identify supersized, incorrect or deprecated data types
Identity, to identify identity columns
Mandatory-DefaultValue, to identify NULL/NOT NULL columns or with default constraints
PrimaryKey, to identify primary key columns
Collation, to identify columns that might have different collation from the database
ForeignKey-ReferencedColumn, to identify foreign keys and the table.column they reference
Click through for the script.