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.

May 26, 2009 · From the list of properties from the Database object, let's select the most common ones – Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size and SpaceAvailable. Notice that it is pretty simple and straight-forward.

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.

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.

Dec 20, 2018 · Tired of exploring each database from the menu in SSMS to look for specific information, I decided to make a script listing the extended properties for all my databases.

Sep 11, 2013 · 154 Properties that you can examine and that is just for databases:-) Picking out a few properties you could do something like this. If you want aliases for your column headings you will need to add a bit of code to the select.

Dec 22, 2016 · Below is a simple script that gives you information from SQL Server properties that are of value. Some of the nice information that can be got is around default backup location, Collation, Errorlogpath, Edition of server, databases involved, Case sensitive, logins, memory, logical processors etc.

Feb 23, 2013 · So the properties for each database are contained in the database. This worked though. PropertyValue varchar(max)) EXEC sp_msforeachdb 'INSERT INTO #EP SELECT ''?'' AS DatabaseName, . CAST(name AS varchar), CAST(Value AS varchar) . FROM [?].sys.extended_properties WHERE class=0'

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 …

Feb 13, 2009 · A quick script to grab all the SERVERPROPERTY() values from a SQL instance in a table. Useful when you forget what the propertyname options are.