?
Solved

SQL Newbie - testing integrity of a database restore

Posted on 2005-05-11
7
Medium Priority
?
193 Views
Last Modified: 2010-08-05
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!
0
Comment
Question by:Duncan Meyers
7 Comments
 
LVL 3

Expert Comment

by:r_a_j_e_s_h
ID: 13984054
try this

select * from sysobjects

u will the objects in ur database.

using the objects, u can  get teh info
0
 
LVL 2

Expert Comment

by:rdesigaud
ID: 13984084
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
 
LVL 8

Expert Comment

by:Julianva
ID: 13984218
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 13985259
consider using the sp_spaceused command....

Hope this helped...
0
 
LVL 30

Author Comment

by:Duncan Meyers
ID: 14008245
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
 
LVL 8

Accepted Solution

by:
Julianva earned 2000 total points
ID: 14008437
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
 
LVL 30

Author Comment

by:Duncan Meyers
ID: 14035066
Thanks Julianva - worked a treat!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 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