De multe ori, nu te multumesti sa stii cat ocupa o baza de date din SQL Server (chiar! de ce spune ‘ocupa’? Nu se poate zice ‘cantareste’?), ci te intereseaza cat ocupa fiecare tabel in parte.
Pentru a determina marimea tabelelor poti proceda asa. Creezi urmatoarea procedura stocata si apoi o apelezi.
CREATE PROCEDURE GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100) --For storing values in the cursor
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
select [name]
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT *
FROM #TempTable
--Final cleanup!
DROP TABLE #TempTable
GO
Pentru mai multe informatii vezi aici: https://www.mitchelsellers.com/blogs/2007/07/27/determing-sql-server-table-size.aspx
Care ar fi alte modalitati?
PS. Presimt ca astia cu mysql-ul se vor umfla iarasi in pene… :p
Comentarii recente