Investigating undocumented database - part 2

I've written in the past about how to investigate an undocumented database, and here are a few more insights to my previous post.
First of all, if you need to list all the tables with data you can use the following script:


SELECT
    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
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    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
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name;

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:

SELECT
     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]
FROM sysobjects

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!