First of all, if you need to list all the tables with data you can use the following script:
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnuasedSpaceKB
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND p.rows > 0 --As we want only tables with data
Warning: sometimes if a table is very large due to partitioning it could appear twice on the list! so you'd see both tables with the same name and only one schema... that's confusing, but it could happen.
When you look around the different tables, sometimes they are not all perfectly normalized! It's very frustrating, and in that case have a look in the triggers:
To List all triggers:
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]
INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid
INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE sysobjects.type = 'TR'
Sometimes you need to have a printout of an "on delete trigger" to find out all the tables that are related to a certain tables, when foreign key isn't always defined.
Ah, and if there's a certain and not quite common data type that you need to find out about. I'm not talking about INT here, maybe it's XML or Money or in the following case, Varbinary:
select IC.TABLE_NAME, IC.COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS IC where IC.DATA_TYPE = 'VARBINARY'
Ah, and BTW: SQL Server 2012 is FUN to work with, you're welcome to try out!