PVI_COPY

11_1_3_SQLServer_список всех баз на сервере

Apr 12th, 2021
154
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. -- 11_1_3_SQLServer_список всех баз на сервере
  4.  
  5.  
  6. --Набор скриптов для знакомства с SQL Server
  7. --https://infostart.ru/1c/articles/1128594/
  8.  
  9. ---------------------------------
  10. EXEC sp_helpdb;
  11.  
  12.  
  13. EXEC sp_Databases;
  14.  
  15.  
  16. SELECT  @@SERVERNAME AS Server ,
  17.         name AS DBName ,
  18.         recovery_model_Desc AS RecoveryModel ,
  19.         Compatibility_level AS CompatiblityLevel ,
  20.         create_date ,
  21.         state_desc
  22. FROM    sys.databases
  23. ORDER BY Name;
  24.  
  25.  
  26. SELECT  @@SERVERNAME AS Server ,
  27.         d.name AS DBName ,
  28.         create_date ,
  29.         compatibility_level ,
  30.         m.physical_name AS FileName
  31. FROM    sys.databases d
  32.         JOIN sys.master_files m ON d.database_id = m.database_id
  33. WHERE   m.[type] = 0 -- data files only
  34. ORDER BY d.name;
  35.  
  36.  
  37. ---------------------------------
  38. -- Размре БД
  39. with fs
  40. as
  41. (
  42.     select database_id, type, size * 8.0 / 1024 size
  43.     from sys.master_files
  44. )
  45. select
  46.     name,
  47.     (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
  48.     (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
  49. from sys.databases db
  50.  
  51. -- После того, как стало известно о размере баз данных, можно посмотреть сколько место фактически используется.
  52.  
  53. SELECT SUM(unallocated_extent_page_count) AS [free pages],
  54.     (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
  55. FROM sys.dm_db_file_space_usage;
  56.  
  57.  
  58.  
  59. ---------------------------------
  60. -- Расположение файлов БД
  61.  
  62. EXEC sp_Helpfile;
  63.  
  64.  
  65. SELECT  @@Servername AS Server ,
  66.         DB_NAME() AS DB_Name ,
  67.         File_id ,
  68.         Type_desc ,
  69.         Name ,
  70.         LEFT(Physical_Name, 1) AS Drive ,
  71.         Physical_Name ,
  72.         RIGHT(physical_name, 3) AS Ext ,
  73.         Size ,
  74.         Growth
  75. FROM    sys.database_files
  76. ORDER BY File_id;
  77.  
  78.  
  79.  
RAW Paste Data