Bidel_barnamenevis
چهارشنبه 29 مهر 1388, 10:18 صبح
سلام،
DECLARE @tblTempSizes TABLE(
[name] varchar(100),
[rows] integer,
[reserved] varchar(100),
[data ]varchar(100),
[index_size] varchar(100),
[unused] varchar(100) )
DECLARE @names TABLE([name] varchar(100))
INSERT INTO @names
SELECT name FROM sys.TABLEs where type = 'u'
DECLARE @counter CURSOR
DECLARE @tempString VARCHAR(100)
SET @counter = CURSOR
FOR
SELECT [name] FROM @names
OPEN @counter
FETCH NEXT FROM @counter INTO @tempString
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblTempSizes exec sp_spaceused @tempString
FETCH NEXT FROM @counter INTO @tempString
END
DEALLOCATE @counter
DECLARE @tblSizes TABLE(
[name] varchar(100),
[rows] integer,
[reserved] integer,
[data] integer,
[index_size] integer,
[unused] integer )
INSERT INTO @tblSizes
SELECT [name],rows,replace(reserved,' KB','') as [reserved(KB)], replace(data,' KB','') as [data(KB)], replace(index_size,' KB','') as [index_size(KB)], replace(unused,' KB','') as [unused(KB)]
FROM @tblTempSizes
SELECT *
FROM @tblSizes
ORDER BY data DESC
ممنون
DECLARE @tblTempSizes TABLE(
[name] varchar(100),
[rows] integer,
[reserved] varchar(100),
[data ]varchar(100),
[index_size] varchar(100),
[unused] varchar(100) )
DECLARE @names TABLE([name] varchar(100))
INSERT INTO @names
SELECT name FROM sys.TABLEs where type = 'u'
DECLARE @counter CURSOR
DECLARE @tempString VARCHAR(100)
SET @counter = CURSOR
FOR
SELECT [name] FROM @names
OPEN @counter
FETCH NEXT FROM @counter INTO @tempString
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblTempSizes exec sp_spaceused @tempString
FETCH NEXT FROM @counter INTO @tempString
END
DEALLOCATE @counter
DECLARE @tblSizes TABLE(
[name] varchar(100),
[rows] integer,
[reserved] integer,
[data] integer,
[index_size] integer,
[unused] integer )
INSERT INTO @tblSizes
SELECT [name],rows,replace(reserved,' KB','') as [reserved(KB)], replace(data,' KB','') as [data(KB)], replace(index_size,' KB','') as [index_size(KB)], replace(unused,' KB','') as [unused(KB)]
FROM @tblTempSizes
SELECT *
FROM @tblSizes
ORDER BY data DESC
ممنون