知方可补不足~写了一个计算数据表占用存储空间的方法回到目录
这个例子是关于计算数据表中所有字段在一定的数据量下占用存储空间的情况,使用了sys.tables,SYSTYPES和SYSCOLUMNS几张系统表,意思就是遍历所有数据表,然后对表的所以字段的length进行sum,就可以了,方法很容易理解,没什么技术含量,但即起到了重要的效果。
DECLARE @tableName VARCHAR(50)DECLARE @totalRecord BIGINTSET @tableName = 'user_info'SET @totalRecord = 1000000SELECT @tableName + '表中有' + LTRIM(STR(@totalRecord)) + '条数据时,占用的空间为:' + LTRIM(STR(SUM(c.length) * @totalRecord / 1024.0 / 1024.0)) + 'MB'FROM SYSTYPES T , SYSCOLUMNS CWHERE T.XTYPE = C.XTYPE AND C.ID = ( SELECT ID FROM SYSOBJECTS WHERE NAME = @tableName ) SELECT TT.NAME , DATA = @TOTALRECORD , SIZE = ( SELECT STR(SUM(C.LENGTH) * @TOTALRECORD / 1024.0 / 1024.0) + 'MB' FROM SYSTYPES T , SYSCOLUMNS C WHERE T.XTYPE = C.XTYPE AND C.ID = TT.OBJECT_ID )FROM SYS.TABLES AS TTORDER BY TT.NAME
执行结果如下
PRD/CF0r5N+FwtbfugnUi7+nk9TP8t9gibl0/paiD+hDYds5LZ6Qr+NhHf3J/By6rV+F3BAPMcTpjr64xvVh4CcFxRz8vgcWEk/psf09VAp7N6mldnu3T8RPbGHW0XhG0/zINvWqWjxr0Hz7N2QOw8Kd8AL8tMkO7Lk1ni7sykip/Xw6HrDgzoHk57+Hk9HF5fl65Ud7mIj8relJ+7/eJzRd2OVg0V7d90QLoa2ULNYM2RcqX5wVYPugXc+fVe2H672rjdp+y4oGYNg/rYYKXj51MbyB7fgEaxtMWf1wPrLvIWvB0xS7QSG6jNWelZzV+JT07vgPWOzz4e34tvJtNAy3nTMWvAkwKbuP1es1t/0lHpuDvYfr6Zb9drGfFtDkBGcJtweL3YO4erTQ+65ztujY4bpPnZbGf2NrdnfAPPwnE6V3sL0+rva9MgJ80N3F71uTqOH7oNMO0BrQL3Y9YrnwuIbzMuHWOsu5hhyDEYu4tvbu/IX/lwyd2IpZPbZ/x231kwg1fyT9wtb7Vxm0df8x5Qv3pe1hbvPe5aPQvQVNMAlo1vkOPURqF0F0XvUMSCiYF3883Zn3RfG/cFk/9dbc3KWeednwTP12G/vCXYwq4GYUL5ZrNk9VufK7J6+RyHcB/Xh2/rluSfn5RmQw7wfIl4jKQ1I2hfPl0rde/cCz4tw3SZz1cuvu3x/M32wVFva6Omo3iN5V7japQgiuWbhtWjqPWu+ZJNpfD1JZqzbK1Q+0nQV+WcuE8bvfrdfTSn2VqeZ+N222yD/3nZfugwyYfntq88Wwiu9ZPi9r3AZwGJFeaVAK/NNZBvtp3kh9YQbfzRGo27+YJ7dhhwhzdW273tGvlYqG3jhufhfOyZKxuMbwTCXmCJJA92Xz9JIBQIli51nOIbgaCBRe9MaoWVkMROf+lvGX9T6SkQCAQTxDcCIR+IbwRCPhDfCIR8IL4RCPlAfCMQ8oH4RiDkA/GNQMgH4huBkA/ENwIhH4hvBEI+EN8IhHwgvhEI+UB8IxDygfhGIOQD8Y1AyAfiG4GQD8Q3AiEfiG8EQj4Q3wiEfCC+EQj5wG6J9bmoUKHCC8U3AiEfKL5RoZKvEN+oUMlXiG9UqOQrLMXLOggEAoiE7+sgEAgaUs1P/vnzJ9GeCQ5gdzt2+1eRZPz2798/7rjde8ztFOxux26/zwkS3+op2N2O3X6fEyS+1VOwux27/T4nmJdv388H9vD2Lf37+Nzzbz+emPYtY6x7X37+9fbI2LT99JWEvx/7e3P34nS73Veq2z87poNfAu0CZbNfPYXZft54Pp4Ag4tsIbvwbeHMr863z4797Z4OL19i48eHg7wxk/gmE5XKqtuFu77eHpXGp7t9Lu9/2dOnun/Llunt/33/y3n+/XyYDIPbwPvfiVpFtpA9+Pb4/NYZt6jf/26/X2+PT5+/739VNr51i6M/ng7d04H4dq/be/hfw+1zMflm2zK7/fbKz47Nt4MiW8g+fOu5L2SnfD8fuvfb79fbo3Qne3zuP+Yb6mf38PYB/ZCKp9tFjcQl0+3mNitbprf/9+vtkelx1WgD/cuDretUSNmJb1PPRA36/cvDFPT6lwflFjVv8/5XI6rSOdd7Pm0Wb74JzgBun4vON/uW6e2fbZ6utXo7ls9RNrjIFrIb324af4Q3GdMHaV9vj0+fH0/zIJji2ya3T4VzCXK7vs1UHFumt18uH0/QGF4a4MGnXETZkW//9S8PynhMmYd8ePsWG/cvD4fHByUeFunN3Uvw+A10+/yvyjfXluntV8pimHRSYthmO+Uyyp58m6aSGGeU5K/5X6X3qLq4SG/uXkLnJ2G3i6sj+ObcMrn9/cuD8kxCbQPKt/Apl1L25Rsf4EKdAfb0abrMOn4zbm9NlsDnbxa3//Jv5c+OLdPbrzwVlGZ65uGG/vgNfP5WQguh9SVVFexux26/zwkS3+op2N2O3X6fEyS+1VOwux27/T4nyKJkrZrgjku0fwII7G7Hbv8qmDsddQvGcUy3c4IN2N2O3X43SC+ISlmFMVbz3zEN/vz5c/v9L9HOCQ5gd/vt979rvWBjgrHpv3//iG97AbvbGWN7kyIhiG+1AbvbKb4R3zABu9vbjG9Aei/xDQXW3N6f2KmX/pmx1KWr8bc/S8u/dKy72Gp+Xg+MSd//vB4YO7z+8K+WVWKvP2FHNfnGF6QR31DC7vbhfFQZMJyPx/Mgf0pX44308Y0zhvPJqPl5PRwOB86m6XvBN3mzMMZRfKsNAfHN/JiuJsT+OLRawVp8O7y+dguZLt2h6w4m34CdrGAHvomeRnBfg7AOf77JoWf6nK7G3/5c47d1vv1cZsJdusPr5RXgG7L4FtzXIKwDO9/KiW8/M50unfgcf/yWnm98HMEYI77FBna+FRTfrj+vh+4yVf9A8Q1Df7I/cZJRfEsAGr/5wYdv87xJd7la+BbcodyFb9KlIL7FRgDfGp2fnODFN8EukG8K97ywQ39S7k4S36IjgG/0/M1WY8YtePwWyDZaX1IfsLs9V3zbB8S32oDd7bni2z4gvtUG7G6vP76lyBsnvu0F7G6vPL6lSx3HfuGRArvbK49v0TuTVKhsLKwE4YM0f0lPoTZgd/ut7v7kSPMldQG724lvxDdMwO524hvxDROwu705vol3kZiv+YnIt/6UbzVX4mPJGX2+i5g81xUqi9+8drzGN1rPdb2IFn7RauaKbHoK0tu0xIvCUvCtP7HT6WRtcrw5xsgh2HosM8nEfhzfxuXPN3kzj1/Z3Y5DTyE136Q1xsvHS7cQZ1lCmV1PYebefW/N8uDbcD6e+rG3kiBmqk6kY5XAN49DBMS3pvNxrleAL3IeTlY9hcTxbaKA0prkDshyJ+ZRZTgf5aw5ttxE5184W+72Y/Xn4/F0WjY7n5Rek3rss1dt2ZzbezwPkjn9yXEGMeObbmSZ+aYZ+Wak02jZNxn1FPjLR5PwbaaA0v9Q23Cv9/F4y142la6j85JGOJbWiMQOtHu35VY+/3D5dvq5qFy7WYQN34hvvvh5PcjjL2XIlllPYQvZPPimNiJwaGSOqeYPMmuYupeUx1J/aPaa5GrgyPPeJEONwZTVVcJa+U5hBfHNBxrZ5GopmzuLnsJGsq3zjUccfjl6Dw5oW3q1vXjH0hpRf2Kn3hgRynyzykb0J96NnUKn8zS01uoxGKLx2yosZJu/momWSU/h42mL0qsP36AhSb/axxuXkCD/J27Ulmsa51gm3+BBl8Y3dZAnhoUhN46k8yXa/aWN+cnrpWOWUZsR39LrKYiHb1P/NP78pDFimRuc9sBm+pePc/iW2t3e3ZmMdCwx1hL9P2jgpZBB6ciqXWB1vmSqt5yC3lzXw3oA35p8/nbRWvg8I6L8S3oKpSG0l5QP2N2eZ/y2F2rim7nMIw0lnBP4W/e72X7iW8moiW+ECSS+lQ3SU6gN2N1eOd9IT6EyYHd75XyL3pmkQoWKrZCeQm3A7vZb3fFtpPmSuoDd7cQ34hsmYHc78Y34hgnY3d4c3/iSrvuS33z5lktPQV5Xtf78OHi5X3FY41uj67nUZVjzSixdRmGG+qIcFlNYAVqvPCcH3P9KqnL0FNQVl/1p9erXzDcjBajN9crX65RnA8goCA7JGaUxhRXc+d0Pb9+J+DZk0VMI30vNfJtA+ThTAqnCkYmA4nuVSBGFFex8+34+pItvQxY9BRt5xA+lOz3vGog0GefOS4U/3xrNN+U8E/FNS4gzXrwYT1jBnt+dcvw25NFTgOvFsaRdaWk2HjsvFcQ3N+RQtozfRNBaqlUixRNW2EUvSEkNS6inAF5qz/ayuvNSQXxzAu74iVCnbRNbWGEPPbwhm54CdK19+VZoetsqaPzmgKXfp+VpAy/yjiWssIPeq0aChUap9BSM+UlXf3LZ3mvnZSKAb83NTyqsEHzRFRYAvsUSVnA9f7svuK3xLaeewvIjZc+asIFWc1wO57XzAhHAt5aev12vQKfP6/lbVGEFWl9SG7C7PfH4bWfUxLdcegplg/hWMmriG2EciW9lg/QUagN2t1fON9JTqAzY3V4536J3JqlQoWIrpKdQG7C7/VZ3fBtpvQQuYHc78Y34hgnY3U58I75hAna3N8q3zy7Req4FefQU7Mv3lLfdVIQ1vjW6notnAHgKK4jX6MiZ35uFFSx8+34+pFo/KV329HoK9p+X+4KbjbC7nS8SbW+9ssQOvR4SVrC8ES6CsALIt6+3x4e/3Z3JOH58G7LoKYSmotaAgPjWSj7Oz+tByyidsSassPx42Wi7sALEt48n1r3fnfzmxbchi57CqGS4nU98/734OELyCpjhz7dW8k0vHesuQOBZF1a46m883SysYPJtluVKyrchj56CfP2Z2sqUDo+WDocbxDeAbZbEmjVhBYl/kYQVgHzTiWYp+TZk0lPwaRFbmkaRIL5ppJL5In2zLqygBLtIwgoa377eHpVWjFxPgfhmoqnxmz4ZqdMH2l7MR2qRLoawgvV5QLr4prXqhUZJ9BQ87sDt9icbmp/UebAmrKArLEQTVsjPt7x6Cp49nrXdIEIA31p6/qYwbE1YQTx841ExkrACrS+pDdjdnohvhaAmvpGewjgS38pGTXwjjCPxrWyQnkJtwO72yvlGegqVAbvbK+db9M4kFSpUbIX0FGoDdrff6o5vI82X1AXsbie+Ed8wAbvbiW/EN0zA7vbW+Pb+VzwvTvf+7uR6CubD7/teF+c8QCT73flEgSexxrdG13PJMNUT+JIsKQ8ugnqCCWv+W+r4tlVPwVwGaT+O7wUP4duK/UFYzd8LsdDu9qFdPQUZhnqCumBZygLYrJ5gwuDb9/Ph8blPzrchjp7Cbnxbsz8I/nzzOJ2A+FZ/Po4bco4N10kQ2anb1RNMQHzjwbJkPYX+fDyeTstm55PSk5GaqKX7tGyu5AYIc/oTc/YWNft7fnh+uNkK/YgDN1ucJXMcK2Z80x3SQr6pC0qyG1dWkHJtNqsnmDD49vHE5viW6n3CYxQ9BTNtVG/xevPSLZjpwCVMeO6PkTHkZT8nnq72oBxR3DikU3YcUB+/rYcK4psPwAGaGI9FUk8w4ZqfvDsFLpOegvpDsyejNS/9yPPepOhiDHB87V8O05+O517taWpHBMelvv1Jr4RY4ps3VC2guUbuYW5VTzCxB9+GSHoK2oXtT+zUGyMqmW9SHFFbwRyWROh0NmvT/mEc+9PxfD5N4XEhHXDETXzzGgzR+M0fShiTamKpJ5gA9IJY9377/e/2+/43zfMAaEgSrqdg8g0edGl8Uwd5Su/OVxzFMqQSzwd4pxI+ohT4xBhyl/kScxYxXY03UvMN0HJV9fBgdt2lnmACiG+fy9OJovUUxFhL9P+gcZDSQJWOIB84AvMlUz3YUGz2q+MxqUlbe87i2Efv+cn1LmUA35p//sZjE6BeHkM9wURN60tCey51IrvbIyNnfzI/auHb2gT+pv0qSEfpOMcivpWMWvhGWIDd7fXzjfQUagJ2t1fON9JTqAzY3V4536J3JqlQoWIrpKdQG7C7/VZ3fBtpvqQuYHc78Y34hgnY3U58I75hAna3t8e3j6dlscp7Qr7h1VOwb6asLtsJa3xrbj2XoZ4gv/1GyjMFFmhJy/8jKSzA+iVT2ttnl+Z9i/yyI9VTsG5WxIIyu9uNdKMW1iub6glAEo143ZskqKDyL5LCgiPfNGl/ckCsp+C+BXgeKxkC4lsD+Tharg38Jm/5JcJX7QuFSNsVFhz5OCn5NmDWU5BC6/nE99af5B2LvIDcEc+fb03km6q5NnNum9r5s2fV6HzbrrAA8O3QPS2xNNX4bUCtpyBuAWrLteW25WUc8c0kzUSuOSAZ2W4/r4dD12n9R5hvmxUWoPiWXL9ESQrDp6ew2sq2NLfNIL45iAcmtS2B72qEOoNvmxUWgPEbnyMhPQWr/VXwrYXxm0ounQpyuFJqZph826qw4NB7TRXftBa40AiNnoLHXR1Hf7KF+UlFynWRSljYwQMe59VqfNuqsJD/+Rt2PQXvXpR0lJwI4Fujz98APQWv528xFBZqWl9SxOOv3ZHd7ZGRbvxWAmrhWw16CnFAfCsZtfCNsAC72+vnG+kp1ATsbq+cb6SnUBmwu71yvkXvTFKhQsVWSE+hNmB3+63u+DbSfEldwO524hvxDROwu534RnzDBOxub4tv/cuD+nh3WkuZgG9R9BTsq/NKkDbYBWt8a3c9lymoIK8yVld4GZoLyfQUpPL9fLhrCWUEPQVPWPnW7touu9uNdKOm1isDb3uT3tatySmYaTWp9BSk8tkled/icqmdegqesF3N7Fkw5SAgvr