Database/Table/Column information

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 2005 Forums

SQL Server Administration (2005)

Database/Table/Column information

Author

Topic

rgjb
Starting Member

19 Posts

Posted - 2007-01-17 : 00:00:09

Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.

Is there any easy way to do this in SQL 2005?

Cheers
Gregg

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 00:58:45

Yes, this has been asked many times here on SQLTeam.
Do a search for INFORMATION_SCHEMA.COLUMNS.

Peter Larsson
Helsingborg, Sweden

rgjb
Starting Member

19 Posts

Posted - 2007-01-17 : 16:29:20

Peter,

Thanks for your response & I have done the search as you sugested but I am unable to find any past request of this nature.

I assume however, that I'll need to write a procedure that:

1. Retrieves the databases
select * from sys.databases

2. Retreives the names of the tables of each database
select * from sys.tables

3. Does a Select count(*) for each of tables.

Unlike Oracle which apparently has a table that already stores this information?

Cheers
Gregg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-17 : 16:34:25

For 2, I'd use this instead:

SELECT * FROM INFORMATION_SCHEMA.TABLES

For 3, you can get an estimate from sysindexes if you've got a clustered index on the tables. If DBCC UPDATEUSAGE is run first or recently, then the estimate is pretty accurate.

COUNT(*) is an expensive operation in regards to performance.

Tara Kizer

rgjb
Starting Member

19 Posts

Posted - 2007-01-17 : 16:56:02

Tara,

Many thanks for your response. Though what's the link though between INFORMATION_SCHEMA.TABLES & the sysindexes table?

Cheers
Gregg

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-17 : 17:03:04

You'd probably have to go from sysindexes to sysobjects (via id) to INFORMATION_SCHEMA.TABLES (via object name). I'm using SQL Server 2000 terms as I'm too lazy to open up 2005 right now.

Tara Kizer

rgjb
Starting Member

19 Posts

Posted - 2007-01-18 : 16:46:43

Tara,

Sorry for the delay getting back to you. I appreciate your response & I'll let you know how it goes.

Gregg

rgjb
Starting Member

19 Posts

Posted - 2007-01-21 : 20:29:58

Hi everybody,

One of the developers at work, came up with this solution which works on 2000 as well.

Thanks everyone for your input:

create table #tblres ( dbname sysname, schemaname sysname, tablename sysname, rcount int )
create table #ts (tname sysname, schname sysname)
declare cdbs cursor local for
select name, cmptlevel from master..sysdatabases where dbid > 4 and (status & 512) = 0
declare @dbname sysname
declare @lvl int
open cdbs
fetch next from cdbs into @dbname, @lvl
while @@fetch_status = 0
begin
set @dbname = rtrim(@dbname)
if( @lvl <= 80 )
insert #ts(tname, schname) exec ('select name, user_name(uid) from [' + @dbname + '].dbo.sysobjects where type = ''U'' ')
else
insert #ts(tname, schname) exec ('select t.name, s.name from [' + @dbname + '].sys.tables t, [' + @dbname + '].sys.schemas s where s.schema_id = t.schema_id')
declare ctn cursor local for select schname, tname from #ts
open ctn
declare @sch sysname
declare @tn sysname
fetch next from ctn into @sch, @tn
while @@fetch_status = 0
begin
set @sch = rtrim(@sch)
set @tn=rtrim(@tn)
exec('insert into #tblres( dbname, schemaname, tablename, rcount ) select ''' + @dbname + ''', ''' + @sch + ''', ''' + @tn +
''', (select count(*) from [' + @dbname + '].[' + @sch + '].[' + @tn + '])')
fetch next from ctn into @sch, @tn
end
close ctn
deallocate ctn
truncate table #ts
fetch next from cdbs into @dbname, @lvl
end
close cdbs
deallocate cdbs
select * from #tblres
drop table #ts
drop table #tblres

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-22 : 11:55:39

You should tell the developer that it is not recommended to use system tables in your code. Always use the INFORMATION_SCHEMA views if possible.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources