Concatenate strings
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Concatenate strings

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

  2. #2
    Join Date
    Feb 2001
    Posts
    6

    Thumbs up

    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

  3. #3
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200

    Thumbs up

    Many thanks for you help..

    That worked a treat ;-)

    Alison

  4. #4
    Join Date
    Feb 2001
    Posts
    6
    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

  5. #5
    Join Date
    Feb 2001
    Location
    Scotland
    Posts
    200
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width