Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Rozmiary tabel MS SQL

775 views
Skip to first unread message

max

unread,
Apr 22, 2010, 2:24:03 AM4/22/10
to
Jak mogę zobaczyć która tabela w bazie MS SQL jest największa i jaki ma
rozmiar ?


GrzybSon

unread,
Apr 22, 2010, 2:50:10 AM4/22/10
to

Użytkownik "max" <m...@max.pl> napisał w wiadomości
news:hqopsn$705$1...@portraits.wsisiz.edu.pl...


Jak mogę zobaczyć która tabela w bazie MS SQL jest największa i jaki ma
rozmiar ?

Poniżej skrypt, który wyświetla listę tabel wraz z informacją o rozmiarze.
Musisz sobie jedynie go zmodyfikować aby wypluł Ci tylko jeden wiersz.


Declare @include_system_tables bit

Set @include_system_tables = 0


SELECT Table_Name

, (SELECT rowcnt FROM sysindexes s WHERE s.indid < 2 AND s.id =
OBJECT_ID(a.Table_Name)) AS Row_Count

, Total_Space_Used_MB

Into #T1

FROM (SELECT QUOTENAME(USER_NAME(o.uid)) + '.' +
QUOTENAME(OBJECT_NAME(i.id)) AS Table_Name

, CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved))

* (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type
= 'E')) / 1024)/1024)) AS Total_Space_Used_MB

FROM sysindexes i (NOLOCK)

INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id

AND ((@include_system_tables = 1 AND o.type IN ('U', 'S')) OR o.type = 'U')

AND (@include_system_tables = 1 OR OBJECTPROPERTY(i.id, 'IsMSShipped') = 0)

WHERE indid IN (0, 1, 255) --AND Total_Space_Used_MB = 0

GROUP BY QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)))
as a

--ORDER BY Total_Space_Used_MB DESC

ORDER BY Row_Count DESC


-- Get the column counts

Select myInner.Table_Name, Count(sc.id) As Number_Columns

Into #T2

From dbo.syscolumns sc

Inner Join

(SELECT QUOTENAME(USER_NAME(o.uid)) + '.' + QUOTENAME(OBJECT_NAME(i.id)) AS
Table_Name, i.id

FROM sysindexes i (NOLOCK)

INNER JOIN sysobjects o (NOLOCK) ON i.id = o.id AND ((o.type IN ('U', 'S'))
OR o.type = 'U')

AND ((OBJECTPROPERTY(i.id, 'IsMSShipped') = 0))

WHERE indid IN (0, 1, 255)) myInner On sc.id = myInner.id

Group By myInner.Table_Name


Select #T1.*, #T2.Number_Columns

From #T1

Inner Join #T2 On #T1.Table_Name = #T2.Table_Name


Drop Table #T1

Drop Table #T2

--

Pozdro Maciek

Krzysztof Jamróz

unread,
Apr 22, 2010, 2:44:21 PM4/22/10
to
Dnia Thu, 22 Apr 2010 08:24:03 +0200, max napisaďż˝(a):

> Jak mog� zobaczy� kt�ra tabela w bazie MS SQL jest najwi�ksza i jaki ma
> rozmiar ?

Je�li wolisz wersj� "klikan�", to Management Studio ma r�ne ciekawe
raporty (dost�pne w menu kontekstowym bazy), m.in. jest tam raport z
tabelami posortowanymi wed�ug rozmiaru.

--
Krzysztof Jamrďż˝

0 new messages