Script to return Rows count from table using sp_ExecuteSQL

Question

Post reply

Script to return Rows count from table using sp_ExecuteSQL

abmore

Hall of Fame

Points: 3628
More actions
July 22, 2008 at 11:11 pm

#186742

Comments posted to this topic are about the item Script to return Rows count from table using sp_ExecuteSQL

Viewing 15 posts - 1 through 14 (of 14 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

IN_Sandeep

SSCertifiable

Points: 6259

July 22, 2008 at 11:12 pm

#846794

Nice...

Really helpful..

--

Dugi SSCoach Points: 17998 More actions · Answer 2

It brings to me the NULL value !!!!!!!!!!!!??????????? :w00t::w00t::w00t::w00t:

Another how to use it to see the results!

😛 LOL

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Russel Bell Old Hand Points: 305 More actions · Answer 3

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).

[font="Courier New"]/* 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[/font]

Andrew Coppock SSC Journeyman Points: 95 More actions · Answer 4

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.

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

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

wchaster SSC-Addicted Points: 422 More actions · Answer 6

I also got one row with NULL, so I did not see much use for this SQL.

but Russel Bell, this is useful, thanks.

😀

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

Jeff Moden SSC Guru Points: 1004454 More actions · Answer 7

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

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004454 More actions · Answer 8

I must admit, though, there is absolutely no need for the dynamic SQL...

DECLARE @TableNameSYSNAME

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Coppock SSC Journeyman Points: 95 More actions · Answer 9

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:

[font="Courier New"]

/****** 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 @varSQLNVARCHAR(4000)

DECLARE @iCountINT

--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[/font]

And as I stated, it does not work.

Jeff Moden SSC Guru Points: 1004454 More actions · Answer 10

You didn't copy the script correctly... you're comparing an Object_ID to a table Name in your from clause.

But, again, all that just isn't necessary. Look at my previous post.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Coppock SSC Journeyman Points: 95 More actions · Answer 11

... just to be clear

If you will look closely, you will see there are two methods in my script. One is commented out. Both return NULL. It should be noted that if I run the script as TSQL in the actual database (USE mydb) then the script works. It returns NULL when I run it as a proc.

Jeff Moden SSC Guru Points: 1004454 More actions · Answer 12

What's your point, Andrew? You said the code didn't work... I told you why it didn't. 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andrew Coppock SSC Journeyman Points: 95 More actions · Answer 13

My point is the code doesn't work in a stored procedure, and I hoped someone might know why... Are you trying to say my table name is wrong? I said that I am using table name as a variable to the proc. That is all. :hehe:

Jeff Moden SSC Guru Points: 1004454 More actions · Answer 14

Heh... No... I'm saying the following is wrong...

FROM sys.tables AS tbl where tbl.[font="Arial Black"]Name[/font] = CAST([font="Arial Black"]object_id(@TableName)[/font] AS VARCHAR(100)) ORDER BY 1

😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)