Get list of tables in MSSQL database with rowcount

In a recent client project I needed to find the number of tables in the database.  Not a big deal, but I also wanted to filter out any files that didn't have a certain column name and I also wanted the number of rows in each table.  After some snooping around this is what I came up with. 

TableInfoTable           FILE,DRIVER('MSSQL','/TURBOSQL=True'), pre(TINFO) 
Record                     RECORD
TableName                    CSTRING(101)
RowsInTable                 LONG
                           END
                         END

FQ                       QUEUE,PRE()
Completed                  BYTE
CompletedIcon              LONG
FileName                   CSTRING(100)
RowsInTable                LONG                         
END

LoadFileQueue ROUTINE
Data
SQL CString(4096)
Code
SQL = 'SELECT T.name TableName, I.rows TableRows FROM MyDatabase.sys.tables T ' &|
'JOIN MyDatabase.sys.columns C ON T.object_id = C.object_id ' &|
'JOIN MyDatabase.sys.sysindexes I ON T.object_id = I.id and I.indid < 2 ' &|
'WHERE T.TYPE_DESC = <39>USER_TABLE<39> ' &|
' AND C.name = <39>ColName<39> ' &|
'ORDER BY T.Name'
Open(TableInfoTable)
Buffer(TableInfoTable,100)
TableInfoTable {PROP:SQL} = SQL
Loop
Next(TableInfoTable)
If ErrorCode()
Break
End
FQ.Completed = False
FQ.CompletedIcon = 0
FQ.FileName = TINFO:TableName
FQ.RowsInTable = TINFO:RowsInTable
Add(FQ)
End
Close(TableInfoTable)


This works very well and since the row counter simply counts the index numbers it is very fast.  This only takes a few milliseconds to run to extract about 50 table names from the database.

This also shows a very good way to use TurboSQL structures to pull data from the database without having to mess with dummy files or dictionary files.  The TurboSQL is just a structure, doesn't exist anywhere except in memory and requires minimum fuss.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk