{"id":2483,"date":"2015-12-01T17:22:07","date_gmt":"2015-12-01T09:22:07","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=2483"},"modified":"2015-12-01T17:23:57","modified_gmt":"2015-12-01T09:23:57","slug":"sp_msforeachtable%e4%bd%bf%e7%94%a8%e8%aa%aa%e6%98%8e","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2015\/12\/01\/sp_msforeachtable%e4%bd%bf%e7%94%a8%e8%aa%aa%e6%98%8e\/","title":{"rendered":"sp_MsForEachTable\u4f7f\u7528\u8aaa\u660e"},"content":{"rendered":"<p>sp_MsForEachTable\u9019\u6307\u4ee4\u662f\u5fae\u8edf\u6c92\u6709\u8a18\u9304\u5728\u516c\u958b\u6587\u4ef6\u7684\u9810\u5132\u7a0b\u5e8f\uff0c\u5728Master\u8cc7\u6599\u5eab\u4e2d\u7684\u7cfb\u7d71\u9810\u5132\u7a0b\u5e8f\u4e2d\u53ef\u4ee5\u627e\u5230\u9019\u500b\u6307\u4ee4\uff0c\u900f\u904e\u9019\u6307\u4ee4\u53ef\u4ee5\u8f15\u9b06\u53d6\u5f97\u6bcf\u4e00\u8cc7\u6599\u8868\u7684\u8a73\u7d30\u8cc7\u8a0a\uff0c\u4e5f\u53ef\u900f\u904e\u9019\u500b\u6307\u4ee4\u57f7\u884c\u8cc7\u6599\u8868\u7684\u7dad\u8b77\u4f5c\u696d\u3002<\/p>\n<p>\u65bc\u76ee\u524d\u5e38\u898bSQL Server \u7248\u672c\u4e2d\uff0c\u900f\u904e\u4e0b\u5217\u6307\u4ee4\u53ef\u4ee5\u53d6\u5f97\u4f7f\u7528\u8005\u81ea\u8a02\u8cc7\u6599\u8868\u7684\u76f8\u95dc\u8a0a\u606f\u3002<\/p>\n<pre class=\"lang:tsql decode:true \">--SQL2000\u3001SQL2005\u3001SQL2008\u3001SQL2008R2\u9069\u7528\r\n\r\nEXEC sp_spaceused 'Production.ProductProductPhoto'\r\n\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072665\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"sp_spaceused-v2008r2.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464680-2354004135.png?w=712\" alt=\"sp_spaceused-v2008r2.png\" border=\"0\" \/><\/a><\/p>\n<p>\u5982\u679c\u9700\u8981\u5217\u51fa\u8cc7\u6599\u5eab\u4e2d\u6240\u6709\u8cc7\u6599\u8868\u7684\u8a0a\u606f\uff0c\u5c31\u5fc5\u9808\u65bc\u4e0a\u5217\u6307\u4ee4\u4e00\u4e00\u6307\u5b9a\u6bcf\u500b\u8cc7\u6599\u8868\uff0c\u800c\u7121\u6cd5\u5728\u55ae\u6b21\u67e5\u8a62\u4e2d\u5c31\u53d6\u5f97\u6240\u6709\u8a0a\u606f\uff0c\u900f\u904esp_MsForEachTable\u6307\u4ee4\u53ef\u4ee5\u5e6b\u52a9\u4f60\u5c07\u6bcf\u500b\u8cc7\u6599\u5eab\u4e2d\u7684\u8cc7\u6599\u8868\u7684\u8a0a\u606f\u5f59\u7e3d\u8d77\u4f86\u3002<\/p>\n<p>&nbsp;<\/p>\n<p>\u57f7\u884csp_MsForEachTable\u5fc5\u9808\u63d0\u4f9b\u53c3\u6578\uff0c\u82e5\u672a\u63d0\u4f9b\u932f\u8aa4\u8a0a\u606f\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<p>\u5728SQL Server 2000\u4e2d\u57f7\u884csp_MsForEachTable\u672a\u63d0\u4f9b\u53c3\u6578\u7684\u932f\u8aa4\u8a0a\u606f<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072655\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"sp_MsForEachTable_error-v2000.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464663-3007048354.png?w=712\" alt=\"sp_MsForEachTable_error-v2000.png\" border=\"0\" \/><\/a><\/p>\n<p>\u5728SQL Server 2008R2\u4e2d\u57f7\u884csp_MsForEachTable\u672a\u63d0\u4f9b\u53c3\u6578\u7684\u932f\u8aa4\u8a0a\u606f<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072659\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"sp_MsForEachTable_error-v2008r2.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464667-1376827197_n.png?w=712\" alt=\"sp_MsForEachTable_error-v2008r2.png\" border=\"0\" \/><\/a><\/p>\n<p>\u63a5\u8457\u501f\u7531sp_MsForEachTable\u6307\u4ee4\u5e6b\u52a9\u6211\u5011\u57f7\u884c\u6307\u4ee4sp_spaceused\uff0c<\/p>\n<p>\u5b8c\u6574\u6307\u4ee4\u5982\u4e0b\uff1a<\/p>\n<pre class=\"lang:tsql decode:true \">--SQL2000\u3001SQL2005\u3001SQL2008\u3001SQL2008R2\u9069\u7528\r\n\r\nEXEC sp_MsForEachTable \"Sp_SpaceUsed '?'\"\r\n\r\nGO\r\n\r\n--\u6216\r\n\r\nEXEC sp_MsForEachTable \"Sp_SpaceUsed [?]\"\r\n\r\nGO\r\n\r\n--\u6216\r\n\r\nEXEC sp_MsForEachTable 'EXEC Sp_SpaceUsed [?]'\r\n\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072661\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"sp_spaceused-AdventureWorks-v2008r2.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464677-752156174.png?w=712\" alt=\"sp_spaceused-AdventureWorks-v2008r2.png\" border=\"0\" \/><\/a><\/p>\n<p>\u63a5\u8457\u6211\u5011\u518d\u4f86\u5217\u51fa\u55ae\u4e00\u8cc7\u6599\u5eab\u5167\u6240\u6709\u8cc7\u6599\u8868\u8cc7\u6599\u5217\u7b46\u6578<\/p>\n<pre class=\"lang:tsql decode:true \">-- SQL2000\u3001SQL2005\u3001SQL2008\u3001SQL2008R2\u9069\u7528\r\n\r\nUSE AdventureWorks\r\n\r\nGO\r\n\r\nEXEC sp_MsForEachTable \"SELECT '?' AS 'TableName',COUNT(*) AS 'RowCount' FROM ?\"\r\n\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072653\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"\u986f\u793a\u6bcf\u500b\u8cc7\u6599\u8868\u5217\u6578-v2008r2.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464660-408247135_n.png?w=712\" alt=\"\u986f\u793a\u6bcf\u500b\u8cc7\u6599\u8868\u5217\u6578-v2008r2.png\" border=\"0\" \/><\/a><\/p>\n<p>\u66f4\u9032\u968e\u7684\u61c9\u7528\u662f\u6211\u5011\u53ef\u4ee5\u628aSP_SPACEUSED\u7522\u751f\u7684\u8cc7\u6599\u5f59\u7e3d\u5132\u5b58\u5230\u4e00\u500b\u8cc7\u6599\u8868\uff0c<\/p>\n<p>\u7bc4\u4f8b\u5982\u4e0b:<\/p>\n<pre class=\"lang:tsql decode:true \">USE AdventureWorks\r\n\r\nGO\r\n\r\nCREATE TABLE #MyTblInfo\r\n\r\n(\r\n\r\n[name] nvarchar(256),--\u8cc7\u6599\u8868\u540d\u7a31\r\n\r\n[rows] int,--\u73fe\u6709\u8cc7\u6599\u5217\u6578\u91cf\r\n\r\n[reserved] varchar(18),--\u8cc7\u6599\u8868\u78c1\u789f\u4fdd\u7559\u7a7a\u9593\u5927\u5c0f\r\n\r\n[reserved_int] int default(0),--\u8cc7\u6599\u8868\u78c1\u789f\u4fdd\u7559\u7a7a\u9593\u5927\u5c0f\u6574\u6578\u503c\r\n\r\n[data] varchar(18),--\u8cc7\u6599\u8868\u5be6\u9ad4\u8cc7\u6599\u4f7f\u7528\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\r\n\r\n[data_int] int default(0),--\u8cc7\u6599\u8868\u5be6\u9ad4\u8cc7\u6599\u4f7f\u7528\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\u6574\u6578\u503c\r\n\r\n[index_size] varchar(18),--\u8cc7\u6599\u8868\u7d22\u5f15\u4f7f\u7528\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\r\n\r\n[index_size_int] int default(0),--\u8cc7\u6599\u8868\u7d22\u5f15\u4f7f\u7528\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\u6574\u6578\u503c\r\n\r\n[unused] varchar(18),--\u4fdd\u7559\u7d66\u8cc7\u6599\u8868\u672a\u4f7f\u7528\u7684\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\r\n\r\n[unused_int] int default(0)--\u4fdd\u7559\u7d66\u8cc7\u6599\u8868\u672a\u4f7f\u7528\u7684\u78c1\u789f\u7a7a\u9593\u5927\u5c0f\u6574\u6578\u503c\r\n\r\n)\r\n\r\nGO\r\n\r\n--\u5c07\u7522\u751f\u7684\u8cc7\u6599\u5beb\u5165\u66ab\u5b58\u8cc7\u6599\u8868#MyTblInfo\r\n\r\nEXEC sp_MSforeachtable \"INSERT INTO #MyTblInfo ([name],[rows],[reserved],[data],[index_size],[unused]) EXEC sp_spaceused '?'\"\r\n\r\nGO\r\n\r\n--\u5f9e[reserved]\u3001[data]\u3001[index_size]\u3001[unused]\u53d6\u51fa\u6578\u5b57\u4e26\u66f4\u65b0\u5230\u5c0d\u61c9\u7684\u6574\u6578\u503c\u6b04\u4f4d\r\n\r\nUPDATE #MyTblInfo\r\n\r\nSET [reserved_int] = CAST(SUBSTRING([reserved], 1, CHARINDEX(' ', [reserved])) AS int),\r\n\r\n[data_int] = CAST(SUBSTRING([data], 1, CHARINDEX(' ', [data])) AS int),\r\n\r\n[index_size_int] = CAST(SUBSTRING([index_size], 1, CHARINDEX(' ', [index_size])) AS int),\r\n\r\n[unused_int] = CAST(SUBSTRING([unused], 1, CHARINDEX(' ', [unused])) AS int)\r\n\r\nGO\r\n\r\n--\u986f\u793a\u8cc7\u6599\r\n\r\nSELECT [name],[rows],[reserved],[reserved_int],\r\n\r\n[data],[data_int],[index_size],[index_size_int],\r\n\r\n[unused],[unused_int],\r\n\r\nCAST(([reserved_int] + [data_int] + [index_size_int] + [unused_int]) \/1024.0 AS VARCHAR) + ' MB' AS 'TABLESIZE',\r\n\r\n([reserved_int] + [data_int] + [index_size_int] + [unused_int]) \/1024.0 AS 'TABLESIZE_FLOAT'\r\n\r\nFROM #MyTblInfo\r\n\r\nORDER BY data_int DESC\r\n\r\nGO\r\n\r\n--\/\/TABLE SIZE\r\n\r\nSELECT (CAST(SUM(data_int)\/1024.0 AS VARCHAR) + ' MB') AS 'DATA_FILE_SIZE',\r\n\r\n(CAST(SUM(index_size_int)\/1024.0 AS VARCHAR) + ' MB') AS 'INDEX_FILE_SIZE',\r\n\r\n(CAST(SUM(unused_int)\/1024.0 AS VARCHAR) + ' MB') AS 'UNUSED_FILE_SIZE',\r\n\r\n(CAST((SUM(data_int) + SUM(index_size_int) + SUM(unused_int))\/1024.0 AS VARCHAR) + ' MB')AS 'TOTAL_FILE_SIZE'\r\n\r\nFROM #MyTblInfo\r\n\r\nGO\r\n\r\nDROP TABLE #MyTblInfo\r\n\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/kirby86a.pixnet.net\/album\/photo\/169072649\"><img data-recalc-dims=\"1\" decoding=\"async\" title=\"\u5f59\u7e3d\u6bcf\u500b\u8cc7\u6599\u8868\u5217\u6578-v2008r2.png\" src=\"https:\/\/i0.wp.com\/pic.pimg.tw\/kirby86a\/1327464642-3200829788_n.png?w=712\" alt=\"\u5f59\u7e3d\u6bcf\u500b\u8cc7\u6599\u8868\u5217\u6578-v2008r2.png\" border=\"0\" \/><\/a><\/p>\n<p>\u53c3\u8003\u8cc7\u6599:<\/p>\n<p>SQL Server Undocumented Stored Procedures sp_MSforeachtable and sp_MSforeachdb<\/p>\n<p>By Gregory A. Larsen<\/p>\n<p><a title=\"http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3441031\/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm\" href=\"http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3441031\/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm\" target=\"_blank\">http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3441031\/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>sp_MsForEachTable\u9019\u6307\u4ee4\u662f\u5fae\u8edf\u6c92\u6709\u8a18\u9304\u5728\u516c\u958b\u6587\u4ef6\u7684\u9810\u5132\u7a0b\u5e8f\uff0c\u5728Master\u8cc7\u6599\u5eab\u4e2d\u7684\u7cfb\u7d71\u9810\u5132\u7a0b &hellip; <a href=\"https:\/\/jason695.why3s.tw\/wordpress\/2015\/12\/01\/sp_msforeachtable%e4%bd%bf%e7%94%a8%e8%aa%aa%e6%98%8e\/\" class=\"more-link\">\u95b1\u8b80\u5168\u6587 <span class=\"screen-reader-text\">sp_MsForEachTable\u4f7f\u7528\u8aaa\u660e<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-2483","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=2483"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2483\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=2483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=2483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=2483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}