[sql-server] SQL Server의 모든 데이터베이스 파일에 대한 정보 나열

Program / 글쓴이

SQL Server에있는 모든 데이터베이스의 파일 (MDF / LDF)에 대한 정보를 나열 할 수 있습니까?

어떤 데이터베이스가 로컬 디스크의 어떤 파일을 사용하고 있는지 보여주는 목록을 얻고 싶습니다.

내가 시도한 것 :

  • exec sp_databases 모든 데이터베이스
  • select * from sys.databases 각 데이터베이스에 대한 많은 정보를 표시하지만 안타깝게도 각 데이터베이스에서 사용하는 파일은 표시하지 않습니다.
  • select * from sys.database_filesmaster데이터베이스 의 mdf / ldf 파일을 표시 하지만 다른 데이터베이스는 표시하지 않습니다.

답변

sys.master_files 를 사용할 수 있습니다 .

master 데이터베이스에 저장된 데이터베이스의 파일 당 행을 포함합니다. 이것은 시스템 전체의 단일보기입니다.

답변

데이터베이스의 위치를 ​​얻으려면 모든 DB 위치 가져 오기를 확인할 수 있습니다 . db의 위치 를 얻고 db 이름을 얻는 데
사용할 수 있습니다.sys.master_filessys.databse

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

답변

스크립트를 사용하여 각 파일에 빈 공간을 확보하고 있습니다.

Create Table ##temp
(
    DatabaseName sysname,
    Name sysname,
    physical_name nvarchar(500),
    size decimal (18,2),
    FreeSpace decimal (18,2)
)
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace)
    Select DB_NAME() AS [DatabaseName], Name,  physical_name,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size,
    Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) -
        Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace
    From sys.database_files
'
Select * From ##temp
drop table ##temp

크기는 KB로 표시됩니다.

답변

이 쿼리를 만들었습니다.

SELECT
    db.name AS                                   [Database Name],
    mf.name AS                                   [Logical Name],
    mf.type_desc AS                              [File Type],
    mf.physical_name AS                          [Path],
    CAST(
        (mf.Size * 8
        ) / 1024.0 AS DECIMAL(18, 1)) AS         [Initial Size (MB)],
    'By '+IIF(
            mf.is_percent_growth = 1, CAST(mf.growth AS VARCHAR(10))+'%', CONVERT(VARCHAR(30), CAST(
        (mf.growth * 8
        ) / 1024.0 AS DECIMAL(18, 1)))+' MB') AS [Autogrowth],
    IIF(mf.max_size = 0, 'No growth is allowed', IIF(mf.max_size = -1, 'Unlimited', CAST(
        (
                CAST(mf.max_size AS BIGINT) * 8
        ) / 1024 AS VARCHAR(30))+' MB')) AS      [MaximumSize]
FROM
     sys.master_files AS mf
     INNER JOIN sys.databases AS db ON
            db.database_id = mf.database_id

답변

이것을 시도 할 수도 있습니다. 

 select db_name(dbid) dbname, filename from sys.sysaltfiles

답변

다음 SQL 실행 (동일한 데이터베이스에 대해 여러 mdf / ldf 파일이없는 경우에만 작동 함)

SELECT
    db.name AS DBName,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'ROWS' and db.database_id = mf.database_id ) as DataFile,
    (select mf.Physical_Name FROM sys.master_files mf where mf.type_desc = 'LOG' and db.database_id = mf.database_id ) as LogFile
FROM sys.databases db

이 출력을 반환합니다

DBName       DataFile                     LogFile
--------------------------------------------------------------------------------
master       C:\....\master.mdf           C:\....\mastlog.ldf
tempdb       C:\....\tempdb.mdf           C:\....\templog.ldf
model        C:\....\model.mdf            C:\....\modellog.ldf

및 나머지 데이터베이스

TempDB에 여러 MDF가있는 경우 (예 : 내 것)이 스크립트는 실패합니다. 그러나 사용할 수 있습니다.

WHERE db.database_id > 4

마지막에 시스템 데이터베이스를 제외한 모든 데이터베이스를 반환합니다.

답변

이 스크립트를 사용하면 사용 된 모든 데이터베이스 이름과 파일을 표시 할 수 있습니다 (시스템 DB 제외).

select name,physical_name from sys.master_files where database_id > 4