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.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.
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