Find out who modified an object in SQL Server

SELECT --T.*, 'I', I.*, 'E', E.*
E.trace_event_id, E.category_id, E.name,
T.[path], T.start_time, T.last_event_time, T.event_count,
I.NTUserName, I.NTDomainName, I.HostName, 
I.ClientProcessID, I.ApplicationName,
I.LoginName, I.SPID, I.ObjectID, I.ServerName,
I.ObjectName, I.DatabaseName, I.SessionLoginName,
I.TextData, I.BinaryData
FROM sys.traces T 
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I 
JOIN sys.trace_events E On I.eventclass = E.trace_event_id 
WHERE T.id = 1 
AND E.name = 'Object:Altered' -- or 'Object:Deleted'  or ...
--AND ObjectName like '%AnObjectName%' -- <- filter for a specific object
ORDER BY T.start_time desc

Get columns list of an index

SELECT i.[object_id] as TableID, object_name(i.[object_id]) as TableName, 
i.index_id as IndexID, i.[name] as IndexName, 
i.is_primary_key, i.type_desc as IndexTypeDesc,
ic.column_id, c.[name] as column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id
AND i.[object_id] = ic.[object_id]
JOIN sys.columns c ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
-- optional filters:
WHERE object_name(i.[object_id]) = 'TableName'
WHERE i.[name] = 'IndexName'
WHERE c.[name] = 'ColumnName'

tempdb files and usage

-- space usage information for each data file in tempdb

SELECT *, 

(user_object_reserved_page_count)*8 as usr_obj_kb,

(internal_object_reserved_page_count)*8 as internal_obj_kb,

(version_store_reserved_page_count)*8 as version_store_kb

FROM sys.dm_db_file_space_usage

GO

http://msdn.microsoft.com/en-us/library/ms174412.aspx

-- tempdb files

SELECT * FROM tempdb.sys.database_files

GO

Troubleshooting tempdb Space Usage:

http://www.madeira.co.il/troubleshooting-tempdb-space-usage/