[筆記]如何找出所有資料表的筆數?
除了使用sp_msforeachdb這個特殊的store procedure外，可以透過下列語法去找:
DECLARE @DBName nvarchar(200)
DECLARE @name VARCHAR(150) -- database name
use master
SET NOCOUNT ON
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Database Name : ' + @NAME
PRINT '--------------------------------'
SET @DBName= @name
SET @SQL = 'USE ' + @DBName + char(13)
+ N'select substring(obj.name, 1, 50) as Table_Name,ind.rows as Number_of_Rows from sysobjects as obj inner join sysindexes as ind on obj.id = ind.id where ind.indid < 2 and obj.xtype like ''U'' order by obj.name';
EXEC sp_sqlexec @SQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
結果:
如果您有微軟技術開發的問題，可以到MSDN Forum發問。
如果您有微軟IT管理的問題，可以到TechNet Forum發問喔。
