Retrieve table name, created date, and row count

[stnkyminky]

I'm using the following query to pull the table name and created date for tables in a database. I would also like to display the number of records in each table.

select sysobjects.name, crdate from sysobjects where name like 'tbl%'

Here is the function I wrote but it didn't work....for obvious reasons.
I was hoping to create a function that would return the number of rows so I could display my information inline.

Create FUNCTION fn_rows(@sTable as varchar(8000))
RETURNS int
AS
BEGIN
Declare @RowCount int
	Select @RowCount = count(*) from @sTable
RETURN @RowCount
END

Can anyone provide a creative solution?

Thanks


Scott
Programmer Analyst
<{{><

 

[hmckillop]

select 
	syo.name, 
	syo.crdate, 
	syi.rowcnt
from 
	sysobjects syo 
inner join sysindexes syi on syi.id = syo.id
where 
	syo.name like 'tbl%' 
	and 
	(syi.indid = 1 or syi.indid = 0)

--uses the clustered index or heap index to get the stored rowcnt, will generally always be correct except when open transactions.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[dbomrrsm]

For more info on the table try:

declare @id    int            
declare @type    character(2)         
declare    @pages    int            
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage    dec(15,0)
declare @pagesperMB        dec(15,0)

create table #spt_space
(
    objid        int null,
    rows        int null,
    reserved    dec(15) null,
    data        dec(15) null,
    indexp        dec(15) null,
    unused        dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select    id
from    sysobjects
where    xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

    /* Code from sp_spaceused */
    insert into #spt_space (objid, reserved)
        select objid = @id, sum(reserved)
            from sysindexes
                where indid in (0, 1, 255)
                    and id = @id

    select @pages = sum(dpages)
            from sysindexes
                where indid < 2
                    and id = @id
    select @pages = @pages + isnull(sum(used), 0)
        from sysindexes
            where indid = 255
                and id = @id
    update #spt_space
        set data = @pages
    where objid = @id


    /* index: sum(used) where indid in (0, 1, 255) - data */
    update #spt_space
        set indexp = (select sum(used)
                from sysindexes
                where indid in (0, 1, 255)
                and id = @id)
                - data
        where objid = @id

    /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
    update #spt_space
        set unused = reserved
                - (select sum(used)
                    from sysindexes
                        where indid in (0, 1, 255)
                        and id = @id)
        where objid = @id

    update #spt_space
        set rows = i.rows
            from sysindexes i
                where i.indid < 2
                and i.id = @id
                and objid = @id

    fetch next from c_tables
    into @id
end

select     TableName = (select left(name,60) from sysobjects where id = objid),
    Rows = convert(char(11), rows),
    ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
    DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
    IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
    UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
        
from     #spt_space, master.dbo.spt_values d
where     d.number = 1
and     d.type = 'E'
order by reserved desc 
drop table #spt_space
close c_tables
deallocate c_tables

[blue]DBomrrsm[/blue]

 

[vongrunt]

Here is my script for admin purposes only:

create table #blah
(	name sysname,
	crdate datetime,
	rows char(11),
	reserved varchar(18),
	data varchar(18),
	index_size varchar(18),
	unused varchar(18),
)

exec sp_msForEachTable 
	'insert into #blah (name, rows, reserved, data, index_size, unused) exec sp_spaceused ''?'', true'

update A set crdate = B.crdate from #blah A inner join sysobjects B on A.name=B.name and B.xtype='U'

select * from #blah order by name
drop table #blah