易丰科技

标题: 列示资料库裹各资料表的资料笔数 [打印本页]
作者: Calvin    时间: 2011-10-7 10:27
标题: 列示资料库裹各资料表的资料笔数
  1. SELECT
  2.     [TableName] = so.name,
  3.     [RowCount] = MAX(si.rows)
  4. FROM
  5.     sysobjects so,
  6.     sysindexes si
  7. WHERE
  8.     so.xtype = 'U'
  9.     AND
  10.     si.id = OBJECT_ID(so.name)
  11. GROUP BY
  12.     so.name
  13. ORDER BY
  14.     2 DESC
复制代码

作者: Calvin    时间: 2011-10-7 10:31
另一种方式
  2. CREATE PROCEDURE dbo.listTableRowCounts
  3. AS
  4. BEGIN
  5.     SET NOCOUNT ON
  7.     DECLARE @SQL VARCHAR(255)
  8.     SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
  9.     EXEC(@SQL)
  11.     CREATE TABLE #foo
  12.     (
  13.         tablename VARCHAR(255),
  14.         rc INT
  15.     )
  16.      
  17.     INSERT #foo
  18.         EXEC sp_msForEachTable
  19.             'SELECT PARSENAME(''?'', 1),
  20.             COUNT(*) FROM ?'
  22.     SELECT tablename, rc
  23.         FROM #foo
  24.         ORDER BY rc DESC
  26.     DROP TABLE #foo
  27. END
复制代码
作者: Calvin    时间: 2011-10-7 10:34
标题: 第三种方式 user defined function
  1. CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (

  3.         @sTableName sysname  -- Table to retrieve Row Count
  4.         )

  6.     RETURNS INT -- Row count of the table, NULL if not found.

  8. /*
  9. * Returns the row count for a table by examining sysindexes.
  10. * This function must be run in the same database as the table.
  11. *
  12. * Common Usage:   
  13. SELECT dbo.udf_Tbl_RowCOUNT ('')

  15. * Test   
  16. PRINT 'Test 1 Bad table ' + CASE WHEN SELECT
  17.        dbo.udf_Tbl_RowCOUNT ('foobar') is NULL
  18.         THEN 'Worked' ELSE 'Error' END
  19.         
  20. * © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
  21. * You may use this function in any of your SQL Server databases
  22. * including databases that you sell, so long as they contain
  23. * other unrelated database objects. You may not publish this
  24. * UDF either in print or electronically.
  25. ***************************************************************/

  27. AS BEGIN
  28.    
  29.     DECLARE @nRowCount INT -- the rows
  30.     DECLARE @nObjectID int -- Object ID

  32.     SET @nObjectID = OBJECT_ID(@sTableName)

  34.     -- Object might not be found
  35.     IF @nObjectID is null RETURN NULL

  37.     SELECT TOP 1 @nRowCount = rows
  38.         FROM sysindexes
  39.         WHERE id = @nObjectID AND indid < 2

  41.     RETURN @nRowCount
  42. END
  43. GO

  45. GRANT  EXECUTE  ON [dbo].[udf_Tbl_RowCOUNT]  TO PUBLIC
  46. GO
复制代码
使用方式
  1. use pubs -- assuming the UDF was created in pubs
  2. go

  4. SELECT [name]
  5.      , dbo.udf_Tbl_RowCOUNT ([name]) as [Row Count]
  6.     FROM sysobjects
  7.     WHERE type='U' and name != 'dtproperties'
  8.     ORDER BY [name]
  9. GO
复制代码






欢迎光临 易丰科技 (http://forum.yftek.com/) Powered by Discuz! X3