Mój post dotyczy tylko SQL Server 2000 i został przetestowany do pracy w moim środowisku.
Ten kod umożliwia dostęp do wszystkich możliwych baz danych jednej instancji , a nie tylko jednej bazy danych.
Korzystam z dwóch tabel tymczasowych, aby zebrać odpowiednie dane, a następnie zrzucam wyniki do jednej tabeli „na żywo”.
Zwracane dane to: nazwa_bazy_danych, nazwa_bazy_danych, wiersze (w tabeli), dane (wydaje się, że rozmiar tabeli w kilobajtach), dane wejściowe (uważam, że jest to przydatne, gdybym wiedział, kiedy ostatnio uruchomiłem skrypt).
Wadą tego kodu jest to, że pole „data” nie jest przechowywane jako int (znaki „KB” są przechowywane w tym polu), a to byłoby przydatne (ale nie całkowicie konieczne) do sortowania.
Mam nadzieję, że ten kod pomaga komuś tam i oszczędza mu trochę czasu!
CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN
SET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)
SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '
INSERT INTO #DatabaseTables(DbName, TableName)
EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR
SELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR
SELECT DBName FROM #DatabaseTables
DECLARE @DBName sysname
OPEN AllDatabaseNames
DECLARE @TName sysname
OPEN AllDatabaseTables
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseNames INTO @DBName
FETCH NEXT FROM AllDatabaseTables INTO @TName
IF @@FETCH_STATUS<>0 BREAK
INSERT INTO #AllDatabaseTableSizes
EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
SELECT [dbname], name, [rows], data FROM #DatabaseTables
INNER JOIN #AllDatabaseTableSizes
ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
GROUP BY [dbname] , name, [rows], data
ORDER BY [dbname]
--To be honest, I have no idea what exact duplicates we are dropping
-- but in my case a near enough approach has been good enough.
DELETE FROM [rsp_DatabaseTableSizes]
WHERE name IN
(
SELECT name
FROM [rsp_DatabaseTableSizes]
GROUP BY name
HAVING COUNT(*) > 1
)
DROP TABLE #DatabaseTables
DROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTables
DEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNames
DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]
W razie potrzeby tabela rsp_DatabaseTableSizes została utworzona poprzez:
CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
