Selecting from Sysobjects

Posted on 2004-11-10
Last Modified: 2008-03-03

I would like to be able to create a stored procedure that accepts a tablename and returns the count of records in that table.

Something like (except I want one that works):

CREATE PROCEDURE [dbo].[spr_RecordCount_GetByTable] AS

DECLARE @TableName nvarchar


I have a feeling that SysObjects will be involved although I'm not sure how.

I tried this and it didn't work either:

select count(*) from (select * from sysobjects where sysobjects.[name] = 'tblChemInfo')

Thanks in advance!!!
Question by:jrandallsexton
    LVL 14

    Expert Comment

    by:Renante Entera
    Hello jrandallsexton!

    Try to have your procedure like this:

    CREATE PROCEDURE [dbo].[spr_RecordCount_GetByTable]
        @TableName nvarchar
        EXECUTE 'Select count(*) From' || @TableName

    Hope this helps.  Just try it.

    eNTRANCE2002 :-)
    LVL 11

    Expert Comment

    Another simple way of doing it........

    CREATE PROCEDURE [dbo].[spr_RecordCount_GetByTable]
        @TableName nvarchar
        EXEC sp_spaceused @TableName

    LVL 14

    Accepted Solution

    here we go...

    Create Proc sp_GetTableCount
      @TableName Varchar(255)
    select rows
    from sysobjects o inner join sysindexes i on =
    where i.indid < 2 and xtype='u' and = rtrim(@TableName)
    LVL 4

    Author Comment

    Only one of these solution worked.
    I tried them all in SQL Query Analyzer with this querystring:

    exec spr_RecordCount_GetByTable 'tblUsers'

    entrance2002: Incorrect syntax near 'Select count(*) From' --> And I even added the DECLARE that you forgot

    ram2098: The SP sytax checks out.  But when I execute the above query, I get:
    Server: Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 76
    The object 't' does not exist in database 'PSCClientDB'.

    RDWaibel: Perfecto!  Thanks much!!!!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now