【转】SQL Server 查询表的记录数(3种方法,推荐第一种)

时间:2021-08-02 07:19:00
--SQL Server 查询表的记录数

--one: 使用系统表.
SELECT object_name (i.id) TableName,
rows as RowCnt
FROM sysindexes i
INNER JOIN sysObjects o
ON (o.id = i.id AND o.xType = 'U ')
WHERE indid < 2
ORDER BY TableName --****************** --two: 使用未公开的过程 "sp_MSforeachtable "
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp --****************** -- three: 使用游标.cursor
SET NOCOUNT ON
DECLARE @tableName VARCHAR (255),
@sql VARCHAR (300)
CREATE TABLE #temp (TableName VARCHAR (255), rowCnt INT)
DECLARE myCursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table '
OPEN myCursor
FETCH NEXT FROM myCursor INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ( 'INSERT INTO #temp (TableName, rowCnt) SELECT ''' + @tableName + ''' as tableName, count(*) as rowCnt from ' + @tableName)
FETCH NEXT FROM myCursor INTO @tableName
END
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
CLOSE myCursor
DEALLOCATE myCursor
DROP TABLE #temp