SQL Newbie - testing integrity of a database restore

Is it possible to list the number of items in a table in a SQL query? I am testing backup/restore in a test environment so I don't have an application server to read from the SQL databases so I was hoping to run a simple-ish SQL query that will read through the database tables and come up with a total  number of records (or something like that). Hopefully that total before and after a restore should be the same...

Alternatively, is there a good integrity test and/or read test that I could run against the databases to prove the restore is good?

Problem is, my knowledge of SQL language is now 12 years old and very, very rusty. Creaky even. And I'm new to SQL 2000.  

Thanks!
LVL 30
Duncan MeyersAsked:
Who is Participating?
 
JulianvaConnect With a Mentor Commented:
With the addition of
USE <db name>
EXEC sp_msforeachtable
etc

the Sp_msforeachtable  procedure does not work like the normal procedures
you have to use the @command with parameters

if you want to get table names , columnname and count  run the following Query

sp_msforeachtable
@command1 = "Print '?' ",
@command2 = "select top 0 * from ?",
@command3 = "Select count(*)  from ?"
0
 
r_a_j_e_s_hCommented:
try this

select * from sysobjects

u will the objects in ur database.

using the objects, u can  get teh info
0
 
rdesigaudCommented:
Hi meyersd,

Select count(*) from You_Table

This kind of basic query should bring you the number of records in a table...

Hope this helps !
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
JulianvaCommented:
If you want to get a count of  records in all your tables at once with one query
run the following script

sp_msforeachtable
@command1 = "Print '?' ",
@command2 = "Select count(*) from ?"





0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
consider using the sp_spaceused command....

Hope this helped...
0
 
Duncan MeyersAuthor Commented:
Thanks Julianva,

With the addition of
USE <db name>
EXEC sp_msforeachtable
etc

and we're in business!

Is it a simple matter to:
get a total of all the count figures?
and output the table names? Currently the result window shows the figures and (No column name).

Thanks!
0
 
Duncan MeyersAuthor Commented:
Thanks Julianva - worked a treat!
0
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.

All Courses

From novice to tech pro — start learning today.