Shawn,

If you are using SQL2000, you can use the sp_MSforeachtable stored proc. Details from a mailing by TechRepublic follow:

Today's SQL Server tip

SP_MSFOREACHTABLE SYSTEM STORED PROCEDURE V2K
You will not find a reference to it in the 2k books online, but it has
been in SQL Server since v6.5: a little-known system stored procedure
that will issue commands based upon all the tables in a specific
database.

If you wanted to execute a table row count against every table in your
database, you may first be inclined to write a cursor that queries the
informationschema views or system tables to first get a list of tables
in the database. Then process each row with the required count(*)
command.

The sp_MSforeachtable stored procedure can do all of this for you
behind the scenes. All you do is pass in the commands you want executed
and sp_MSforeachtable does the rest. Know that the question mark (?) is
the table place holder for this procedure. Anywhere you would like to
refer to the table, use the question mark identifier. You can pass in
as many as three separate commands by identifying each command as
@command1, @command2, and @command3.

Consider the following scenario:
sp_MSforeachtable @command1 = "PRINT REPLICATE('*',DATALENGTH('?'))",
@command2 = "SELECT '?' = COUNT(*) FROM ?"
The above statement will output similar to the following:

**************
[dbo].[Table1]
--------------
309

***************
[dbo].[Table29]
---------------
545

*********************
[dbo].[HistoryTable1]
---------------------
968
You can use sp_MSforeachtable most anywhere. Note that this procedure
resides in the master database.

------------------------------------------

TechRepublic TechMails may contain links to sites on the Internet which
are owned and operated by third parties. TechRepublic, Inc. is not
responsible for the content of any such third-party site.

To update your TechMails selections:
http://www.techrepublic.com/techmails.jhtml


If the stored proc is not available to you, in SQL Query Alalyzer, you can select something like:
use mydatabase
go
select 'alter ' + o.name + ' drop column msrepl_synctran_ts
go'
from sysobjects o
where o.type = 'U'

Take the output except for the number of rows found and cut it and then paste it into the query analyzer and run it.

For more speed, break it into several seperate pieces and run them in different SQL Query Analyzer windows.

This is not too elegant but easy to do.

. . . Tom

Tom Zeblisky
Reuters




Shawn Smith
<[Email Address Removed] To: "'Mssqldba Distribution List (E-mail)'" <[Email Address Removed]
egram.com> cc:
Subject: RE: Data issues
01/22/02 11:55 AM
Header: Internal Use Only







Turns out the problem is when I made the change the replication created
another column in all of the tables called....msrepl_synctran_ts. Now I need
to delete this form all the tables. How can I do this without going through
each one individually?
example:
alter (all_tables) drop column msrepl_synctran_ts

-----Original Message-----
From: Shawn Smith [mailto:[Email Address Removed] Tuesday, January 22, 2002 9:46 AM
To: Mssqldba Distribution List (E-mail)
Subject: Data issues


I switched form Snapshot replication to Transactional Replication over the
weekend. I ran out of space and had to disable replication all together
Monday. I am now getting reports form people that there is data issues.
Columns have changed and tables were dropped. Could the replication caused
this? I know in theory it should not effect any data, so it may just be a
coincidence. And if it is not how do I recover what was lost without
restoring form backup?


Shawn Smith
Database Administrator
[Email Address Removed] cell
817-667-2757 pager


---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page