Show the number of records and space for each database table.
I created a nice SQL query today to show the number of records in each table in a database and the space it takes in the database in megabytes.
Very handy to see why some MS CRM databases are so big.
declare @tmpTable TABLE ([RowCount] int, [TableName] nvarchar(max)) declare @ResultTable TABLE ([name] nvarchar(max),[rows] int, [reserverd] nvarchar(max),[data] nvarchar(max),[index_size] nvarchar(max),[unused] nvarchar(max)) insert into @tmpTable ([RowCount], [TableName])SELECT [RowCount] = MAX(si.rows), [TableName] = so.name FROM sysobjects so, sysindexes si WHERE so.xtype = ‘U’ ANDÂ si.id = OBJECT_ID(so.name) GROUP BY so.name ORDER BY 2 DESC declare @cursor cursor, @tablename varchar(max) set @cursor = cursor for begin select [name], ROUND((CAST(REPLACE(data, ‘ KB’, ”) as float) / 1024), 2) from @ResultTable order by (CAST(REPLACE(data, ‘ KB’, ”) as int)) desc |
Posted: May 19th, 2010 under MS CRM 4.0, MS SQL Server.
Comments: none
Write a comment