|
|
Posted by Erland Sommarskog on 04/25/06 00:54
andro (av1@email.t-com.hr) writes:
> Regarding the first part.....nothing special - it's just that I'm kinda
> bored to repeatedly enter nearly the same query for every system here.
> (There are not just several but say 40-50 which are changing from
> project to project.) I just wondered (please note: I'm a newbie) is
> there a way to instruct the system in a kind of "loop" DO - UNTIL or
> FOR/NEXT for every system table in database to repat the qurey within
> the tables using "parameter" TBL_NAME where neccessary. Some kind of
> automation maybe - you know. (Seems very advanced request).
You can do this:
exec sp_MSforeachtable 'SELECT COUNT(*), ''?'' FROM ?'
But this is mainly good for admin tasks. And it is undocumented and
unsupported.
The reason that there is not any more civilised way to this is that
it is not meaningful. In a well-designed database, each table is
supposed to describe a unique entity, and thus each table has a
different layout and running the same query on several tables is not
possible, unless it's a trivial one like the SELECT COUNT(*) in the
example above.
If you have a database where you have a lot of tables with the same
structure, then you have a poorly designed database. Best would be
to unite the tables into one - or construct a partitioned view over
the tables, and then run the queries against that view.
> (I heaven't checked your link yet ).
In case you have multiple tables wthe same layout, look particularly
at http://www.sommarskog.se/dynamic_sql.html#Sales_yymm.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|