Table Info - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Table Info

Author

Topic

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-19 : 18:01:49

In SQL Server 2000 Enterprise Manager, if you right-click on a database name and choose View Taskpad you can look at Table Info.
This lists the tables and indexes together with number of rows and size.

Is it possible to get either a table or a list of this information for each database on our server?

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-19 : 18:55:47

There is a system table that is part of every database named sysindexes. You should be able to query it to get the information shown in the taskpad. Any row with an indid of 0 is a heap (table without a clustered index). Any row with an indid of 1 is a clustered index (the table). All of the other rows are non-clustered indexes with the exception of indid = 255 which is a collection of pages for text, ntext, and image data. You will want to join the sysindexes table with the sysobjects table to get the names and other descriptive stuff. This query should get you started -- just add in whatever other columns from sysindexes that you want.

SELECT so.name AS tablename, si.name AS indexname, si.indid
FROM sysindexes si
INNER JOIN sysobjects so
ON si.id = so.id

Edited by - spyder on 02/19/2002 18:57:54

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-02-21 : 16:31:34

Thanks Spyder.

I've taken the query you gave me and created the following procedure which goes through each database
and outputs database name ,table names , row counts and column counts into a table which I then export
using DTS to an Excel spreadsheet.

create procedure spd_table_summary

as

declare @db_no integer
declare @i integer
declare @j integer
declare @db_name varchar(100)
declare @SQLStr nvarchar(1000)

select @db_no = (select count(*) from master..sysdatabases)

select @i = 1
select @j = (select min(dbid) from master..sysdatabases)

while @i <= @db_no
begin

select @db_name = (select name from master..sysdatabases where dbid = @j)

Select @SQLStr = N'insert into temp_table select @db_name1, so.name , si.rowcnt, co.col_count'
Select @SQLStr = @SQLSTR + N' from @db_name..sysindexes si'
Select @SQLStr = @SQLSTR + N' inner join @db_name..sysobjects so'
Select @SQLStr = @SQLSTR + N' on si.id=so.id'
Select @SQLStr = @SQLSTR + N' inner join (select id, count(*) as col_count from @db_name..syscolumns group by id) as co'
Select @SQLStr = @SQLSTR + N' on so.id=co.id'
Select @SQLStr = @SQLSTR + N' where so.xtype = ''U'''
Select @SQLStr = @SQLSTR + N' and si.indid in (0,1)'
Select @SQLStr = @SQLSTR + N' order by so.name'

Select @SQLSTR = replace (@SQLSTR,'@db_name1',''''+@db_name+'''')
Select @SQLSTR = replace (@SQLSTR,'@db_name',@db_name)
print @SQLSTR

exec sp_executesql @sqlstr

select @i = @i + 1
select @j = (select min(dbid) from master..sysdatabases where dbid > @j)
end

go

The data on our server is being moved and so I intend to run this procedure before the data is moved and again after
the data is moved to check that no tables, rows, columns have been lost.

What other checks can I do to ensure that nothing has gone wrong ??

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-22 : 01:40:28

quote:

The data on our server is being moved and so I intend to run this procedure before the data is moved and again after
the data is moved to check that no tables, rows, columns have been lost.

What other checks can I do to ensure that nothing has gone wrong ??

Given that you are wanting this information for doing a migration, you ought to either run DBCC UPDATEUSAGE or run actual 'SELECT COUNT(*)' rowcounts for each of your tables. This is because the row count information in the sysindexes table is only accurate as of the last time usage statistics were updated. If you have auto-update stats turned on then you MIGHT be OK but I wouldn't want to leave anything to chance!

Edited by - spyder on 02/22/2002 01:41:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources