| Prossima revisione | Revisione precedente |
| hobby:development:sql:select:sys_0003 [2023/08/30 10:13] – creata mauro.cortese | hobby:development:sql:select:sys_0003 [2023/11/13 21:07] (versione attuale) – mauro.cortese |
|---|
| [[hobby:development:sql:select:sys_0003|Query Db User]]\\ | {{:wiki:icons:t-sql.png?nolink&90 |}} <fs xx-large>Query Db User</fs> |
| //<fc #5F5F5F><fs small>(Elenca tutti i DB di un'istanza SQL Server)</fs></fc>//\\ | [[hobby:development:sql|{{ :wiki:icons:left_arrow.png?48|Vai alla mappa}}]]\\ |
| | //<fc #5F5F5F><fs large>(Elenca tutti i DB di un'istanza SQL Server)</fs></fc>// |
| | \\ |
| | \\ |
| | <sxh sql> |
| | SELECT |
| | db.database_id ID, |
| | db.name DBName, |
| | mf.name LogicalName, |
| | mf.type_desc FileType, |
| | mf.Physical_Name Path, |
| | mf.size Size, |
| | db.collation_name Collation, |
| | db.recovery_model_desc RecMode, |
| | CompatibilityLevel = CASE db.compatibility_level |
| | WHEN 65 THEN 'SQL Server 6.5 (65)' |
| | WHEN 70 THEN 'SQL Server 7.0 (70)' |
| | WHEN 80 THEN 'SQL Server 2000 (80)' |
| | WHEN 90 THEN 'SQL Server 2005 (90)' |
| | WHEN 100 THEN 'SQL Server 2008/R2 (100)' |
| | WHEN 110 THEN 'SQL Server 2012 (110)' |
| | WHEN 120 THEN 'SQL Server 2014 (120)' |
| | WHEN 130 THEN 'SQL Server 2016 (130)' |
| | WHEN 140 THEN 'SQL Server 2017 (140)' |
| | WHEN 150 THEN 'SQL Server 2019 (150)' |
| | WHEN 160 THEN 'SQL Server 2022 (160)' |
| | ELSE 'new unknown - ' + CONVERT(varchar(10),compatibility_level) |
| | END |
| | FROM |
| | sys.master_files mf |
| | INNER JOIN |
| | master.sys.databases db ON db.database_id = mf.database_id |
| | WHERE db.database_id > 4 |
| | ORDER BY DBName, FileType |
| | |
| | </sxh> |
| | |
| | <datatable> |
| | ^ ID ^ DBName ^ LogicalName ^ FileType ^ Path ^ Size ^ Collation ^ RecMode ^ |
| | | 5 | Bom | Bom_log | LOG | P:\UserLog\LOG\Bom.ldf | 96 | Latin1_General_CI_AS | FULL | |
| | | 5 | Bom | Bom_dat | ROWS | P:\UserDB\DATA\Bom.mdf | 88152 | Latin1_General_CI_AS | FULL | |
| | | 6 | ErpLauncher | ErpLauncher_log | LOG | P:\UserLog\LOG\ErpLauncher.ldf | 3857 | Latin1_General_CI_AS | FULL | |
| | | 6 | ErpLauncher | ErpLauncher_dat | ROWS | P:\UserDB\DATA\ErpLauncher.mdf | 112792 | Latin1_General_CI_AS | FULL | |
| | | 7 | ErpTools | ErpTools_log | LOG | P:\UserLog\LOG\ErpTools.ldf | 640 | Latin1_General_CI_AS | FULL | |
| | | 7 | ErpTools | ErpTools_dat | ROWS | P:\UserDB\DATA\ErpTools.mdf | 1280 | Latin1_General_CI_AS | FULL | |
| | | 18 | Hrm | Hrm_log | LOG | P:\UserLog\LOG\Hrm.ldf | 776 | Latin1_General_CI_AS | FULL | |
| | | 18 | Hrm | Hrm_dat | ROWS | P:\UserDB\DATA\Hrm.mdf | 1280 | Latin1_General_CI_AS | FULL | |
| | | 8 | Ict | Ict_log | LOG | P:\UserLog\LOG\Ict.ldf | 3072 | Latin1_General_CI_AS | FULL | |
| | | 8 | Ict | Ict | ROWS | P:\UserDB\DATA\Ict.mdf | 1024 | Latin1_General_CI_AS | FULL | |
| | | 9 | LiveBom | LvtBom_log | LOG | P:\UserLog\LOG\LvtBom.ldf | 104 | SQL_Latin1_General_CP1_CI_AS | FULL | |
| | | 9 | LiveBom | LvtBom | ROWS | P:\UserDB\DATA\LvtBom.mdf | 2584 | SQL_Latin1_General_CP1_CI_AS | FULL | |
| | | 10 | LivePdm | LvtPdm_log | LOG | P:\UserLog\LOG\LvtPdm.ldf | 136 | SQL_Latin1_General_CP1_CI_AS | FULL | |
| | | 10 | LivePdm | LvtPdm | ROWS | P:\UserDB\DATA\LvtPdm.mdf | 5960 | SQL_Latin1_General_CP1_CI_AS | FULL | |
| | | 12 | LiveRights | LiveRights_log | LOG | P:\UserLog\LOG\LiveRights.ldf | 640 | Latin1_General_CI_AS | FULL | |
| | | 12 | LiveRights | LiveRights_dat | ROWS | P:\UserDB\DATA\LiveRights.mdf | 1280 | Latin1_General_CI_AS | FULL | |
| | | 13 | LiveSuite | LiveSuite_log | LOG | P:\UserLog\LOG\LiveSuite.ldf | 1024 | Latin1_General_CI_AS | FULL | |
| | | 13 | LiveSuite | LiveSuite_dat | ROWS | P:\UserDB\DATA\LiveSuite.mdf | 7216 | Latin1_General_CI_AS | FULL | |
| | | 14 | LiveSys | LiveSys_log | LOG | P:\UserLog\LOG\LiveSys.ldf | 640 | Latin1_General_CI_AS | FULL | |
| | | 14 | LiveSys | LiveSys_dat | ROWS | P:\UserDB\DATA\LiveSys.mdf | 1280 | Latin1_General_CI_AS | FULL | |
| | | 20 | LiveWatermark | LiveWatermark_log | LOG | P:\UserLog\LOG\LiveWatermark.ldf | 640 | Latin1_General_CI_AS | FULL | |
| | | 20 | LiveWatermark | LiveWatermark_dat | ROWS | P:\UserDB\DATA\LiveWatermark.mdf | 1280 | Latin1_General_CI_AS | FULL | |
| | | 17 | LvtPdmBase | LvtPdmBase_log | LOG | P:\UserLog\LOG\LvtPdmBase.ldf | 579072 | Latin1_General_CI_AS | FULL | |
| | | 17 | LvtPdmBase | LvtPdmBase_dat | ROWS | P:\UserDB\DATA\LvtPdmBase.mdf | 251072 | Latin1_General_CI_AS | FULL | |
| | | 16 | LvtPdmLibrary | Pdm_log | LOG | P:\UserLog\LOG\Pdm_Library.ldf | 114544 | Latin1_General_CI_AS | FULL | |
| | | 16 | LvtPdmLibrary | Pdm_dat | ROWS | P:\UserDB\DATA\Pdm_Library.mdf | 251072 | Latin1_General_CI_AS | FULL | |
| | | 11 | LvtPdmProd | Pdm_log | LOG | P:\UserLog\LOG\LvtPdmProd.ldf | 128 | Latin1_General_CI_AS | FULL | |
| | | 11 | LvtPdmProd | Pdm_dat | ROWS | P:\UserDB\DATA\LvtPdmProd.mdf | 155112 | Latin1_General_CI_AS | FULL | |
| | | 15 | Pdm | Pdm_log | LOG | P:\UserLog\LOG\Pdm.ldf | 128 | Latin1_General_CI_AS | FULL | |
| | | 15 | Pdm | Pdm_dat | ROWS | P:\UserDB\DATA\Pdm.mdf | 193304 | Latin1_General_CI_AS | FULL | |
| | | 24 | PdmGarbage | Pdm_log | LOG | P:\UserLog\LOG\PdmGarbage.ldf | 128 | Latin1_General_CI_AS | FULL | |
| | | 24 | PdmGarbage | Pdm_dat | ROWS | P:\UserDB\DATA\PdmGarbage.mdf | 2416 | Latin1_General_CI_AS | FULL | |
| | </datatable> |
mauro.cortese