April 21, 2011 at 10:24 am
Hello Everyone
Hope all is well.
I am trying to write some code to query all user tables to get the name of the table and the row count for that table, for all tables in the database.
Does anyone have anything like that handy?
Thank you in advance
Andrew SQLDBA
April 21, 2011 at 10:25 am
enjoy
SELECT so.name, si.rows FROM sysindexes si
left join sysobjects so on si.id = so.id
WHERE so.type = 'U'
and si.indid < 2
Order by so.name
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 21, 2011 at 10:26 am
Right after posting, I found my copy. I knew that I had that coded already.
Sorry about that, but thank you
Andrew SQLDBA
April 21, 2011 at 12:04 pm
Based on the statistics ... ( are they accurate ? / auto_updateon ? )
how about:
create procedure sp_DBA_RowCount
@TableName varchar(130) = null
, @TableSchema varchar(130)= null
, @MinRows int = 0
-- with encryption
as
begin
/*********************************************************************
get catalog number of rows for all or given user object
***********************************************************************/
set nocount on
declare@dbnamevarchar(32),@OID int,@Composed_Obj_Name varchar(260)
if @TableSchema is null
Begin
select @Composed_Obj_Name = @TableName
End
else
Begin
select @Composed_Obj_Name = @TableSchema+'.'+@TableName
end
/* get the Object_id for the table */
Select @OID = object_id(@Composed_Obj_Name)
set nocount on
if @Composed_Obj_Name is not null
begin
if @OID is null
begin
select @dbname = db_name()
raiserror ('Object "%s" does not exist in database "%s".',
16, 1, @Composed_Obj_Name, @dbname)
end
else
begin
select sum(p.row_count) as Row_Count , s.name + '.' + o.name as TableName
from sys.objects o
inner join sys.schemas s
on (o.schema_id = s.schema_id)
and s.name <> 'sys'
inner join sys.indexes i
on (o.object_id = i.object_id)
and i.index_id in (0,1)
inner join sys.dm_db_partition_stats p
on o.object_id = p.object_id
and p.index_id in (0,1)
where o.object_id = object_id(@Composed_Obj_Name)
group by s.name + '.' + o.name
having sum(p.row_count) >= @MinRows
order by s.name + '.' + o.name
end
end
else
begin
select sum(p.row_count) as Row_Count , s.name + '.' + o.name as TableName
from sys.objects o
inner join sys.schemas s
on (o.schema_id = s.schema_id)
and s.name <> 'sys'
inner join sys.indexes i
on (o.object_id = i.object_id)
and i.index_id in (0,1)
inner join sys.dm_db_partition_stats p
on o.object_id = p.object_id
and p.index_id in (0,1)
group by s.name + '.' + o.name
having sum(p.row_count) >= @MinRows
order by s.name + '.' + o.name
end
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 21, 2011 at 1:48 pm
I can't beleive I forgot this on earlier but here is a script from Michael Valentine Jones that provides a great deal of useful information.
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005
--All credit and thanks for this script go to Michael Valentine Jones
set nocount on
print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'
select
[FileSizeMB]=
convert(numeric(10,2),sum(round(a.size/128.,2))),
[UsedSpaceMB]=
convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
[UnusedSpaceMB]=
convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
[Type] =
case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
[DBFileName]= isnull(a.name,'*** Total for all files ***')
from
sysfiles a
group by
groupid,
a.name
with rollup
having
a.groupid is null or
a.name is not null
order by
case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
a.groupid,
case when a.name is null then 99 else 0 end,
a.name
create table #TABLE_SPACE_WORK
(
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE_USED
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME sysnamenot null ,
TABLE_ROWS numeric(18,0)not null ,
RESERVED varchar(50) not null ,
DATA varchar(50) not null ,
INDEX_SIZE varchar(50) not null ,
UNUSED varchar(50) not null ,
)
create table #TABLE_SPACE
(
Seqintnot null
identity(1,1)primary key clustered,
TABLE_NAME SYSNAME not null ,
TABLE_ROWS int not null ,
RESERVED int not null ,
DATA int not null ,
INDEX_SIZE int not null ,
UNUSED int not null ,
USED_MBnumeric(18,4)not null,
USED_GBnumeric(18,4)not null,
AVERAGE_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_DATA_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_INDEX_BYTES_PER_ROWnumeric(18,5)null,
AVERAGE_UNUSED_BYTES_PER_ROWnumeric(18,5)null,
)
declare @fetch_status int
declare @proc varchar(200)
select@proc= rtrim(db_name())+'.dbo.sp_spaceused'
declare Cur_Cursor cursor local
for
select
TABLE_NAME=
rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE= 'BASE TABLE'
order by
1
open Cur_Cursor
declare @TABLE_NAME varchar(200)
select @fetch_status = 0
while @fetch_status = 0
begin
fetch next from Cur_Cursor
into
@TABLE_NAME
select @fetch_status = @@fetch_status
if @fetch_status <> 0
begin
continue
end
truncate table #TABLE_SPACE_WORK
insert into #TABLE_SPACE_WORK
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
exec @proc @objname =
@TABLE_NAME ,@updateusage = 'true'
-- Needed to work with SQL 7
update #TABLE_SPACE_WORK
set
TABLE_NAME = @TABLE_NAME
insert into #TABLE_SPACE_USED
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED
from
#TABLE_SPACE_WORK
end --While end
close Cur_Cursor
deallocate Cur_Cursor
insert into #TABLE_SPACE
(
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB,
USED_GB,
AVERAGE_BYTES_PER_ROW,
AVERAGE_DATA_BYTES_PER_ROW,
AVERAGE_INDEX_BYTES_PER_ROW,
AVERAGE_UNUSED_BYTES_PER_ROW
)
select
TABLE_NAME,
TABLE_ROWS,
RESERVED,
DATA,
INDEX_SIZE,
UNUSED,
USED_MB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024),4),
USED_GB=
round(convert(numeric(25,10),RESERVED)/
convert(numeric(25,10),1024*1024),4),
AVERAGE_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),RESERVED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_DATA_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),DATA))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_INDEX_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),INDEX_SIZE))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end,
AVERAGE_UNUSED_BYTES_PER_ROW=
case
when TABLE_ROWS <> 0
then round(
(1024.000000*convert(numeric(25,10),UNUSED))/
convert(numeric(25,10),TABLE_ROWS),5)
else null
end
from
(
select
TABLE_NAME,
TABLE_ROWS,
RESERVED=
convert(int,rtrim(replace(RESERVED,'KB',''))),
DATA=
convert(int,rtrim(replace(DATA,'KB',''))),
INDEX_SIZE=
convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
UNUSED=
convert(int,rtrim(replace(UNUSED,'KB','')))
from
#TABLE_SPACE_USED aa
) a
order by
TABLE_NAME
print 'Show results in descending order by size in MB'
select * from #TABLE_SPACE order by USED_MB desc
go
drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED
drop table #TABLE_SPACE
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply