Row Count for all SQL tables in a Schema

[Steven Knapman]

Does anyone know of a way to do this?

I'm pretty new to ISC Caché SQL and I am used to using sys partitions Microsoft SQL to list row counts for tables. We have been handed a fairly large database, about 7000 tables but the vast majority of tables/fields in it are not used so it would be useful to see which tables actually contain data.

Many thanks,

[Dmitry Maslennikov]

In Caché you can do it in the way like below.

calcAll() public {

set sql="SELECT STRING(TABLE_SCHEMA, '.', TABLE_NAME) TableName, ClassName "_

"FROM INFORMATION_SCHEMA.TABLES "_

"WHERE TABLE_TYPE LIKE '%BASE%' AND TABLE_SCHEMA NOT LIKE 'Ens%'"

set statement=##class(%SQL.Statement).%New()

set sc=statement.%Prepare(sql)

set rs=statement.%Execute()

while rs.%Next() {

set tableName=rs.TableName

set className=rs.CLASSNAME

set cnt=$$count(tableName)

write !,className," - ",cnt

}

}

count(table) public {

set sql="SELECT count(*) Cnt FROM "_table

set statement=##class(%SQL.Statement).%New()

set sc=statement.%Prepare(sql)

set rs=statement.%Execute()

set count=0

if rs.%Next() {

set count=rs.Cnt

}

quit count

}

You can create some mac routine, like `count.mac`, and put my code there

after call, you will information about number of rows in every class

SAMPLES>d ^count

Aviation.Aircraft - 1215

Aviation.Crew - 1382

Aviation.Event - 1200

Aviation.Cubes.Aircraft.Fact - 0

Aviation.Cubes.Aircraft.Listing - 0

Aviation.Cubes.Aircraft.StarAircraftCategory - 0

Aviation.Cubes.Aircraft.StarAircraftManufacturer - 0

Aviation.Cubes.Aircraft.StarAircraftModel - 0

Aviation.Cubes.Aircraft.StarDamage - 0

Aviation.Cubes.Aircraft.StarFlightType - 0

Aviation.Cubes.Crew.Fact - 0

Aviation.Cubes.Crew.Listing - 0

Aviation.Cubes.Crew.StarAge - 0

Aviation.Cubes.Crew.StarAgeRg814105347 - 0

Aviation.Cubes.Crew.StarCategory - 0

Aviation.Cubes.Crew.StarInjury - 0

Aviation.Cubes.Crew.StarMedicalCertification - 0

Aviation.Cubes.Crew.StarSex - 0

Aviation.Cubes.Events.Fact - 0

Aviation.Cubes.Events.Listing - 0

Aviation.Cubes.Events.Star3610825905 - 0

Aviation.Cubes.Events.Star627747261 - 0

Aviation.Cubes.Events.StarInjuriesHighest - 0

Aviation.Cubes.Events.StarMidAir - 0

Aviation.Cubes.Events.StarSkyConditionNonCeiling - 0

--
--
Caché, Ensemble, DeepSee

---
You received this message because you are subscribed to the Google Groups "Caché, Ensemble, DeepSee" group.
To unsubscribe from this group and stop receiving emails from it, send an email to intersystems-publi...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Dmitry Maslennikov

Senior Caché Developer

C SYSTEM CZ a.s. http://csystem.cz/

Skype: DAiMor

about.me/dmitry.maslennikov

[Andreas]

You can use the latest release of Caché Monitor to count all rows with one click. See details here

Regards
Andreas