[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Server 2000 Table Information

Posted on 2004-11-22
27
Medium Priority
?
288 Views
Last Modified: 2007-11-27
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
0
Comment
Question by:Baikie
  • 10
  • 9
  • 8
27 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12646554
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12647193
sweet ;)
0
 

Author Comment

by:Baikie
ID: 12652683
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 26

Expert Comment

by:Hilaire
ID: 12652696
Just copy-paste in QA, select your database in the combo, and press F5
0
 

Author Comment

by:Baikie
ID: 12652718
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12652769
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
 

Author Comment

by:Baikie
ID: 12652828
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12652892
it works for me ...
what's your version on SQL Server ?
0
 

Author Comment

by:Baikie
ID: 12652983
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12653147
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12653226
It isnt Desktop edition is it?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12653237
Thanks for joining ShogunWade,
I'm afraid I'm stuck ...
0
 

Author Comment

by:Baikie
ID: 12653319
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12653385
"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
 

Author Comment

by:Baikie
ID: 12653433
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12653535
hmm. interesting.

can you just echo back the results of  

SELECT @@VERSION
0
 

Author Comment

by:Baikie
ID: 12653576
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12653671
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
 

Author Comment

by:Baikie
ID: 12653731
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12653839
Im just rigging a test environment to simulate this ( ill be a couple of mins)
0
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12653917
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
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 400 total points
ID: 12654014
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
 

Author Comment

by:Baikie
ID: 12654115
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12654139
"ShogunWade '100' "  not necessary, give all points to hill, he did all the work.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12654183
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 12654195
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12654202
"Again thks for your support Shogun"  is a pleasure.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question