SQL Server Metadata Succinctly®
CHAPTER 4
Database Properties
Within a SQL Server instance, there can be any number of databases containing related tables and objects. In this chapter, we will explore how to use the SQL views to determine information about the individual databases. Figure 3 shows the database properties table from SSMS.
Figure 3: Database properties
Note: Your account may not have permission to access the various views described in this chapter. You will generally need the VIEW DATABASE STATE permission for most of these queries.
Sys.databases
The primary view for database information is the sys.databases view. This view holds a row of information for each database in the server. You can restrict it to just the current database by filtering to the current database ID, as shown in the following code.
SELECT * from sys.databases
WHERE database_id=db_id()
The function db_id() returns the numeric ID of the current database (or takes a parameter of database name and returns the database ID).
DATABASEPROPERTYEX()
The SQL function DATABASEPROPERTYEX () provides additional information about any database in the system. It takes two parameters: the database name (using db_name() for current database) and the property you want to view.
General information
The General tab of the Properties page provides some simple status information, such as database size and date of the last backup. This information can be assembled using the sys.databases view and the DATABASEPROPERTYEX() function.
Basic information
Code Listing 18 uses the sys.databases view and DATABASEPROPERTYEX() function to duplicate much of the information from the General tab. In the following query, we are filtering to just the current database on the WHERE clause. If you remove the WHERE clause, you can obtain the general information for all databases to which you have access.
Code Listing 18: Database information
--------------------------------------------- -- Script: DatabaseInformation.sql -- Some basic database information --------------------------------------------- SELECT db.name AS databaseName, DATABASEPROPERTYEX(db.name, 'Status') AS 'Status', su.Name AS 'Owner', db.create_date AS 'Date Created', CONVERT(VARCHAR,mf.size*8/1024)+' MB' AS [Total disk space] FROM sys.databases db JOIN (select database_id, sum(size) AS Size from sys.master_files group by database_id) mf ON db.database_id=mf.database_id LEFT join sys.sql_logins su ON su.sid=db.owner_sid WHERE db.database_Id=db_Id() |
Table 22 shows the sample output.
Table 22: Sample database information
databaseName | Status | Owner | Date created | Total disk space |
|---|---|---|---|---|
JDB Demo | ONLINE | sa | 2019-07-31 21:27 | 144 MB |
Backup information
The backup information is retrieved from the backupset table in the MSDB (Microsoft Database) database. We can retrieve the backup information for both the data and the logs using Code Listing 19.
Code Listing 19: Database backup information
--------------------------------------------- -- Script: BackupInfo.Sql -- Backup information for current database --------------------------------------------- SELECT case type when 'D' then 'Database' when 'L' then 'Logs' end as BackupType, max(backup_finish_date) as LastBackup FROM msdb.dbo.backupset WHERE database_name=db_name() GROUP BY database_name,type |
There is other information available in the backupset table, such as the backup size, whether it is encrypted or not, recovery model, compressed size, etc. Although we are only interested in the backup dates, you might find occasional need to access the other fields.
Size information
The size of any database can be retrieved easily from the sys.master_files view, but the actual use (needed to compute space available) requires a bit more effort. Code Listing 20 provides this information.
Code Listing 20: Size information for database
--------------------------------------------- -- Script: DBSizeInfo.Sql -- Size information for the current database --------------------------------------------- SELECT db_name() as database_name, ltrim(str((CASE WHEN sf.dbsize >= pt.reservedpages THEN (convert(DECIMAL(15,2),sf.dbsize) - convert(DECIMAL(15,2),pt.reservedpages)) * 8192 / 1048576 ELSE 0 END),15,2) + ' MB') as 'Space Available' FROM ( SELECT sum(convert(BIGINT, CASE WHEN sf.STATUS & 64 = 0 THEN size ELSE 0 END)) as dbSize FROM dbo.sysfiles sf ) AS sf, ( SELECT reservedpages = sum(a.total_pages) FROM sys.partitions p INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id ) AS pt |
The sp_spaceused stored procedure returns this information as well, but it currently returns two result sets, so you cannot execute it to a temporary table.
Note: SQL 2016 added a new parameter, @oneResultSet, which allows sp_spaceused to return a single result if set to 1.
Files
The Files tab on the database properties shows basic information about the files and growth on the database.
Figure 4: Files information
The information about the database files and growth is available from the sysFiles view. Code Listing 21 converts the data to mimic the Files tab available from the SSMS Properties tab.
Code Listing 21: File information for current database
--------------------------------------------- -- Script: DBFileInfo.Sql -- File information for current database --------------------------------------------- SELECT sf.[name] AS Logical_Name, CASE WHEN Status = 2 THEN 'ROWS Data' WHEN Status = 66 THEN 'LOG' END AS FileType, IsNull(fg.type_desc,'Not Applicable') AS FileGroup, size*8.0/1024 as 'Current Size (MB)', growth*8.0/1024 as 'Autogrowth (MB)', CASE WHEN maxsize = 0 THEN 'No growth' WHEN maxsize < 0 THEN 'Unlimited' WHEN maxsize*8.0/1024 >=cast(268435456/8 as bigint) THEN '2 TerraBytes' ELSE CAST(Round(maxsize*8.0/1024,0) as varchar(20))+'MB' END as MaxSize, FileName AS 'Path' FROM sys.sysfiles sf LEFT join sys.filegroups fg on fg.data_space_id=sf.fileid |
Note that sizes are expressed in pages (one page is 8K bytes), so we multiply the size by 8 to get the pages converted to bytes, and then divide by 1,024 to show the results in megabytes.
Options
Many of the database flags and options shown on the Options tab (Figure 5) can be obtained via the DATABASEPROPERTYEX() SQL function.
Figure 5: Database options
In addition to the DATABASEPROPERTYEX function, most of these bit fields also exist in the sys.database view. Table 19 lists the key column in sys.databases to see the various configuration options.
Automatic settings
Be aware that the automatic settings can impact server performance. For example, if the last user logs out and the database closes automatically, the next user will experience a slight delay as the database gets re-opened.
Table 23: Automatic settings
Column name | Description |
|---|---|
is_auto_close_on | Will database close after last user logs off? |
is_auto_create_stats_incremental_on | Rather than full scan, statistics are only created for table partitions that might have changed, which can increase performance while creating statistics. |
is_auto_create_stats_on | Should the optimizer create statistics on columns that do not have statistics as they are used in a query? |
is_auto_update_stats_on | If SQL detects certain data modifications that indicate statistics might be out of date, it updates them. |
is_auto_update_stats_async_on | When on, the query is run first, and then stats are updated. When off, outdated stats are updated first. |
If you do not automatically update stats, you should put a job or plan in to do so. Outdated statistics can cause the query optimizer not to generate an optimal plan, and the query will run slower than it could.
Tip: If you are unfamiliar with statistics, imagine a table holding a list of students, containing name, gender, and GPA. If a query was run to determine female students with a 3.0 or better GPA, the optimizer would likely search the GPA column first, and then consider gender. This assumes that GPA 3.0 would return fewer records to check gender against. However, if the system was aware that this was an all-male school with only four female students, it would likely check gender first, and then GPA. Statistics provide the information to allow the query optimizer to create the best query plan.
ANSI and NULL settings
These options can impact the way SQL queries and stored procedures operate and can be different between databases on the same server. If you are writing stored procedures, be sure you review these options to make sure your code takes them into account. Table 24 lists some of the settings that impact ANSI and NULL behavior in each database.
Table 24: ANSI and NULL options
Column name | Description |
|---|---|
is_ansi_nulls_on | When ON, you need the IS NULL operator to test for NULL value (ANSI Standard). When OFF, = NULL will find null values. |
is_ansi_warnings_on | When ON, any aggregate function (SUM, COUNT, etc.) will issue a warning if NULL values appear. |
is_arithabort_on | If ON, a query will abort for overflow or div by zero errors. |
is_concat_null_yields_null_on | If ON, string + NULL results in NULL value. |
is_numeric_roundabort_on | If ON, a loss of numeric precision will return an error, otherwise the value will be rounded, and no warning returned. |
For a programming example, the code in Code Listing 22 will return different results depending on the setting of is_concat_null_yields_null_on. (Note that this example assumes the database has a table called site.)
Code Listing 22
--------------------------------------------- -- Script: TellUserAboutMissingName.sql -- Warn user to assign a name to the site --------------------------------------------- SELECT IsNull('Site Name:'+name,'Please assign a name to site '+ cast(siteNumber as varchar)) as Msg FROM site |
Since these settings can change the behavior of SQL queries, it is at a minimum a good idea to check them in the database you are writing code in. Even better, set them to your expected value for the procedure and then restore them when done.
Note: In a future version of SQL Server, you will not be able to change the value of CONCAT equals NULL; it will also default to ON.
If you find some inconsistent behavior between databases, be sure to check these options that impact how the server operates.
Other database properties
There are a fair number of other properties that appear on the Properties tab and can be read from the databases view. Table 25 lists three of these.
Table 25: Other options
Column name | Description |
|---|---|
user_access_desc | SINGLE_USER, RESTRICTED_USER, MULTI_USER |
is_read_only | Is database in read-only mode? |
default_language_name | Default language (English) for the current database. |
The best way to get familiar with them is to look at the database properties tab in SSMS and find the corresponding option in the sys.databases view.
DATABASEPROPERTYEX()
In addition to the databases view, you can also use the SQL DATABASEPROPERTYEX() function to get a lot of the configuration options, as well. The function takes two parameters: the name of the database and the property you want to look at.
Many of the properties provide the same information as the sys.database view. A few sample properties are shown in Table 26.
Table 26: DATABASEPROPERTYEX() parameters
Column name | Description |
|---|---|
LCID | Locale identifier for Windows |
Collation | Collation name for the database |
IsNullConcat | How is NULL concatenation handled? |
You can see the list of all parameters on the Microsoft website.
Comparing two databases
Sometimes it is useful to compare properties between databases, and DATABASEPROPERTYEX can be very handy for that. Code Listing 23 shows code that compares two databases for a list of property values.
Code Listing 23: Compare database properties
--------------------------------------------- -- Script: CompareDBProperties.sql -- Compare some properties between databases --------------------------------------------- DECLARE @targetDB varchar(128) SET @targetDB='' -- SET NAME HERE CREATE TABLE #tmpProps (PropertyName varchar(100)) INSERT INTO #tmpProps values ('LCID'),('Collation'), ('UserAccess'),('IsArithmeticAbortEnabled'), ('IsNullConcat') SELECT PropertyName, DATABASEPROPERTYEX(db_name(),PropertyName) as CurrentDB_Property, DATABASEPROPERTYEX(@targetDB,PropertyName) as TargetDB_Property FROM #tmpProps DROP TABLE #tmpProps |
You can adjust the contents of the #tmpProps table to see the properties you want to compare.
Database permissions
There are two system views that you can use to determine who has various types of permissions for your database.
Database permissions
This view lists all the database permissions, whether they’ve been granted or denied, and the group, role, user, etc., who was granted the permission and who granted the permission. Table 27 shows the fields in the view.
Table 27: Permissions view
Column name | Description |
|---|---|
class | Number class value. |
class_desc | Description of the class value (DATABASE, OBJECT_OR_COLUMN, SCHEMA, etc.). |
major_id | 0 for the database itself; >0 for user objects; <0 for system objects You can use the OBJECT_NAME() function to get the object name associated with the major_id field. |
minor_id | Most often 0, or the column ID number of a table/view object. |
grantee_principal_id | User/role/group who has been granted or denied permission. |
grantor_principal_id | User/role/group who has been granted or denied permission. |
type | Short code for type of permission. |
permission_name | Full name of permission, UPDATE, SELECT, etc. |
state | D – Deny R – Revoke G – Granted W – Grant with option to grant |
state_desc | Full name of state code |
database_principals
This view lists the various database users, roles, groups, etc., that have access to the database. Table 28 lists the key columns needed to show permissions within the database.
Table 28: Database principals
Column name | Description |
|---|---|
Name | Object name (role, user, group). |
principal_id | ID for object, used to link with permissions table (grantor and grantee). |
type | One-letter code for type object R – Database role S – SQL user G – Windows group See complete list of types here. |
type_desc | Full name of type code. |
create_date | Date this object was created. |
modify_date | Date object was modified. |
authentication_type | 0 – None 1 – Instance 2 – Database (SQL Login) 3 – Windows Auth |
We can join the permissions and principals views to explore who has what permissions in our database. The next section lists a few sample scripts to explore permissions.
Who can edit?
Code Listing 24 shows a list of all users, roles, groups, etc., that can manipulate data with your database.
Code Listing 24: Who can edit?
------------------------------------------------ -- Script: WhoCanEdit.SQL -- List users, groups, roles with edit ability ------------------------------------------------ SELECT pr.name,pr.type,p.permission_name FROM sys.database_permissions p JOIN sys.database_principals pr ON pr.principal_id=p.grantee_principal_id WHERE permission_name IN ('DELETE','UPDATE','INSERT') AND state='G' |
Who can select database objects?
You might want to see which groups and roles can select from various tables, including system metadata tables. Code Listing 25 provides this information.
Code Listing 25: Who and what can they select?
-------------------------------------------------------- -- Script: WhoHasSelectRights.sql -- List users, groups, roles with selectable objects -------------------------------------------------------- SELECT pr.name,pr.type,p.permission_name,OBJECT_NAME(major_id) as AllowedView FROM sys.database_permissions p JOIN sys.database_principals pr ON pr.principal_id=p.grantee_principal_id WHERE permission_name IN ('SELECT') AND p.state='G' ORDER BY AllowedView |
What can the public role do?
You might also want to see what rights the public role has, since all users inherit these rights. Code Listing 26 shows how to find what everyone can do.
Code Listing 26: Public role permissions
-------------------------------------------------------- -- Script: WhatCanPublicDo.SQL -- List all permissions granted to public role -------------------------------------------------------- SELECT p.permission_name,OBJECT_NAME(major_id) as AllowedView FROM sys.database_permissions p JOIN sys.database_principals pr on pr.principal_id=p.grantee_principal_id WHERE pr.name = 'Public' and state='G' and class<>0 ORDER BY AllowedView |
In a production environment, you should limit what the public role has access to. Even if just SELECT rights, a lot of the metadata could be exposed to the public role.
Summary
SQL provides a lot of information about the database you are working in. Every property that you can see on the Properties tab in SSMS can be pulled from system views or functions.
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.