如题.
解决方案 »
- 数据库恢复的问题
- 如何将VB程序和SQL SERVER 2000一起打包发布,而不需要在其它机器上再单独安装SQL SERVER 2000
- [求助]MS SQLSERVER SQL连接语句
- 用路由器上网,就不能在本地机子上测试连接网上的sql2000服务器的网站了吗?
- ???怎么批量将指定数据库用户的SELECT、INSERT、UPDATE、EXEC等权限。
- 转贴新思路: Guid 和 Int 作为系统编号的取舍
- 如何得到一个数据库中的所有的表名,视图名称(用语句实现)
- SQL SERVER一个库最多能容纳多少张表
- 大家来动动脑子,我被搅糊涂了。很着急。 (简单描述)
- 如何把sqlserver数据表导入EXCEL?谢谢先!
- 一个很奇怪的sql问题 你遇到过没有?
- 求存储过程中插入unicode字符串的方法
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a'
exec(@sql)/*
--sql server 2000 自带库 pubs 的结果
name num
----------- -----------
titleauthor 25
stores 6
sales 21
roysched 86
discounts 3
jobs 14
pub_info 8
employee 43
authors 23
publishers 8
titles 18
*/
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a where num > 0'
exec(@sql)--无记录declare @sql varchar(8000)
set @sql='select * from ('
select @sql=@sql+' select name = ''' + name + ''' , count(*) as num from ['+name+'] union all ' from sysobjects where xtype='u'
set @sql = left(@sql,len(@sql) - 10) + ')a where num = 0'
exec(@sql)
-- 无记录的表
-- 可能会由于 sysindexes 没有及时更新,而产生偏差。
select o.name
from sysindexes i inner join sysobjects o on i.id=o.id
where indid in (0,1) and rowcnt=0 and o.type='u'
-- 适用于 sql server 2005
select o.[name]
from sys.partitions p inner join sys.objects o on p.[object_id]=o.[object_id]
where o.type='u'
group by o.[name] having SUM(p.[rows])=0
select b.name,a.rowcnt from sysindexes a,sysobjects b
where a.id=b.id and a.indid<2 and b.xtype='u'--统计数据库里每个表的详细情况
EXEC sp_MSforeachtable @command1="sp_spaceused '?'" --获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1="print '?'",
@command2="sp_spaceused '?'",
@command3= "SELECT count(*) FROM ? "
呵呵 这还是我第一次在csdn上回答问题。use [数据库名称]
CREATE TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'INSERT INTO #temp SELECT ''?'', COUNT(*) FROM ?'
SELECT TableName, RowCnt FROM #temp ORDER BY TableName
DROP TABLE #temp
有记录的:declare @tablename varchar(100)
declare @sql nvarchar(4000)
declare @Num int
declare tb_cursor scroll cursor for select name from sysobjects where xtype='U'
open tb_cursor
fetch next from tb_cursor into @tablename
while @@fetch_status=0
begin
set @sql=N'select @Num=count(1) from '+@tablename
exec sp_executesql @sql,N'@Num int out',@Num out
if @Num>0 print @tablename
fetch next from tb_cursor into @tablename
end
close tb_cursor
deallocate tb_cursor
无记录的:declare @tablename varchar(100)
declare @sql nvarchar(4000)
declare @Num int
declare tb_cursor scroll cursor for select name from sysobjects where xtype='U'
open tb_cursor
fetch next from tb_cursor into @tablename
while @@fetch_status=0
begin
set @sql=N'select @Num=count(1) from '+@tablename
exec sp_executesql @sql,N'@Num int out',@Num out
if @Num<=0 print @tablename
fetch next from tb_cursor into @tablename
end
close tb_cursor
deallocate tb_cursor