I've been playing around with SQL 2008's Intellisense features and ran across a few of the "sys." ('sys dot') functions that are very useful.
Naturally, I cracked open one of the ambiguous SharePoint content database and started running some analysis queries about the database. I came up with a very high-level and then a more detailed level SQL script that you can use for some very quick database analysis. No this is not as robust as a tool that you can buy that does deep analysis, but it does give you a good high-level view of the database that you are analyzing.
Enjoy!
//begin script
--high-level database report
SELECT COUNT(*) AS 'Number of Tables'
FROM sys.tables
SELECT COUNT(*) AS 'Number of Triggers'
FROM sys.triggers
SELECT COUNT(*) AS 'Number of Views'
FROM sys.views
SELECT COUNT(*) AS 'Number of Sprocs'
FROM sys.procedures
SELECT COUNT(*) AS 'Number of Indexes'
FROM sys.indexes
SELECT COUNT(*) AS 'Number of Database files'
FROM sys.database_files
SELECT COUNT(*) AS 'Number of SQL Logins'
FROM sys.sql_logins
SELECT COUNT(*) AS 'Number of Assemblies'
FROM sys.assemblies
--Detailed Database Report
SELECT name as 'Table Name'
,create_date
,modify_date
FROM sys.tables
ORDER BY name
SELECT t.name AS 'Trigger Name', tbl.name as 'Associated Table'
FROM sys.triggers t
INNER JOIN sys.tables tbl
ON t.parent_id = tbl.object_id
ORDER BY t.name
SELECT name AS 'View Name'
FROM sys.views
ORDER BY name
SELECT name AS 'SProc Name', create_date, modify_date
FROM sys.procedures
ORDER BY name
SELECT tbl.name as 'Associated Table'
, i.name as 'Index Name'
, i.type_desc
, i.is_unique
, i.is_primary_key
, i.is_unique_constraint
FROM sys.indexes i
INNER JOIN sys.tables tbl
ON i.object_id = tbl.object_id
ORDER BY tbl.name, i.name
SELECT name AS 'Database File Name'
,type_desc
,physical_name
,state_desc
,size
,growth
,is_read_only
,is_sparse
,is_percent_growth
FROM sys.database_files
ORDER BY name
SELECT name AS 'SQL Login Name'
,[sid]
,type_desc
,default_database_name
,default_language_name
FROM sys.sql_logins
ORDER BY name
SELECT name AS 'Assembly Name'
,clr_name,permission_set_desc
,create_date
,modify_date
FROM sys.assemblies
ORDER BY name
//end script