use master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tisize'), 'IsProcedure') = 1
BEGIN
DROP PROCEDURE dbo.sp_tisize
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tisize'), 'IsProcedure') = 1
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_tisize >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_tisize >>>'
END
go
CREATE PROCEDURE sp_tisize (
@tablename nvarchar(255) = '%',
@indexname nvarchar(128) = '%',
@sizeinkmg char(1) = 'A',
@stripdbccoutput bit = 1,
@orderby varchar(10) = 'name'
)
-- WITH ENCRYPTION
AS
/********************************************************************************/
/* Created By : Umachandar Jayachandran (UC) */
/* Created On : 01 October 1999 */
/* Description: This stored procedure generates a report about the size */
/* of the table, number of rows, index sizes & details. The*/
/* display format for the Size of the tables can also be */
/* specified. The dbcc output messages can be stripped or */
/* left as is. The order by option is present for now. Will*/
/* have to add some functionality here. */
/********************************************************************************/
/* Resources : https://umachandar.com/resources.htm */
/********************************************************************************/
if object_id('tempdb..dbcctableaffinity') is null
create table tempdb..dbcctableaffinity
(Owner varchar(128), "Name" varchar(128), ObjId int,
IndId int, FileGroup int,
IAMField int, IAMPageNo int, ManagedFileId int,
ManagedExtStart int, ExtentsInUse int,
MixedPagesInUse int, SPID int null default(@@SPID))
declare @dbcc_str varchar(500), @obj_id int, @obj_name varchar(257),
@curdb nvarchar(128), @cmdstr varchar(500),
@tempsqlinfile varchar(30), @tempsqloutfile varchar(30)
select @curdb = rtrim(db_name()), @sizeinkmg = upper(@sizeinkmg),
@tempsqlinfile = '%windir%\dbcc_temp.sql',
@tempsqloutfile = '%windir%\dbcc_temp.out',
@orderby = lower(@orderby)
-- If invalid size parameter is passed, default to auto
select @sizeinkmg = 'A' where @sizeinkmg not in ('A', 'K', 'M', 'G')
-- If invalid orderby parameter is passed, default to name
select @orderby = 'name' where @orderby not in ('name', 'size', 'rows')
if @stripdbccoutput = 1
begin
exec master..sp_fileop;1 @tempsqlinfile, 'set nocount on'
set @cmdstr = 'use [' + @curdb + ']'
exec master..sp_fileop;2 @tempsqlinfile, @cmdstr
exec master..sp_fileop;2 @tempsqlinfile, 'go'
end
exec('declare dbcc_cur cursor fast_forward for
select TABLE_SCHEMA + ''.'' + TABLE_NAME,
OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME)
from [' + @curdb + '].INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ''BASE TABLE'' and
OBJECTPROPERTY(OBJECT_ID(table_schema + ''.'' + table_name),
''IsMSShipped'') = 0 and
TABLE_SCHEMA + ''.'' + TABLE_NAME LIKE ''%' + @tablename + '%''')
open dbcc_cur
while(1=1)
begin
fetch next from dbcc_cur into @obj_name, @obj_id
if @@fetch_status < 0 break
select @dbcc_str = case when @stripdbccoutput = 1
then 'insert tempdb..dbcctableaffinity ' +
'(Owner, Name, ObjId, IndId, FileGroup, ' +
'IAMField, IAMPageNo, ManagedFileId, ' +
'ManagedExtStart, ExtentsInUse, ' +
'MixedPagesInUse) ' +
'exec ('''
else ''
end +
'dbcc showtableaffinity(' + str(@obj_id) + ') with tableresults' +
case when @stripdbccoutput = 1 then ''')' else '' end
if @stripdbccoutput = 1
exec master..sp_fileop;2 @tempsqlinfile, @dbcc_str
else
insert tempdb..dbcctableaffinity
(Owner, Name, ObjId, IndId, FileGroup, IAMField, IAMPageNo,
ManagedFileId, ManagedExtStart, ExtentsInUse, MixedPagesInUse)
exec (@dbcc_str)
end
deallocate dbcc_cur
if @stripdbccoutput = 1
begin
set @cmdstr = 'update tempdb..dbcctableaffinity set spid = ' + str(@@spid) +
' where spid = @@spid'
exec master..sp_fileop;2 @tempsqlinfile, @cmdstr
set @cmdstr = 'isql -E -n -i "' + @tempsqlinfile + '" -o "' + @tempsqloutfile + '"'
exec master..xp_cmdshell @cmdstr , 'no_output'
exec master..sp_fileop;3 @tempsqlinfile
exec master..sp_fileop;3 @tempsqloutfile
end
select d."Name" as "Table Name",
r."Rows",
case when d.indid in (-1, 1)
then
str(d.size / (case @sizeinkmg
when 'A' then (case when d.size < 1024 then 1 else 1024.0 end)
when 'K' then 1
when 'M' then 1024.0
when 'G' then 1024.0*1024.0
end), 25, 0) +
(case @sizeinkmg
when 'A' then (case when d.size < 1024 then ' (KB)' else ' (MB)' end)
when 'K' then ' (KB)'
when 'M' then ' (MB)'
when 'G' then ' (GB)'
end)
else ''
end as "Table Size",
coalesce( i."Name" , '(None)' ) as "Index Name",
case when i."Name" is not null then
(case Indexproperty( d.ObjId , i."Name" , 'IsUnique' )
when 1 then 'Unique, '
else ''
end) +
(case Indexproperty( d.ObjId , i."Name" , 'IsClustered')
when 1 then 'Clustered'
else 'Non-Clustered'
end) +
', ' +
('IndexDepth = ' + cast( Indexproperty(d.ObjId, i."Name", 'IndexDepth') as varchar) ) +
', ' +
('FillFactor = ' + cast( Indexproperty(d.ObjId, i."Name", 'IndexFillFactor') as varchar) )
else
'(None)'
end as "Index Type",
case when d.indid not in (-1, 1)
then
str(d.size / (case @sizeinkmg
when 'A' then (case when d.size < 1024 then 1 else 1024.0 end)
when 'K' then 1
when 'M' then 1024.0
when 'G' then 1024.0*1024.0
end), 25, 0) +
(case @sizeinkmg
when 'A' then (case when d.size < 1024 then ' (KB)' else ' (MB)' end)
when 'K' then ' (KB)'
when 'M' then ' (MB)'
when 'G' then ' (GB)'
end)
else ''
end as "Index Size",
d."Size",
d.Objid,
d.Indid
from
(
select d1.spid, d1.objid, d1."name", d1.indid,
((sum(d1.ExtentsInUse) * 64 + sum(d1.MixedPagesInUse) * 8))
from (select spid, objid, owner + '.' + "name" , extentsinuse, mixedpagesinuse,
(case when Indid in (0, 1, 255)
then
(case when objectproperty(objid, 'TableHasClustIndex') = 1
then 1
else -1
end)
else indid
end)
from tempdb..dbcctableaffinity
) as d1 ( spid , objid , "name" , extentsinuse , mixedpagesinuse , indid )
Group By d1.spid , d1."Name" , d1.ObjId, d1.indid
) as d ( spid , objid , "Name" , indid , "Size" )
join sysindexes r
on r."id" = d.ObjId
left join sysindexes i
on i."Id" = d.ObjId And i.IndId = d.IndId
where r.indid < 2 and d.spid = @@spid and coalesce( i."Name", '' ) like '%' + @indexname + '%'
Order By d."Name", d.indid
delete tempdb..dbcctableaffinity where spid = @@spid
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_tisize'), 'IsProcedure') = 1
BEGIN
GRANT EXECUTE ON dbo.sp_tisize To Public
PRINT '<<< CREATED PROCEDURE dbo.sp_tisize >>>'
END
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_tisize >>>'
go
This page was last updated on May 01, 2006 04:28 PM.