The Transact-SQL script below (written for SQL Server 2000, but also works with 2005), returns a list
of tables and a count of the number of records within each table.
DECLARE @name VARCHAR(500)
DECLARE @sql VARCHAR(1000)
CREATE TABLE #TableRecordCount
( TableName VARCHAR(100),
RecordCount INT
)
DECLARE dbCursor CURSOR FAST_FORWARD
FOR
SELECT T.NAME
FROM SYSOBJECTS T
WHERE T.XTYPE = 'U'
ORDER BY T.NAME
OPEN dbCursor
FETCH NEXT FROM dbCursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'INSERT INTO #TableRecordCount (TableName, RecordCount) '
SET @sql = @sql + 'SELECT ''' + @Name + ''' AS TableName, COUNT(*) AS CNT '
SET @sql = @sql + 'FROM [' + @name + ']'
EXEC (@sql)
FETCH NEXT FROM dbCursor INTO @name
END
CLOSE dbCursor
DEALLOCATE dbCursor
SELECT * FROM #TableRecordCount
DROP TABLE #TableRecordCount
Responses
0 Respones to "Count the number of records in each table within a database"
Post a Comment