SQL Server 2000 Table Information

Hi There,

I am looking for an easy way to display all the table names in a certain database, including the number of rows, columns and size of table in KB.

Any Ideas?

Baikie
BaikieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Please try this script

create table #temp (table_name sysname , row_count int, reserved_size varchar(50), data_size varchar(50), index_size varchar(50), unused_size varchar(50))
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select a.table_name, a.row_count, count(*) as col_count, a.data_size
from #temp a inner join information_schema.columns b on a.table_name = b.table_name
group by a.table_name, a.row_count, a.data_size
drop table #temp

0
ShogunWadeCommented:
sweet ;)
0
BaikieAuthor Commented:
Hi,

Thanks for the reply. Do i just copy and paste this script into the query analyzer or are there any parameters that i need to supply?

Thanks,

Baikie
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HilaireCommented:
Just copy-paste in QA, select your database in the combo, and press F5
0
BaikieAuthor Commented:
Ok, tried that but came up with error message:

Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for equal to operation.

??

0
HilaireCommented:
OK that means that your tempdb and your current database don't use the same collation settings

to resolve this you'll need to find out what's your default collaition settings in your DB
for this, in QA, use

select databasepropertyex(db_name(),'collation')

let's say you get
SQL_Latin1_General_CP1_CI_AS

modify the join in my code my code above as follows, with your own collation settings

create table #temp (table_name sysname , row_count int, reserved_size varchar(50), data_size varchar(50), index_size varchar(50), unused_size varchar(50))
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select a.table_name, a.row_count, count(*) as col_count, a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name COLLATE SQL_Latin1_General_CP1_CI_AS
group by a.table_name, a.row_count, a.data_size
drop table #temp

Hilaire
0
BaikieAuthor Commented:
You were right, it did display SQL_Latin1_General_CP1_C1_AS

therefore i just copy and pasted your new script into QA and ran it. However it now comes up with the following error:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'COLLATE'.

Baikie
0
HilaireCommented:
it works for me ...
what's your version on SQL Server ?
0
BaikieAuthor Commented:
It is SQL Server 2000 with latest Service Pack.

By the way, thanks for the help it is greatly appreciated. I am just a newbie to SQL Server 2000 after using Access for the past couple of years.

0
HilaireCommented:
I don't have a clue why this would not work on a SQL 2K box ...

could you try this instead ?

create table #temp (
   table_name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50)
)
insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''
select a.table_name, a.row_count, count(*) as col_count, a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by a.table_name, a.row_count, a.data_size
drop table #temp

0
ShogunWadeCommented:
It isnt Desktop edition is it?
0
HilaireCommented:
Thanks for joining ShogunWade,
I'm afraid I'm stuck ...
0
BaikieAuthor Commented:
No, it is standard edition.

Just to recap,

create table #temp (
   table_name sysname COLLATE SQL_Latin1_General_CP1_CI_AS,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50)
)

When i execute this part i get this error:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'COLLATE'.

when i remove the COLLATE command it executes no problem.

I can run the Insert Statement ok:

insert #temp exec sp_msforeachtable 'sp_spaceused ''?'''

Then when i run the select satement:

select a.table_name, a.row_count, count(*) as col_count, a.data_size
from #temp a
inner join information_schema.columns b on a.table_name = b.table_name
group by a.table_name, a.row_count, a.data_size

i recieve the following:

Server: Msg 446, Level 16, State 9, Line 3
Cannot resolve collation conflict for equal to operation

Thanks for your help guys

0
ShogunWadeCommented:
"Thanks for joining ShogunWade,"   No probs,   I suspect if your stuck i may end up there too, but ill give it a bash.

Given that ther is a collation conflict, would i be right in assuming that the default Server collation is different to that of your database?

can you give this a try ?  just out of curiosity

create table #temp (
   table_name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50)
)
0
BaikieAuthor Commented:
Tried the above but just came back with the same error:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'COLLATE'.
0
ShogunWadeCommented:
hmm. interesting.

can you just echo back the results of  

SELECT @@VERSION
0
BaikieAuthor Commented:
Ok, no probs:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
0
ShogunWadeCommented:
Hmm. wierd.   Ive just tested this on exacly the same config  with no problems.

I dont think there is an easy answer to this one (as im sure Hill has concluded).  So I hope you dont mind be firing a number of little thoughts at to to test.   then maybe it'll help me pinpoint it.

Firstly could you check that the collation of the temp db is the same as the collation of the master db    and model is also the same.

0
BaikieAuthor Commented:
temp db = Latin1_General_CI_AS

master db = Latin1_General_CI_AS

model db = Latin1_General_CI_AS

J2KPilot = SQL_Latin1_General_CP1_CI_AS (This is the database i require the info from)




0
ShogunWadeCommented:
Im just rigging a test environment to simulate this ( ill be a couple of mins)
0
HilaireCommented:
If your DB has been restored from an old backup (from an older version of sql server, say 6.5),
this might have affected the compatibility level of your DB.

Default in SQL 2000 is 80

please run

sp_helpdb J2KPilot

and let us know what is returned in the compatibility_level field
compatibility level under 80 won't let you use COLLATE

I think it can be changed using sp_dbcmptlevel

sp_dbcmptlevel 'J2KPilot', 80

I never used it so I'm not aware of any negative impact / drawbacks.

Maybe ShogunWade has already used it ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShogunWadeCommented:
you might also want to try if you can execute the create table statement from withing the master database.  

ie:

use master
go
create table #temp (
   table_name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS,
   row_count int,
   reserved_size varchar(50),
   data_size varchar(50),
   index_size varchar(50),
   unused_size varchar(50)
)
0
BaikieAuthor Commented:
SUCCESS!!!!

Nice one!

It was in Compatability Level 70.

I used sp_dbcmpt 'J2Kpilot', 80, then ran your original script. All the info i need!!! Excellent.

I will increase the points value by 100 and give Hilaire '125' (as this was the answer) and ShogunWade '100' for the time taken out to help.

Many thanks guys, as i mentioned before this is my first experience of SQL Server and it is very pleasing to know that there are people out there willing to take the time out to help. Thanks again!

Baikie
0
ShogunWadeCommented:
"ShogunWade '100' "  not necessary, give all points to hill, he did all the work.
0
ShogunWadeCommented:
too late :|

Thats certainly one for the memory bank.

PS:   Its well worth when you have a bit of time on your hand getting the collation across the server consistent.   When you do this opt for Latin_General.....  rather than SQL_Latin.....    as the SQL collations are only present now in SQL 2000 for backward compatibility.

0
HilaireCommented:
Split sounds fair to me.
Again thks for your support Shogun, I don't have much merit on this one, I remember reading this compatibility issues from another thread by arbert or ScottPletcher ...

See you (both) in another thread
Regards

Hilaire
0
ShogunWadeCommented:
"Again thks for your support Shogun"  is a pleasure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.