Lista tabel w bazie danych

Poniższy skrypt zwraca listę tabel w bazie danych posortowanych od największej (zawierającej najwięcej wierszy).


SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages)* 8 AS TotalSpaceKB,
SUM(a.used_pages)* 8 AS UsedSpaceKB,
(SUM(a.total_pages)-SUM(a.used_pages))* 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id= a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, p.Rows
ORDER BY
p.rows desc

TableName			RowCounts            TotalSpaceKB         UsedSpaceKB          UnusedSpaceKB
----------------------------------------------------------------------------------------------------------------------
SalesOrderDetail		121317               15816                15776                40
TransactionHistory		113443               10128                10016                112
TransactionHistoryArchive	89253                7904                 7840                 64
WorkOrder			72591                6168                 6104                 64
WorkOrderRouting		67131                6720                 6712                 8
SalesOrderHeader		31465                8232                 8168                 64
SalesOrderHeaderSalesReason	7647                720                  704                  16
BusinessEntity			20777                1424                 1336                 88
EmailAddress			19972                3600                 3496                 104
Password			19972                1928                 1896                 32
Person				19972                32032                32016                16
PersonPhone			19972                2256                 2176                 80
Customer			19820                2464                 2296                 168

(13 rows affected)

Ten wpis został opublikowany w kategorii Microsoft SQL. Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz