SQLServerCentral / Article Discussions / Discuss content posted by Abhijit / Article Discussions by Author / Script to return Rows count from table using sp_ExecuteSQL / Latest Posts

RE: Script to return Rows count from table using sp_ExecuteSQL

Jeff Moden — Thu, 02 Oct 2008 21:15:00 GMT

Heh... No... I'm saying the following is wrong...[code]FROM sys.tables AS tbl where tbl.[b][size="3"][font="Arial Black"]Name[/font][/size][/b] = CAST([b][size="3"][font="Arial Black"]object_id(@TableName)[/font] [/size][/b]AS VARCHAR(100)) ORDER BY 1 [/code] ;)

RE: Script to return Rows count from table using sp_ExecuteSQL

Andrew Coppock — Thu, 02 Oct 2008 11:53:00 GMT

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:

RE: Script to return Rows count from table using sp_ExecuteSQL

Jeff Moden — Wed, 01 Oct 2008 21:02:04 GMT

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

RE: Script to return Rows count from table using sp_ExecuteSQL

Andrew Coppock — Wed, 01 Oct 2008 07:42:35 GMT

[quote]... just to be clear[/quote]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.

RE: Script to return Rows count from table using sp_ExecuteSQL

Jeff Moden — Tue, 30 Sep 2008 19:15:50 GMT

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.

RE: Script to return Rows count from table using sp_ExecuteSQL

Andrew Coppock — Tue, 30 Sep 2008 13:43:26 GMT

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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[usp_GetTableRowCount] @TableName VARCHAR(100)ASBEGIN -- 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[/font]And as I stated, it does not work.

RE: Script to return Rows count from table using sp_ExecuteSQL

Jeff Moden — Mon, 29 Sep 2008 19:04:49 GMT

I must admit, though, there is absolutely no need for the dynamic SQL...[code]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)[/code]

RE: Script to return Rows count from table using sp_ExecuteSQL

Jeff Moden — Mon, 29 Sep 2008 18:56:11 GMT

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

RE: Script to return Rows count from table using sp_ExecuteSQL

wchaster — Mon, 29 Sep 2008 17:46:14 GMT

I also got one row with NULL, so I did not see much use for this SQL.but Russel Bell, this is useful, thanks.:DSometime the topic just gets you looking in the right direction.

RE: Script to return Rows count from table using sp_ExecuteSQL

Madhivanan-208264 — Fri, 26 Sep 2008 09:49:02 GMT

[url]http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx[/url]

RE: Script to return Rows count from table using sp_ExecuteSQL

Andrew Coppock — Fri, 26 Sep 2008 09:09:26 GMT

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.

RE: Script to return Rows count from table using sp_ExecuteSQL

Russel Bell — Fri, 26 Sep 2008 08:59:41 GMT

Interesting but I'm not sure why you would do this instead of:select count(*) from TimeZonesWhat 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 tblORDER BY 1[/font]

RE: Script to return Rows count from table using sp_ExecuteSQL

Dugi — Wed, 23 Jul 2008 00:49:03 GMT

It brings to me the NULL value !!!!!!!!!!!!??????????? :w00t::w00t::w00t::w00t:Another how to use it to see the results!:P LOL

RE: Script to return Rows count from table using sp_ExecuteSQL

IN_Sandeep — Tue, 22 Jul 2008 23:12:02 GMT

Nice...Really helpful..

Script to return Rows count from table using sp_ExecuteSQL

abmore — Tue, 22 Jul 2008 23:11:06 GMT

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