asked on

Selecting from Sysobjects

Hello:

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

SELECT COUNT(*) FROM @TableName

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!!!

Renante Entera

Hello jrandallsexton!

Try to have your procedure like this:

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

Hope this helps. Just try it.

Goodluck!
eNTRANCE2002 :-)

ram2098

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

CREATE PROCEDURE [dbo].[spr_RecordCount_GetByTable]
(
@TableName nvarchar
)
As
Begin
EXEC sp_spaceused @TableName
End

ASKER CERTIFIED SOLUTION

RDWaibel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrandallsexton

ASKER

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!!!!