Determine Size of a Table in SQL Server Določite Velikost tabele v SQL Server
Ever wonder how big a table really is in your database? Kdaj se sprašujem, kako velik je tabela res v zbirko podatkov? You know there are a million rows in the table, but how much space is that really taking? Veste, obstaja milijonov vrstic v tabeli, vendar pa, koliko prostora je, da je res ob?
SQL Server provides a built-in stored procedure that you can run to easily show the size of a table, including the size of the indexes… which might surprise you. SQL Server ponuja vgrajeno v shranjeno proceduro, da boste lahko zagnali z lahkoto dokazati, velikost tabele, vključno z velikostjo ... indeksov, ki bi lahko presenetiti.
Syntax: Sintaksa:
sp_spaceused 'Tablename' sp_spaceused "TABLENAME"
Here's an example of it in action: Tukaj je primer, da v akciji:
Highly useful stuff. Zelo koristne stvari. It's always interesting to see that the indexes are bigger than the actual data. Vedno je zanimivo videti, da so indeksi večje od dejanskih podatkov.
Daily Email Updates Dnevni Email Updates
You can get our how-to articles in your inbox each day for free. Lahko dobite našo kako do člankov v vašo mapo »Prejeto vsak dan brezplačno. Just enter your name and email below: Preprosto vpišite vaše ime in e-pošto spodaj:
While this is a useful command for determining the space allocated to a table, keep in mind that sp_spaceused references the sysindexes system table to get the space attributes and sometimes what's stored there can get be out of synch with reality. Čeprav je to koristno ukaz za določanje prostora, dodeljena tabelo, imejte v mislih, da je sklicevanje sp_spaceused tabelo sistem sysindexes dobiti prostor atributov in včasih kaj tam shranjene moči zaslužiti se iz Harmonizovati z realnostjo. If you see space numbers that are seemingly out of whack (especially for the 'unused' value), run a “DBCC UPDATEUSAGE” command against the table in question before running sp_spacesued (or against the entire database if you have time and aren't worried about stepping on anyone's toes). Če vidite prostora številke, ki so na videz od čudno (predvsem za "neizkoriščena" vrednost), ki delujejo "DBCC UPDATEUSAGE" zapoved proti tabelo tudi pred vožnjo sp_spacesued (ali proti celotni zbirki podatkov, če imate čas in se ne skrbelo za krepitev kdorkoli na prstih).
This can automatically be done as an optional parameter within sp_spaceused… To je mogoče storiti samodejno kot izbirni parameter v sp_spaceused ...
eg sp_spaceused 'Orders',true npr sp_spaceused "Orders", res
…or as separate commands as shown below. ... Ali kot samostojne ukaze, kot je prikazano spodaj.
To update usage stats for the entire current database, the syntax is: Za posodobitev uporabo statistika za celotno trenutno zbirko podatkov, sintaksa je:
DBCC UPDATEUSAGE (0) DBCC UPDATEUSAGE (0)
For a specific, named database: Za posebne, imenovan zbirke:
DBCC UPDATEUSAGE ('InsertdDbNameHere') DBCC UPDATEUSAGE ( 'InsertdDbNameHere')
For a specific table: Za posebno tabelo:
DBCC UPDATEUSAGE ('InsertdDbNameHere','InsertdTableNameHere') DBCC UPDATEUSAGE ( 'InsertdDbNameHere', 'InsertdTableNameHere')
You can always check BOL for all the details on DBCC UPDATEUSAGE Vedno lahko preverite BOL za vse podrobnosti o DBCC UPDATEUSAGE
Thank you, using query analyer and running procedure sp_spaceused is helpful, however, what if your database has close too 100 tables. Hvala, z uporabo poizvedbe analyer in teče postopek sp_spaceused je koristno, vendar, kaj če je vaša zbirka podatkov je tudi blizu 100 tabel. Anyway to return the entire database size? Kakorkoli že, da se vrnete celotno velikost baze podatkov?
I just found this, it returns spaceused for each table: Pravkar sem našel to, vrne spaceused za vsako preglednico:
EXEC sp_MSforeachtable @command1=”EXEC sp_spaceused '?'” EXEC sp_MSforeachtable @ Command1 = "EXEC sp_spaceused '?"
Charles sp_helpdb Charles sp_helpdb
Rick – that is one of the most useful sql server commands of all time. Rick - da je eden od najbolj uporabnih ukazov sql server vseh časov. You are the greatest. Ti si največji.
I tried to use Ricks Tipp, but having 181 Tables in the Database didnt do it much good. Poskusil sem uporabiti Ricks Tipp, vendar ob 181 tabel v didnt Database naredil veliko dobrega. Also, his Mechanic with sp_spaceused treated each Table as a standalone query, wich made it impossible to use in an automated Growth-of-Database Statistic. Tudi sp_spaceused njegov mehanik z vsako tabelo obravnava kot samostojno vprašanje, ki je ni mogoče uporabiti v avtomatsko rasti-of-Statistic Database.
I stole/borrowed the logic of sp_spaceused and applied it to the following VIEW (wich I now add to a Statistics Table, wich can then be queried in several ways): I ukradel / sposodil logiko sp_spaceused in se uporablja, da bi naslednje VIEW (ki sem ga zdaj dodate v statistiko tabelo, ki se nato lahko poizveduje na več načinov)
SELECT SELECT
TableName, TABLENAME,
NumRows, NumRows,
reservedpages *8192/1024 as TotalSpace, reservedpages * 8192/1024 kot TotalSpace,
pages * 8192/1024 as DataSpace, * 8192/1024 straneh kot DataSpace,
(usedpages-pages)*8192/1024 as IndexSpace, (usedpages-strani) * 8192/1024, kot IndexSpace,
(reservedpages-usedpages)*8192/1024 as UnusedSpace (reservedpages-usedpages) * 8192/1024, kot UnusedSpace
FROM (SELECT FROM (SELECT
t.[name] as tablename, t. [ime], kakor je TABLENAME,
avg([rows]) as NumRows, avg ([vrstice]) kot NumRows,
sum(total_pages) as reservedpages, vsota (total_pages) kot reservedpages,
sum(used_pages) as usedpages, vsota (used_pages) kot usedpages,
sum( vsota (
CASE CASE
When it.internal_type IN (202,204) Then 0 Ko it.internal_type IN (202.204) Potem 0
When a.type 1 Then a.used_pages Ko a.type 1 Potem a.used_pages
When p.index_id Ko p.index_id
Bah, it got cut off, here is the rest: Bah, got to odrezan, tukaj je ostalo:
When p.index_id Ko p.index_id
When p.index_id Ko p.index_id
(smaller than sign – why doesnt it take it here?) 2 Then a.data_pages (manjši od znaka - zakaj doesnt traja tu?) 2 Potem a.data_pages
Else 0 Else 0
END) as pages END) kot strani
from sys.allocation_units as a Join sys.partitions as p on p.partition_id = a.container_id od sys.allocation_units kot sys.partitions Pridružite se nam kot na p.partition_id p = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id levo pridruži sys.internal_tables ga p.object_id = it.object_id
JOIN sys.tables as t on p.object_id=t.object_id Pridružite se sys.tables kot na p.object_id t = t.object_id
–WHERE t.name='mittra' -WHERE t.name = 'mittra'
group by t.[name]) as subselect Skupina, ki jih t. [ime]) kot subselect