Tuesday, October 12, 2010

Sysindex ( How Do I Display List of Rows In Tables In A Database)

Sysindexes table is an interesting table which stores important metadata.

Need to have a detail look into it.

By the way here is some code which uses sysindex to display
number of columns in all tables in a database :


CREATE PROCEDURE [dbo].[DisplayRowCount] as
BEGIN
declare @tabcnt int
declare @printline char (60)
select @tabcnt = count (*) from sysobjects where type = 'U'


If @tabcnt != 0
BEGIN
select "TABLE NAME"= convert (varchar (50), o.name), ROWS=i.rows
from sysobjects o, sysindexes i
where o.type = 'U'
and o.id = i.id
and i.indid in (0,1)
order by o.name
END

select @printline = '(' + convert (varchar(10), @tabcnt) +
' tables in ' + DB_NAME() + ')'

print @printline

END

No comments:

Post a Comment