Post reply

typical query

  • May 2, 2006 at 8:46 am

    #112731

    Hi,

     

    I need a query which will return the tablename,and total no of records of that table name in sql server.I dont want in plsql.

     

    Thanks,

    J.Aswani kumar.

  • May 2, 2006 at 8:57 am

    #635334

    PL/SQL is Oracle lingo...SQL Server's language is T-SQL.

    If your stats are up-to-date, then you can do this:

    select  substring(o.name, 1, 30) Table_Name ,i.rows  Number_of_Rows

    from  sysobjects o 

    inner join sysindexes i 

    on (o.id = i.id)

    where  o.xtype = 'u' 

    and i.indid < 2

    order by o.name

    or you can also use the undocumented stored proc sp_Msforeachtable to achieve the goal. Sp_MSforeachtable scans through all the tables in a certain database and performs a command on those tables. You use "?" as a placeholder to represent the table name.

    sp_msforeachtable @command1= 'print ''?''', @command2 = "select count(*) from ?"

  • May 2, 2006 at 10:15 am

    #635362

    use mydatabase

    go

    dbcc updateusage(mydatabase)

    go

    sp_msforeachtable @command1= 'sp_spaceused ''?'''

    go

    MVDBA

  • May 2, 2006 at 6:04 pm

    #635484

    CREATE TABLE #TableUsage (

     Name sysname,

     Rows int,

     reserved nvarchar(50),

     Data nvarchar(50),

     Index_Size nvarchar(50),

     unused nvarchar(50)

    &nbsp

    EXEC sp_msforeachtable @command1= 'INSERT INTO #TableUsage exec sp_spaceused ''?'''

    SELECT * FROM #TableUsage

    _____________
    Code for TallyGenerator

  • May 2, 2006 at 11:45 pm

    #635498

    Thanks a lot for ur answers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply