SQL查看所有表和删除所有表
查看所有表use 数据库名
select name from sysobjects where type='u'
删除所有表
use 数据库名
declare @sql varchar(800)
while (select count(*) from sysobjects where type='U')>0
begin
SELECT @sql='drop table ' + name
from sysobjects
WHERE (type = 'U')ORDER BY 'drop table ' + name
exec(@sql) end
查看所有表大小
use 数据库名
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
int null,
int null,
dec(15) null,
dec(15) null,
dec(15) null,
dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select> open c_tables fetch next from c_tables into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255) and> select @pages = sum(dpages)
from sysindexes
where indid < 2
and> select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255 and> update #spt_space set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and> where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved - (
select sum(used)
from sysindexes
where indid in (0, 1, 255) and> )
where objid = @id
update #spt_space set = i.
from sysindexes i
where i.indid < 2 and i.id = @id and objid = @id
fetch next from c_tables into @id
end
select TableName = (select left(name,60) from sysobjects where> = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables
页:
[1]