Results 1 to 8 of 8

Thread: overview query

  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Unanswered: overview query

    I'm trying to write a query to get a table that contains some basic information such as table names, table sizes (in rows) and such.

    I am able to do a query to get the table names:
    SELECT name FROM dbo.sysobjects WHERE type='U' and name !='dtproperties' ORDER BY name

    This shows a listing sorta like:
    table1
    table2
    table3
    table4

    I would like to do a
    SELECT COUNT(*) table1
    SELECT COUNT(*) table2
    SELECT COUNT(*) table3
    SELECT COUNT(*) table4

    so that my output looks like
    table1 13
    table2 47
    table3 0
    table4 93

    anyone know a way i can do this in 1 sql query, or know a way to use multiple sql statements with an asp.net datagrid?

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    don't know of a single select to handel your request but could yo ucall a stored procedure from your ASP page?

    Code:
    create procedure TableRowCount
    as
    declare @TableName sysname
    create table #Tmp (TableName sysname, [RowCount] int)
    select @TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties'
    while (@TableName is not null) begin
      insert into #Tmp
      exec('select ''' + @TableName + ''', count(*) from ' + @TableName)
      select @TableName = min([name]) from sysobjects WHERE type='U' and [name] !='dtproperties' and [name] > @TableName
    end
    select cast(TableName as varchar) as TableName, [RowCount] From #Tmp
    return 0
    go
    
    exec TableRowCount
    go
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Mar 2003
    Posts
    7
    thanks! you're a genius. now i just have to figure out what all that means. i'm not used to stored procedures, but that gives the output i need, in the format i need it.

    Thanks again!

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Basicaly, I created a temporary table to hold the results of counting the number of records in a table, then stepped through the sysobjects table looking for records that matched your original where clause. Once a table was found I did the count and stuffed the result set into the temp table. When there were no more table to count I returned the result set. All of this is basic stuff and Books Online will answer any questions you have.

    I should have put comments in the code!
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Mar 2003
    Posts
    7
    na, it's fine how it was, because I understood most of it, and i'm just a sql beginner.

    I just didn't understand the #Tmp being a temporary table, since i'd never seen those, but that's the idea i was going to try, but keeping track of the table was going to be a pain.

    Hopefully now I'll be able to finish my application

  6. #6
    Join Date
    Mar 2003
    Posts
    7
    would #Tmp be what i'm hearing be referred to as a cursor? My boss does a lot of programming, and suggested that i make a cursor, but i had never heard that terminology.

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No #Tmp is a sesion temporary table. Once you log off the table is destroyed.

    You can read up on cursors in Microsofts's Books Online. The have their uses but are VERY resource intensive.

    The solution I posted is an alternative to a cursor.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    Mar 2003
    Posts
    7
    ok cool

    you bet i'll be reading those I'd really eventually like to do some more sql programming, it's kinda just going along with my asp.net stuff right now, but that's next on the list, since data is important to all applications

    thanks again paul

Posting Permissions

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