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.
Take a look at the system views, particularly information_schema.tables. These procs will also get a lot of the data you are looking for.
sp_helpdb dbname
sp_help objectname
sp_spaceused tablename
sp_help tablename will give you detailed information down to the column definition level.
SQL Server 2005 has a number of very useful built-in standard reports (right-click on DB, -> Reports). "Disk usage by Top tables" is a useful one and contains the info that you are looking for.
Instead of querying the sysobjects etc... tables directly, you can use the INFORMATION_SCHEMA view.
In fact, the sysobjects was a table in SQL SErver2000, but in SQL 2005, it is implemented as a view, and it is kept to preserve backward compatability.