如何快捷地查询SQL SERVER中每个用户表的表名和行数?

发布网友发布时间：2022-04-30 19:31

我来回答

共3个回答

懂视网时间：2022-04-30 23:52

CREATE TABLE #RowCounts(NumberOfRows BIGINT,TableName VARCHAR(128))

EXEC sp_MSForEachTable ‘INSERT INTO #RowCounts
SELECT COUNT_BIG(*) AS NumberOfRows,
‘‘?‘‘ as TableName FROM ?‘

SELECT TableName,NumberOfRows
FROM #RowCounts
ORDER BY NumberOfRows DESC,TableName

DROP TABLE #RowCounts

sp_MSForEachTable是SqlServer中没有Document下来的存储过程。

SqlServer中获取数据库中每个表的行数

标签：

热心网友时间：2022-04-30 21:00

执行一下就可以了
CREATE TABLE #T ([name] nvarchar(255),rows bigint)
DECLARE @Name nvarchar(255),@Sql nvarchar(4000)
DECLARE TName CURSOR FOR
SELECT [name] FROM sysobjects WHERE xtype = 'U'
OPEN TName
FETCH NEXT FROM TName
INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Sql='INSERT INTO #T ([name],rows) SELECT '''+@name+''' AS name, COUNT(1) AS row FROM '+@name
EXEC(@Sql)
FETCH NEXT FROM TName INTO @name
END
CLOSE TName
DEALLOCATE TName
SELECT * FROM #T

热心网友时间：2022-04-30 22:18

select * from sysobjects where xtype = 'U'

查询数据库中所有用户表的信息