Monday, March 12, 2012

List record counts of all tables?

Hi All,
Is there a fancy way to list all table names with record counts?
Using table: INFORMATION_SCHEMA.TABLES
Also, Is there a way to initialize/empty all data from all tables?
Thank you very muchUse TRUNCATE to clear out a table

as for space

USE Northwind
GO

SET NOCOUNT ON
GO

CREATE TABLE #SpaceUsed (
[name] varchar(255)
, [rows] varchar(25)
, [reserved] varchar(25)
, [data] varchar(25)
, [index_size] varchar(25)
, [unused] varchar(25)
)
GO

DECLARE @.tablename nvarchar(128)
, @.maxtablename nvarchar(128)
, @.cmd nvarchar(1000)
SELECT @.tablename = ''
, @.maxtablename = MAX(name)
FROM sysobjects
WHERE xtype='u'

WHILE @.tablename < @.maxtablename
BEGIN
SELECT @.tablename = MIN(name)
FROM sysobjects
WHERE xtype='u' and name > @.tablename

SET @.cmd='exec sp_spaceused['+@.tablename+']'
INSERT INTO #SpaceUsed EXEC sp_executesql @.cmd
END

SET NOCOUNT OFF
GO

SELECT * FROM #SpaceUsed
GO

DROP TABLE #SpaceUSed
GO|||I didn't mean that fancy! It worked nonetheless.

Thanks a million

No comments:

Post a Comment