• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 823
  • Last Modified:

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!!!
0
jrandallsexton
Asked:
jrandallsexton
1 Solution
 
Renante EnteraCommented:
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 :-)
0
 
ram2098Commented:
Another simple way of doing it........

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


0
 
RDWaibelCommented:
here we go...

Create Proc sp_GetTableCount
  @TableName Varchar(255)
AS
select rows
from sysobjects o inner join sysindexes i on o.id = i.id
where i.indid < 2 and xtype='u' and o.name = rtrim(@TableName)
0
 
jrandallsextonAuthor Commented:
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!!!!

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now