Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to return Rows count from table using sp_ExecuteSQL Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2008 11:11 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:36 AM
Points: 1,158, Visits: 878
Comments posted to this topic are about the item Script to return Rows count from table using sp_ExecuteSQL
Post #538963
Posted Tuesday, July 22, 2008 11:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, March 7, 2011 3:15 AM
Points: 375, Visits: 1,255
Nice...
Really helpful..


--
Post #538965
Posted Wednesday, July 23, 2008 12:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:44 AM
Points: 1,260, Visits: 3,424
It brings to me the NULL value !!!!!!!!!!!!???????????


Another how to use it to see the results!

:P LOL


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #539029
Posted Friday, September 26, 2008 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 21, 2013 8:42 AM
Points: 13, Visits: 264
Interesting but I'm not sure why you would do this instead of:

select count(*) from TimeZones

What I find more helpful is to be able to return a list rowcounts for all tables in a database in tabular form. I found this somewhere (not original with me).

/* Display table name and rowcount using system tables */
SELECT
tbl.NAME AS [Table],
Coalesce( ( select sum (spart.rows) from sys.partitions spart
where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
FROM sys.tables AS tbl
ORDER BY 1
Post #576940
Posted Friday, September 26, 2008 9:09 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:12 AM
Points: 33, Visits: 55
I also get NULL as the result when I run this procedure.
I have set this procedure up to run as a stored procedure which takes TableName as a parameter to the function. It does not work as advertised in a SQL 2005 environment.
Post #576943
Posted Friday, September 26, 2008 9:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx



Madhivanan

Failing to plan is Planning to fail
Post #576973
Posted Monday, September 29, 2008 5:46 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 25, 2012 7:43 AM
Points: 63, Visits: 109
I also got one row with NULL, so I did not see much use for this SQL.

but Russel Bell, this is useful, thanks.

:D

Sometime the topic just gets you looking in the right direction.



ACN is the world's largest direct seller of telecommunications and essential services.
http://helpu.acndirect.com/
ACN Digital Phone Service customers automatically enjoy unlimited calling to 60 landline
destinations around the world, including India, Mexico and the UK!
Post #578039
Posted Monday, September 29, 2008 6:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
The problemwith all of you folks getting the Null is that you didn't read the script... it's setup to get the rowcount for a table called "TimeZones" which you probably don't have. You need to change the table name...

"Must LOOK eye!" :P


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #578044
Posted Monday, September 29, 2008 7:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
I must admit, though, there is absolutely no need for the dynamic SQL...

DECLARE @TableName	SYSNAME
SET @TableName = 'dbo.JBMTest' --LOOK!!!! CHANGE THE TABLE NAME!!!!
SELECT TOP 1 [Rows] FROM sys.Partitions WHERE Object_ID = OBJECT_ID(@TableName)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #578045
Posted Tuesday, September 30, 2008 1:43 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 7:12 AM
Points: 33, Visits: 55
I may have another problem, but it most certainly isn't the table name. As I said, I made the script into a stored procedure:


/****** Object: StoredProcedure [dbo].[usp_GetTableRowCount]
Script Date: 09/30/2008 14:39:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[usp_GetTableRowCount]
@TableName VARCHAR(100)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

DECLARE @varSQL NVARCHAR(4000)
DECLARE @iCount INT
--SET @varSQL = 'SELECT TOP 1 @iCountOut = rows FROM sys.partitions where object_id = ' + CAST(object_id(@TableName) AS VARCHAR(100))
--EXECUTE sp_ExecuteSQL @varSQL, N'@iCountOut INT OUTPUT', @iCountOut = @iCount OUTPUT
--SELECT @iCount as [rowcount]

SELECT Coalesce( ( select sum (spart.rows) from sys.partitions spart
where spart.object_id = tbl.object_id and spart.index_id < 2), 0) AS [RowCount]
FROM sys.tables AS tbl where tbl.Name = CAST(object_id(@TableName) AS VARCHAR(100)) ORDER BY 1

END


And as I stated, it does not work.
Post #578639
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse