Dec 17, 2024 · Use this page to view or modify options for the selected database. For more information about the options available on this page, see ALTER DATABASE SET Options (Transact-SQL) and ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL). Specify the collation of the database by selecting from the list.

This topic describes how to view or change the properties of a database in SQL Server by using SQL Server Management Studio or Transact-SQL. After you change a database property, the modification takes effect immediately.

Sep 3, 2024 · For a specified database in SQL Server, this function returns the current setting of the specified database option or property. Transact-SQL syntax conventions. An expression specifying the name of the database for which DATABASEPROPERTYEX will return the named property information. database has an nvarchar (128) data type.

Aug 17, 2017 · You are looking for DATABASEPROPERTY and SERVERPROPERTY. Just list them in a SELECT statement like: [Service_Name] = @@SERVICENAME. And add/delete any property you need. Thanks. It would be nice if there was a way to dynamically select all the options. I stumbled across your old post.

Apr 1, 2023 · SQL Server has a built-in function called DATABASEPROPERTYEX that allows you to return the specific information you are looking for, for one or all databases. This function can be called from a SELECT statement to return the results of one or more databases.

Feb 23, 2013 · AS DatabaseName, CAST(name AS varchar), CAST(Value AS varchar) FROM [?].sys.extended_properties WHERE class=0' And if you want all the databases and just properties where they exist. SELECT db.Name, #EP.PropertyName, #EP.PropertyValue FROM sys.databases db LEFT OUTER JOIN #EP ON db.name = #EP.DatabaseName

Jun 27, 2019 · I don't have an SQL Server - but you do have documentation which reads, " DATABASEPROPERTYEX returns only one property setting at a time. To display multiple property settings, use the sys.databases catalog view.". All property names will have to be explicitly written one way or another. This is how I'd do it: No, that's not possible.

May 28, 2020 · In this article, we explore a SQL function to extract SQL database metadata using the DATABASEPROPERTYEX function. We might want to retrieve the database property value. You can connect to SQL Instance using SSMS and …

Dec 17, 2020 · SQL Server does have a built-in DATABASEPROPERTYEX function that will return specific information for one or all databases, but to get the database files’ physical size, you’ll have to query each different database.

Jan 7, 2025 · Learn how to use the Configurations tab in the Database Properties dialog box to view or modify a database scoped option.