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
Monday, March 12, 2012
List record counts of all tables?
Labels:
counts,
countsusing,
database,
empty,
fancy,
information_schema,
initialize,
microsoft,
mysql,
names,
oracle,
record,
server,
sql,
table,
tables,
tablesalso
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment