{"id":254,"date":"2009-03-17T10:25:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=254"},"modified":"2009-03-17T10:25:00","modified_gmt":"1970-01-01T00:00:00","slug":"tablo-boyutlari-ve-satir-sayilari-bulmak","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2009\/03\/17\/tablo-boyutlari-ve-satir-sayilari-bulmak\/","title":{"rendered":"Tablo Boyutlari ve Satir Sayilari Bulmak"},"content":{"rendered":"

Bir SQL Server veritabaniniz mi var. Veritabanindaki tablolarin boyutlarini ve satir sayilarini mi ögrenmeniz gerekiyor. O zaman asagidaki T-SQL kodu çalistirin, zaman kazanin.<\/span><\/p>\n


\n  T-SQL kodunu buradan indirebilirsiniz<\/a><\/span><\/p>\n

 .<\/a><\/p>\n

\n\n \n \n
\n

\/* Tablolar, isimlerine göre siralanir *\/<\/span><\/p>\n

SELECT<\/span> ROW_NUMBER<\/span>()<\/span> OVER <\/span>(<\/span>ORDER<\/span> BY<\/span> TABLE_NAME)<\/span> AS<\/span> TABLE_NO,<\/span> TABLE_NAME,<\/span> TABLE_SCHEMA INTO<\/span> #Tablo FROM<\/span> INFORMATION_SCHEMA<\/span>.<\/span>TABLES<\/span> <\/span><\/p>\n

 <\/span><\/p>\n

GO<\/span><\/p>\n

 <\/span><\/p>\n

\/* Tablo verilerini tutacak geçici tablo yaratilir *\/<\/span><\/p>\n

CREATE<\/span> TABLE<\/span> #TabloBilgileri <\/span>(<\/span>[Tablo Adi] varchar<\/span>(<\/span>128),<\/span> [Satir Sayisi] varchar<\/span>(<\/span>11),<\/span> [Toplam Alan] varchar<\/span>(<\/span>18),<\/span> [Veri Alani] varchar<\/span>(<\/span>18),<\/span> [Index Alani] varchar<\/span>(<\/span>18),<\/span> [Kullanilmayan Alan] varchar<\/span>(<\/span>18))<\/span><\/span><\/p>\n

 <\/span><\/p>\n

GO<\/span><\/p>\n

 <\/span><\/p>\n

\/* Her tablo için “exec sp_spaceused” komutu çaliþtirilir *\/<\/span><\/p>\n

DECLARE<\/span> @sayac int<\/span><\/span><\/p>\n

DECLARE<\/span> @tablosayisi int<\/span><\/span><\/p>\n

DECLARE<\/span> @sql varchar<\/span>(<\/span>MAX<\/span>)<\/span><\/span><\/p>\n

SET<\/span> @tablosayisi =<\/span> <\/span>(<\/span>SELECT<\/span> MAX<\/span>(<\/span>TABLE_NO)<\/span> FROM<\/span> #Tablo)<\/span><\/span><\/p>\n

SET<\/span> @sayac =<\/span> 0<\/span><\/p>\n

WHILE<\/span> @sayac <<\/span> @tablosayisi<\/span><\/p>\n

BEGIN<\/span><\/p>\n

SET<\/span> @sayac =<\/span> @sayac +<\/span> 1<\/span><\/p>\n

SET<\/span> @sql =<\/span> ‘insert into #TabloBilgileri exec sp_spaceused "’<\/span> +<\/span> <\/span>(<\/span>SELECT<\/span> TABLE_SCHEMA FROM<\/span> #Tablo WHERE<\/span> @sayac =<\/span> TABLE_NO)<\/span> +<\/span> ‘.[‘<\/span> +<\/span> <\/span>(<\/span>SELECT<\/span> TABLE_NAME FROM<\/span> #Tablo WHERE<\/span> @sayac =<\/span> TABLE_NO)<\/span> +<\/span> ‘]"’<\/span><\/span><\/p>\n

EXEC<\/span>(<\/span>@sql)<\/span><\/span><\/p>\n

END<\/span><\/p>\n

 <\/span><\/p>\n

\/* KB cinsinden gösterilen tablo boyutlari MB ve GB boyutlarinda gösterilir *\/<\/span><\/p>\n

SELECT<\/span> [Tablo Adi],<\/span> [Satir Sayisi],<\/span> <\/span>(<\/span>CASE<\/span> <\/span><\/p>\n

WHEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Toplam Alan],<\/span>LEN<\/span>(<\/span>[Toplam Alan])-<\/span>3))\/<\/span>1024)<\/span> <<\/span> 1024  <\/span>THEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Toplam Alan],<\/span>LEN<\/span>(<\/span>[Toplam Alan])-<\/span>3))\/<\/span>1024)<\/span> +<\/span> ‘ MB’<\/span><\/span><\/p>\n

ELSE<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>(<\/span>10,<\/span>2),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>,LEFT(<\/span>[Toplam Alan],<\/span>LEN<\/span>(<\/span>[Toplam Alan])-<\/span>3))\/<\/span>1024\/<\/span>1024))<\/span> +<\/span> ‘ GB’<\/span> END<\/span>)<\/span> AS<\/span> [Toplam Alan]<\/span><\/p>\n

,(<\/span>CASE<\/span> <\/span><\/p>\n

WHEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Veri Alani],<\/span>LEN<\/span>(<\/span>[Veri Alani])-<\/span>3))\/<\/span>1024)<\/span> <<\/span> 1024 THEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Veri Alani],<\/span>LEN<\/span>(<\/span>[Veri Alani])-<\/span>3))\/<\/span>1024)<\/span> +<\/span> ‘ MB’<\/span><\/span><\/p>\n

ELSE<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>(<\/span>10,<\/span>2),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>,LEFT(<\/span>[Veri Alani],<\/span>LEN<\/span>(<\/span>[Veri Alani])-<\/span>3))\/<\/span>1024\/<\/span>1024))<\/span> +<\/span> ‘ GB’<\/span> END<\/span>)<\/span> AS<\/span> [Veri Alani]<\/span><\/p>\n

,(<\/span>CASE<\/span> <\/span><\/p>\n

WHEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Index Alani],<\/span>LEN<\/span>(<\/span>[Index Alani])-<\/span>3))\/<\/span>1024)<\/span> <<\/span> 1024  <\/span>THEN<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>bigint<\/span>,LEFT(<\/span>[Index Alani],<\/span>LEN<\/span>(<\/span>[Index Alani])-<\/span>3))\/<\/span>1024)<\/span> +<\/span> ‘ MB’<\/span><\/span><\/p>\n

ELSE<\/span> CONVERT<\/span>(<\/span>varchar<\/span>(<\/span>50),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>(<\/span>10,<\/span>2),<\/span>CONVERT<\/span>(<\/span>decimal<\/span>,LEFT(<\/span>[Index Alani],<\/span>LEN<\/span>(<\/span>[Index Alani])-<\/span>3))\/<\/span>1024\/<\/span>1024))<\/span> +<\/span> ‘ GB’<\/span> END<\/span>)<\/span> AS<\/span> [Index Alani]<\/span><\/p>\n

FROM<\/span> #TabloBilgileri tb<\/span><\/p>\n

 <\/span><\/p>\n

\/* Geçici tablolar silinir *\/<\/span><\/p>\n

 <\/span><\/p>\n

DROP<\/span> TABLE<\/span> #Tablo <\/span><\/p>\n

DROP<\/span> TABLE<\/span> #TabloBilgileri<\/span><\/p>\n <\/td>\n <\/tr>\n <\/tbody>\n<\/table>\n<\/span><\/p>\n

  <\/p>\n

 <\/p>","protected":false},"excerpt":{"rendered":"

Bir SQL Server veritabaniniz mi var. Veritabanindaki tablolarin boyutlarini ve satir sayilarini mi ögrenmeniz gerekiyor. O zaman asagidaki T-SQL kodu çalistirin, zaman kazanin.<\/p>\n","protected":false},"author":552,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[5584,4955,5299,5990,5822,4219,4559],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/254"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/552"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=254"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/254\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}