Concatenate strings

[alison]

Hi there,

I have created a temporary table which has "Table_Name" and "Count" columns.

What I want to do is automatically populate this table with each table name in the database (not sys or system) and it's row count, from that I can create a truncate statement easily.

The statement I am trying to get to work is:

'select 'select count(*), table_name from '||owner||'.'||table_name||';' from dba_tables
where owner not in ('SYS','SYSTEM');

I know that eventually this will turn into an 'update table ....' statement but at the moment I am just trying to get this bit to work.

I'm sure that this could be done using PLSQL but I want to do it with pure SQL

Any advice would be greatly appreciated

Many thanks

Alison

[BMorley]

How about:

SELECT 'INSERT INTO Temp_Table (TableName, Count) SELECT '''||Owner||'.'||Table_Name||''', COUNT(*) FROM '||OWNER||'.'||TABLE_NAME||';'
FROM DBA_TABLES
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
ORDER BY Table_Name

This would give you output like:

INSERT INTO Temp_Table (TableName, Count) SELECT 'OWNER1.TABLENAME1', COUNT(*) FROM OWNER1.TABLENAME1;
INSERT INTO Temp_Table (TableName, Count) SELECT 'OWNER2.TABLENAME2', COUNT(*) FROM OWNER2.TABLENAME2;
Just spool and run

cheers

Ben

[alison]

Many thanks for you help..

That worked a treat ;-)

Alison

[BMorley]

Another idea.

Take a look at ALL_TABLES.

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE OWNER NOT IN ('SYS','SYSTEM');

As far as I know this method only works if the tables are ANALYZED.

Ben

[alison]

Thanks Ben,

I did originally try and do it the way you suggested, however the schema which I would have to analyze has over 5000 tables in it (the overhead could be a bit large)

The other suggestion works well, if a bit messy.

Could anybody help me out with a PLSQL script which could do a similar job.

Cheers

Alison