Script to return Rows count from table using sp_ExecuteSQL

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

  • Nice...

    Really helpful..

    --

  • 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]

  • 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]

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

  • 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:

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

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

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

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

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

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

  • 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:

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

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

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