{"id":1519,"date":"2010-08-17T09:35:29","date_gmt":"2010-08-17T08:35:29","guid":{"rendered":"http:\/\/blog.capdata.fr\/?p=1519"},"modified":"2022-12-01T17:57:55","modified_gmt":"2022-12-01T16:57:55","slug":"une-alternative-a-xp_fixeddrives","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/une-alternative-a-xp_fixeddrives\/","title":{"rendered":"Une alternative \u00e0 xp_fixeddrives"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Pour faire simple, le besoin est de pouvoir r\u00e9cup\u00e9rer l’\u00e9quivalent d’un df <\/strong>(UNIX) en Transact-SQL, c’est \u00e0 dire \u00e7a:<\/p>\n

$ df -k\r\nSys. de fich.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1K-blocs\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Occup\u00e9     Disponible  Capacit\u00e9     Mont\u00e9 sur\r\nc:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 40957684\u00a0    28061160\u00a0   12896524\u00a0   69%         \/cygdrive\/c\r\ne:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 210170360     133011444\u00a0  77158916\u00a0   64%         \/cygdrive\/e\r\ng:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 78153152\u00a0    19149152\u00a0   59004000\u00a0   25%         \/cygdrive\/g\r\nv:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 61440576\u00a0    30980608\u00a0   30459968\u00a0   51%         \/cygdrive\/v<\/span><\/pre>\n
Il existe dans SQL Server une proc\u00e9dure stock\u00e9e \u00e9tendue xp_fixeddrives<\/em> qui renvoie une approximation table-valu\u00e9e du df, mais il y manque des informations importantes comme par exemple la taille du drive:<\/p>\n
exec xp_fixeddrives\r\n\r\ndrive   Mo disponibles\r\n-----   --------------\r\nC\t12594\r\nE\t75350\r\nG\t57621\r\nV\t29746<\/span><\/pre>\n
Il nous fallait donc r\u00e9\u00e9crire notre propre xp_fixeddrives <\/em>en le formattant comme un df<\/strong> (sans l’information du point de montage), sous la forme d’une fonction table valu\u00e9e en SQLCLR *.<\/p>\n
Le source de fn_fixeddrives.dll<\/em>: <\/strong>
\n<\/strong><\/h3>\n
On r\u00e9cup\u00e8re les propri\u00e9t\u00e9s des lecteurs assez simplement en utilisant la m\u00e9thode GetDrives()<\/em> de la classe DriveInfo<\/em>, et on stocke le r\u00e9sultat dans un tableau, que l’on affiche ensuite ligne \u00e0 ligne.<\/p>\n
using System;\r\nusing System.Data;\r\nusing System.Data.SqlClient;\r\nusing System.Data.SqlTypes;\r\nusing Microsoft.SqlServer.Server;\r\nusing System.IO;\r\nusing System.Collections;\r\n\r\npublic partial class cFixedDrives\r\n{\r\n    static DriveInfo[] _t_drvlist;\r\n\r\n    [Microsoft.SqlServer.Server.SqlFunction\r\n        (\r\n            FillRowMethodName = \"_f_fill<\/span>\",\r\n            TableDefinition = \"Drive nvarchar(3), SizeMb bigint, UsedMb bigint, \"+<\/span>\r\n            \"AvailableMb bigint, UsePct bigint<\/span>\"\r\n        )\r\n    ]\r\n\r\n    \/\/ Main<\/span>\r\n    public static IEnumerable fn_fixeddrives()\r\n    {\r\n        _t_drvlist = DriveInfo.GetDrives();\r\n        ArrayList _t_index = new ArrayList();\r\n        int i = 0;\r\n\r\n        foreach (DriveInfo _drv in _t_drvlist)\r\n        {\r\n            if ((\"Fixed<\/span>\" == _drv.DriveType.ToString()) && (_drv.IsReady))\r\n            {\r\n                _t_index.Add(i);\r\n            }\r\n            i++;\r\n        }\r\n\r\n        return _t_index;\r\n    }\r\n\r\n    \/\/ _fill<\/span>\r\n    private static void _f_fill(Object _v_Obj, out string _v_drvName,\r\n                                out long _v_totalsizeMb, out long _v_usedsizeMb,\r\n                                out long _v_freesizeMb, out long _v_pctused)\r\n    {\r\n        int _t_row = (int)_v_Obj;\r\n\r\n        _v_drvName = _t_drvlist[_t_row].Name;\r\n        _v_totalsizeMb = _t_drvlist[_t_row].TotalSize \/ 1048576;\r\n        _v_usedsizeMb = (_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)\r\n                          \/ 1048576;\r\n        _v_freesizeMb = _t_drvlist[_t_row].TotalFreeSpace \/ 1048576;\r\n        _v_pctused = ((_t_drvlist[_t_row].TotalSize - _t_drvlist[_t_row].TotalFreeSpace)\r\n                        * 100 \/ _t_drvlist[_t_row].TotalSize);\r\n    }\r\n};<\/span><\/span><\/pre>\n
Globalement, pour qu’une fonction SQLCLR puisse retourner une table, elle doit renvoyer un type IEnumerable. <\/em>L’\u00e9l\u00e9ment important est dans le r\u00f4le de la fonction de traitement des lignes ( _f_fill()<\/em> ). Cette fonction va \u00eatre appel\u00e9e de mani\u00e8re it\u00e9rative par la fonction principale fn_fixeddrives()<\/em> \u00e0 chaque fois qu’une ligne du tableau est fetch\u00e9e, un peu comme dans un curseur, et va traiter et retourner la valeur de chaque colonne de la ligne. A la fin de l’it\u00e9ration, on obtient une table.<\/p>\n
La seule vague astuce consiste \u00e0 stocker dans un Arraylist <\/em>les positions dans le DriveInfo[]<\/em> o\u00f9 les lecteurs sont de type ‘Fixed<\/em>‘, car les propri\u00e9t\u00e9s de taille ou d’espace libre ne sont pas renvoy\u00e9es pour les autres types de lecteurs (par exemple les lecteurs de DVD), et plut\u00f4t que de passer \u00e0 _f_fill()<\/em> le tableau DriveInfo[]<\/em> contenant la liste des drives, on passe juste un petit tableau d’entiers contenant les positions des disques fixes, et on s’en sert comme d’un index pour affecter les colonnes en retour. On utilise un ArrayList <\/em>\u00e0 la place d’un int[] <\/em>parce qu’on ne sait pas au d\u00e9part combien de lecteurs fixes on aura sur la machine.<\/p>\n
A noter le test && (_drv.IsReady)<\/span> qui permet d’\u00e9viter de provoquer une IOException <\/span>si le drive n’est pas accessible.<\/p>\n
D\u00e9claration de l’assembly et de la fonction dans SQL Server:<\/h3>\n
La m\u00e9thode GetDrives() <\/em>est consid\u00e9r\u00e9e comme UNSAFE<\/em>, en raison du type d’appels qu’elle effectue vers le kernel pour r\u00e9cup\u00e9rer les propri\u00e9t\u00e9s et les tailles des disques. Pour charger l’assembly dans SQL Server, il faut activer ‘clr enabled<\/em>‘ et param\u00e9trer la base pour accepter de charger des assemblies UNSAFE:<\/p>\n
sp_configure 'clr enabled<\/span>',1\r\nGO\r\nreconfigure\r\nGP\r\nalter database clrtest set trustworthy on\r\nGO<\/span><\/pre>\n
Enfin, cr\u00e9er l’assembly et la fonction:<\/p>\n
use clrtest\r\nGO\r\nCREATE ASSEMBLY fixeddrives from 'E:\\CAPDATA\\DEV\\CLR\\fn_fixeddrives\\bin\\Debug\\fn_fixeddrives.dll<\/span>' WITH PERMISSION_SET = UNSAFE\r\nGO\r\nCREATE FUNCTION fn_fixeddrives ()\r\nRETURNS TABLE (\"Drive<\/span>\" nvarchar(3), \"Size(Mb)<\/span>\" bigint, \"Used(Mb)<\/span>\" bigint, \"Available(Mb)<\/span>\" bigint, \"Use%<\/span>\" bigint)\r\nAS EXTERNAL NAME fixeddrives.cFixedDrives.fn_fixeddrives\r\nGO<\/span><\/pre>\n
Finalement:<\/p>\n
select * from fn_fixeddrives()\r\nGO\r\n<\/span><\/pre>\n
Drive   Size(Mb)  Used(Mb)  Available(Mb)    Use%\r\n----    --------  --------  --------------   -----\r\nC:\\\t39997\t  27403\t    12593\t     68\r\nE:\\\t205244\t  129893    75350\t     63\r\nG:\\\t76321\t  18700\t    57621\t     24\r\nV:\\\t60000\t  30254\t    29746\t     50<\/span><\/pre>\n
A+ David B.<\/p>\n
* Note<\/strong>: A noter qu’un pr\u00e9c\u00e9dent <\/a>existe et a \u00e9t\u00e9 publi\u00e9\u00a0 par Andy Novick dans mssqltips.com, mais sa fonction ne filtre pas sur les lecteurs DVD ou CD, et elle est oblig\u00e9e de passer par des pseudo-types SQL (sqlint64) pour tenir compte des colonnes nullable.
\n<\/em><\/p>\n
C\u00a0\u00a0 \u00a012594
\nE\u00a0\u00a0 \u00a075350
\nG\u00a0\u00a0 \u00a057621
\nV\u00a0\u00a0 \u00a029746<\/div>\n