I NEED TO DO A ROWCOUNT ON ALL TABLES ON A GIVEN DATABASE. I CAN EASILY DO THIS IN SQL USING A PIVOT TABLE.
CAN SOMEONE HELP?
THIS IS WHAT I HAVE:
LETS SAY THIS IS (TABLE_COUNTS)
THIS IS WHAT I WANT TO ACCOMPLISH:
LETS SAY THIS IS (TABLE_INPUTDATA)
TABLE1 TABLE2 TABLE3 TABLE4
500 200 800 499
CAN SOMEONE HELP PLEASE. LET ME KNOW IF YOU NEED ADDITIONAL INFORMATION. THANKS.
It's good that you found a solution on your own, but it would be nice if you could share it.
Btw, your question is quite confusing, i still don't know what you actually wanted to do.
I have a few TD tables that I needed to do rowcounts. e.g.
count(*) from TableName.
Now, the result of the count is going to a temp table. Once I get all the counts inserted into the temp table (temptable) then I need to convert those rows to column. Just like using pivot in SQL server.
The way I accomplished this task was using the following syntax:
Max(Case when t2.tablename in('table1') then rowsnum else null end) table1
,Max(Case when t2.tablename in('table2') then rowsnum else null end) table2
,Max(Case when t2.tablename in('table3') then rowsnum else null end) table3
(select tablename as tablename, rowsnum, row_number() over(order by tablename) as RowNr from temptable)
Now, the result of this query get inserted into another permanent table that i can use to compare the counts.
Now my table read:
databaseName table1 table2 table3
Raymond 100 200 300
Raymon2 100 200 300
See, the counts match.
In the event that the count doesn't match, i get notification. This process it to load a production table from dev.
what if number of tables increases and you finally hit the 2048 columns in a table limit :-)
For me it would be much easier to compare those counts when they are rows in a table.
Thank you Deiter.
I do understand, but I need to keep my counts in a single row for all the tables for history audits.
The ideal solution is if i can convert the following MSSQL server code to Teradata:
SET NOCOUNT ON;
CREATE TABLE #temp(
[table1] [int] NULL,
[table2] [int] NULL,
[table3] [int] NULL,
[table4] [int] NULL
) ON [PRIMARY]
SELECT so.name [TableName], si.rows [RowCount]
-- SELECT *
FROM sysobjects so
INNER JOIN sysindexes si on si.id = OBJECT_ID(so.name)
AND SI.INDID <2
WHERE so.xtype = 'U'
PIVOT (MAX([RowCount]) FOR
[TableName] IN (
There's no place like sysindexes in Teradata which keeps the rowcount.
You need to run multplie SELECTs like
(SELECT COUNT(*) FROM tab1) as cnt1,
(SELECT COUNT(*) FROM tab2) as cnt2,
Or you extract tha info from the stats, it's not exact, but if stats are mantained regularily it might be good enough.