Viewing Database Metadata

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 (tempdb only)
sys.dm_db_session_space_usage (tempdb only)
sys.dm_db_task_space_usage (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.

Examples

A. Using system catalog views to return database information

The following example use the catalog views sys.partitions, sys.allocation_units, sys.objects, and sys.indexes to return the partition numbers and allocation units used by each table and index in the database.

USE AdventureWorks;
GO
SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS table_name, 
    i.name AS index_name, 
    au.type_desc AS allocation_type, 
    au.data_pages AS pages_per_allocation_unit, 
    partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id 
        AND i.object_id = p.object_id
WHERE o.type_desc <> N'SYSTEM_TABLE'
ORDER BY table_name, p.index_id;

B. Using system catalog views to return database size information

The following examples use the catalog view sys.database_files and the dynamic management view sys.dm_db_file_space_usage to return size information for the tempdb database. The view sys.dm_db_file_space_usage is applicable only to tempdb.

SELECT 
name AS FileName, 
size*1.0/128 AS FileSizeinMB,
'MaximumSizeinMB' = 
    CASE max_size 
       WHEN 0 THEN 'No growth is allowed.'
       WHEN -1 THEN 'Autogrowth is on.'
       WHEN 268435456 
          THEN 'Log file will grow to a maximum size of 2 TB.'
       ELSE CAST (max_size*1.0/128 AS nvarchar(30))
    END,
growth AS 'GrowthValue',
'GrowthIncrement' = 
    CASE 
       WHEN growth = 0 THEN 'File size is fixed and will not grow.'
       WHEN growth > 0 AND is_percent_growth = 0 
          THEN 'Growth value is in units of 8-KB pages.'
       ELSE 'Growth value is a percentage.'
    END
FROM tempdb.sys.database_files;
GO
USE tempdb;
GO
SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS free_space_in_MB,
(SUM(version_store_reserved_page_count + 
    user_object_reserved_page_count +internal_object_reserved_page_count + 
    mixed_extent_page_count)*1.0/128) AS used_space_in_MB
FROM sys.dm_db_file_space_usage;

C. Using system functions

The following example uses the system function DATABASEPROPERTYEX to return the name of the default collation for the AdventureWorks database.

SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation');

See Also

Concepts

Setting Database Options
Querying the SQL Server System Catalog FAQ
Metadata Visibility Configuration

Other Resources

Implementing Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Added the "Examples" section. Added dynamic management views for tempdb. Updated content: Corrected example B.