SET NOCOUNT ON
IF EXISTS (SELECT OBJECT_ID('tempdb..#Tablo')) DROP TABLE #Tablo
IF EXISTS (SELECT OBJECT_ID('tempdb..#TabloBilgileri')) DROP TABLE #TabloBilgileri
SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS TABLE_NO, TABLE_NAME, TABLE_SCHEMA INTO #Tablo FROM INFORMATION_SCHEMA.TABLES
GO
CREATE TABLE #TabloBilgileri ([Tablo Adi] varchar(128), [Satir Sayisi] INT, [Toplam Alan] varchar(18), [Veri Alani] varchar(18), [Index Alani] varchar(18), [Kullanilmayan Alan] varchar(18))
GO
DECLARE @sayac int
DECLARE @tablosayisi int
DECLARE @sql varchar(MAX)
SET @tablosayisi = (SELECT MAX(TABLE_NO) FROM #Tablo)
SET @sayac = 0
WHILE @sayac < @tablosayisi
BEGIN
SET @sayac = @sayac + 1
SET @sql ='insert into #TabloBilgileri exec sp_spaceused "' + (SELECT TABLE_SCHEMA FROM #Tablo WHERE @sayac = TABLE_NO) + '.[' + (SELECT TABLE_NAME FROM #Tablo WHERE @sayac = TABLE_NO) + ']"'
EXEC(@sql)
END
SELECT * FROM (
SELECT [Tablo Adi], [Satir Sayisi], (CASE
WHEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Toplam Alan],LEN([Toplam Alan])-3))/1024) < 1024 THEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Toplam Alan],LEN([Toplam Alan])-3))/1024) + ' MB'
ELSE CONVERT(varchar(50),CONVERT(decimal(10,2),CONVERT(decimal,LEFT([Toplam Alan],LEN([Toplam Alan])-3))/1024/1024)) + ' GB' END) AS [Toplam Alan]
,(CASE
WHEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Veri Alani],LEN([Veri Alani])-3))/1024) < 1024 THEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Veri Alani],LEN([Veri Alani])-3))/1024) + 'MB'
ELSE CONVERT(varchar(50),CONVERT(decimal(10,2),CONVERT(decimal,LEFT([Veri Alani],LEN([Veri Alani])-3))/1024/1024)) + ' GB' END) AS [Veri Alani]
,(CASE
WHEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Index Alani],LEN([Index Alani])-3))/1024) < 1024 THEN CONVERT(varchar(50),CONVERT(bigint,LEFT([Index Alani],LEN([Index Alani])-3))/1024) + ' MB'
ELSE CONVERT(varchar(50),CONVERT(decimal(10,2),CONVERT(decimal,LEFT([Index Alani],LEN([Index Alani])-3))/1024/1024)) + ' GB' END) AS [Index Alani]
FROM #TabloBilgileri tb)
AS TMP
ORDER BY 2 DESC
Yorumlar
Yorum Gönder