Viewing Database Metadata in SQL Server 2008 R2

You can view database, file, partition, and filegroup properties using a variety of catalog views, system functions, and system stored procedures.

The following table lists the catalog views, system functions, and system stored procedures that return information about databases, files, and filegroups.

Views	Functions	Stored procedures and other statements
sys.databases	DATABASE_PRINCIPAL_ID	sp_databases
sys.database_files	DATABASEPROPERTYEX	sp_helpdb
sys.data_spaces	DB_ID	sp_helpfile
sys.filegroups	DB_NAME	sp_helpfilegroup
sys.allocation_units	FILE_ID	sp_spaceused
sys.master_files	FILE_IDEX	DBCC SQLPERF
sys.partitions	FILE_NAME
sys.partition_functions	FILEGROUP_ID
sys.partition_parameters	FILEGROUP_NAME
sys.partition_range_values	FILEGROUPPROPERTY
sys.partition_schemes	FILEPROPERTY
sys.dm_db_partition_stats	fn_virtualfilestats
sys.dm_db_file_space_usage (Transact-SQL) (tempdb only)
sys.dm_db_session_space_usage (Transact-SQL)(tempdb only)
sys.dm_db_task_space_usage (Transact-SQL) (tempdb only)

Some columns in the sys.databases catalog view and properties in the DATABASEPROPERTYEX function may return a NULL value if the specified database is not available. For example, to return the collation name of a database, the database must be accessed. If the database is not online, or the AUTO_CLOSE option is set to ON, the collation name cannot be returned.