Common
--Query the number of records for all tables in the database, Occupy space, index use space execsp_msforeachtable@precommand=N'CREATE TABLE # # # (table name sysname, number of records int, reserved space Nvarchar (20), Space varchar (20), index using space varchar (20), unused space varchar ())', @command1=N'Insert # # EXEC sp_spaceused"'?" ", @postcommand=N'select * FROM # # ORDER by record number desc'DROP TABLE##
Another kind
Create Table#tb (table name sysname, number of recordsint, Reserve spacevarchar( -), use spacevarchar( -), the index uses spacevarchar( -), unused spacevarchar( -))Insert into#tbexecSp_msforeachtable'EXEC sp_spaceused"'?" "Select * from#tbGoSELECTtable name, number of records,cast(LTrim(RTrim(Replace(Reserved space,'KB',"'))) as int)/1024x768Reserved space MB,cast(LTrim(RTrim(Replace(Using Space,'KB',"'))) as int)/1024x768Use space MB,cast(LTrim(RTrim(Replace(Using Space,'KB',"'))) as int)/1024x768/1024.00use space GB,cast(LTrim(RTrim(Replace(Index uses space,'KB',"'))) as int)/1024x768Index uses space MB,cast(LTrim(RTrim(Replace(Unused space,'KB',"'))) as int)/1024x768Unused space MB from#tbWHERE cast(LTrim(RTrim(Replace(Using Space,'KB',"'))) as int)/1024x768 > 0 --ORDER BY record number descORDER byUse space MBDESCDROP TABLE#tb
Query the number of records for all tables in the database, Occupy space, index use space