Extracting database metadata—like size, collation, user permissions, and recovery models—is a frequent task for developers and database administrators. While navigating graphical user interfaces (GUIs) can be slow, using optimized scripts retrieves this information instantly.
Here is how to extract core database properties in seconds across SQL Server, PostgreSQL, and MySQL. Microsoft SQL Server
SQL Server provides the DATABASEPROPERTYEX function and the sys.databases catalog view to fetch properties immediately.
SELECT name AS DatabaseName, DATABASEPROPERTYEX(name, ‘Collation’) AS Collation, DATABASEPROPERTYEX(name, ‘RecoveryModel’) AS RecoveryModel, DATABASEPROPERTYEX(name, ‘Status’) AS Status FROM sys.databases; Use code with caution.
To find the exact file sizes and disk usage for the current database, use this script:
SELECT name AS FileName, size8 / 1024 AS SizeInMB, max_size AS MaxSize, growth AS GrowthSettings FROM sys.database_files; Use code with caution. PostgreSQL
PostgreSQL stores database properties within system catalogs like pg_database. Run this query to view encryption, collation, and access limits:
SELECT datname AS database_name, pg_encoding_to_char(encoding) AS encoding, datcollate AS collation, datconnlimit AS connection_limit FROM pg_database; Use code with caution.
To calculate the exact physical size of a specific database on your disk, use the built-in size formatting function:
SELECT pg_size_pretty(pg_database_size(‘your_database_name’)); Use code with caution.
MySQL organizes metadata within the information_schema. You can extract the default character set and collation for all databases with one query:
SELECT SCHEMA_NAME AS database_name, DEFAULT_CHARACTER_SET_NAME AS character_set, DEFAULT_COLLATION_NAME AS collation FROM information_schema.SCHEMATA; Use code with caution.
To see the combined data and index sizes for every database on your MySQL server, use this aggregation script:
SELECT table_schema AS database_name, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_in_mb FROM information_schema.tables GROUP BY table_schema; Use code with caution. Automation via Command Line
For rapid documentation, execute these queries directly from your terminal or command prompt to export properties to a file without opening a heavy GUI.
SQL Server (sqlcmd): sqlcmd -S server_name -Q “SELECT name FROM sys.databases” -o properties.txt
PostgreSQL (psql): psql -d database_name -c “\l+” > properties.txt
MySQL (mysql): mysql -u user -p -e “SHOW DATABASES;” > properties.txt If you would like to expand this article, let me know:
Which specific database platform you want to focus on (e.g., Oracle, MongoDB, Snowflake)
The target audience (e.g., beginner developers, senior DBAs)
If you need to include programming language examples like Python or Node.js
I can customize the depth and technical complexity to fit your exact needs.
Leave a Reply