Menu
Is it possible to list information about the files (MDF/LDF) of all databases on an SQL Server?
I'd like to get a list showing which database is using what files on the local disk.
What I tried:
exec sp_databases all databasesselect * from sys.databases shows a lot of information about each database - but unfortunately it doesn't show the files used by each database.select * from sys.database_files shows the mdf/ldf files of the master database - but not the other databases
630
You have two native options for finding out where the SQL server stores its database files: either right-click on the instance name in SQL Server Management Studio (SSMS) and navigate to the 'Database Settings' tab, or use a T-SQL query.
To do that we will be using the below given commands: CREATE TABLE [database_name.] table_name ( pk_column data_type PRIMARY KEY, column_1 data_type NOT NULL, column_2 data_type, ..., table_constraints ); If we do not mention the name of the database then the default USE database is selected for the creation of tables.
If you ever need to know where your database files are located, run the following T-SQL code: USE master; SELECT name 'Logical Name', physical_name 'File Location' FROM sys. master_files; This will return a list of all data files and log files for the SQL Server instance.
You can use sys.master_files.
Contains a row per file of a database as stored in the master database. This is a single, system-wide view.
200
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
