|
|
|
SSC Eights!
 
Group: General Forum Members
Last Login: Today @ 9:02 AM
Points: 814,
Visits: 464
|
|
|
|
|
|
Old Hand
Group: General Forum Members
Last Login: Thursday, November 12, 2009 7:19 AM
Points: 375,
Visits: 1,242
|
|
Nice...
Really helpful..
--
|
|
|
|
|
SSC Eights!
Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 940,
Visits: 2,145
|
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, November 13, 2009 8:03 AM
Points: 12,
Visits: 104
|
|
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
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, September 21, 2009 8:40 AM
Points: 27,
Visits: 30
|
|
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.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Monday, November 16, 2009 7:46 AM
Points: 288,
Visits: 357
|
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, November 04, 2009 9:17 AM
Points: 57,
Visits: 83
|
|
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.
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 18,344,
Visits: 12,357
|
|
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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 18,344,
Visits: 12,357
|
|
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."
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Monday, September 21, 2009 8:40 AM
Points: 27,
Visits: 30
|
|
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.
|
|
|
|
