列示资料库裹各资料表的资料笔数

Calvin

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

另一种方式

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

1. CREATE FUNCTION dbo.udf_Tbl_RowCOUNT (
   
2. 
   
3.         @sTableName sysname  -- Table to retrieve Row Count
   
4.         )
   
5. 
   
6.     RETURNS INT -- Row count of the table, NULL if not found.
   
7. 
   
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 ('')
    
14. 
    
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. ***************************************************************/
    
26. 
    
27. AS BEGIN
    
28.    
    
29.     DECLARE @nRowCount INT -- the rows
    
30.     DECLARE @nObjectID int -- Object ID
    
31. 
    
32.     SET @nObjectID = OBJECT_ID(@sTableName)
    
33. 
    
34.     -- Object might not be found
    
35.     IF @nObjectID is null RETURN NULL
    
36. 
    
37.     SELECT TOP 1 @nRowCount = rows
    
38.         FROM sysindexes
    
39.         WHERE id = @nObjectID AND indid < 2
    
40. 
    
41.     RETURN @nRowCount
    
42. END
    
43. GO
    
44. 
    
45. GRANT  EXECUTE  ON [dbo].[udf_Tbl_RowCOUNT]  TO PUBLIC
    
46. GO

复制代码

使用方式

1. use pubs -- assuming the UDF was created in pubs
   
2. go
   
3. 
   
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

复制代码