Are there any system stored procs to report the stats and metadata of a database itself in SQL Server 2005?
What I need is a quick way to output a list of tables, the size of each table, the number of rows in each table and so on. Stored procs for individual tables and metadata would also be useful.
Advice appreciated.
Yes, the data dictionary tables will let you do this. The main tables in the data dictionary are sys.objects, sys.columns, sys.indexes, sys.foreign_keys and sys.sql_modules. For an example of a variety of queries that use the system data dictionary to reverse-engineer a database to an SQL script, take a look at this stackoverflow posting.
Getting space usage is a bit more convoluted to do from the data dictionarybut sp_spaceused will do it for a single table. You can wrap this with sp_msforeachtable to iterate over a set of tables and get the report for all of the tables.