If Exists (Select * from sysobjects where id = object_id('dbo._trace_spGetTableInfo')
and sysstat & 0xf = 4)
Drop Proc dbo._trace_spGetTableInfo
Go
CREATE PROC dbo._trace_spGetTableInfo
/********************************************************************
* Description: Returns number of rows and disk usage for all tables
*
********************************************************************
* Parameter Name Description
* --------------------- ------------------------
* @SortOrder The sort order for the output ('name', 'size')
********************************************************************/
@SortOrder nvarchar(4) = NULL
AS
BEGIN
SET NOCOUNT ON
Declare @nvcTblNm nvarchar(513),
@siUID smallint ,
@nvcUserNm nvarchar(256),
@iObjectID int ,
@iPages int ,
@nvcSortCol nvarchar(255),
@nvcSQL nvarchar(2048)
If @SortOrder IS NOT NULL
Begin
Select @SortOrder = lower(@SortOrder)
If @SortOrder Not In ('name', 'size')
Begin
Raiserror ('Please specify name, size, or NULL for the @SortOrder parameter.', 16, 1)
Return 1
End
Else
Begin
If @SortOrder = 'size'
Begin
Select @nvcSortCol = 'reserved desc'
End
Else
Begin
Select @nvcSortCol = 'name'
End
End
End
Else
Begin
Select @nvcSortCol = 'name'
End
Create Table #TableInfo
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
Create Table #AllTableInfo
(
name nvarchar(64) null,
rows int null,
reserved int null,
data int null,
indexp int null,
unused int null
)
Declare curTables Cursor For
Select uid ,
name
From sysobjects
Where type = 'U'
and name not like '_trace_%'
Order By name
FOR READ ONLY
Open curTables
Fetch Next From curTables
into @siUID, @nvcTblNm
While @@FETCH_STATUS = 0
Begin
-- Remove rows from temp table
Delete From #TableInfo
-- Get the user name
Select @nvcUserNm = user_name(@siUID)
If @nvcUserNm != NULL
Begin
Select @nvcTblNm = @nvcUserNm + '.' + @nvcTblNm
End
-- Get the object id
Select @iObjectID = object_id(@nvcTblNm)
Insert Into #TableInfo (reserved)
Select sum(reserved)
From sysindexes
Where indid in (0, 1, 255)
and id = @iObjectID
Select @iPages = sum(dpages)
From sysindexes
Where indid < 2
and id = @iObjectID
Select @iPages = @iPages + isnull(sum(used), 0)
From sysindexes
Where indid = 255
and id = @iObjectID
Update #TableInfo
Set data = @iPages
Update #TableInfo
Set indexp = (
Select sum(used)
From sysindexes
where indid in (0, 1, 255)
and id = @iObjectID ) - data
Update #TableInfo
Set unused = reserved - (
Select sum(used)
From sysindexes
Where indid in (0, 1, 255)
and id = @iObjectID )
Update #TableInfo
Set rows = i.rows
From sysindexes i
Where i.indid < 2
and i.id = @iObjectID
Insert Into #AllTableInfo
Select name = object_name(@iObjectID) ,
rows = rows,
reserved = round(reserved * d.low / 1024.,0,0),
data = round(data * d.low / 1024.,0,0),
indexp = round(indexp * d.low / 1024.,0,0),
unused = round(unused * d.low / 1024.,0,0)
From #TableInfo, master.dbo.spt_values d
Where d.number = 1
and d.type = 'E'
Fetch Next From curTables
into @siUID, @nvcTblNm
End
Select @nvcSQL = N'
Select [Name] = name,
[Rows] = rows,
[Reserved (KB)] = reserved,
[Total Used (KB)] = data + indexp,
[Data (KB)] = data,
[Index Size (KB)] = indexp,
[Unused (KB)] = unused
From #AllTableInfo
Order By ' + @nvcSortCol
Exec sp_executesql @nvcSQL
Close curTables
Deallocate curTables
Drop Table #TableInfo
Drop Table #AllTableInfo
Return 0
END
Comentarios sobre la versión: Versión 1 (7)
formulario
Felicidades y tu código esta excelente