34,594
社区成员
发帖
与我相关
我的任务
分享
--更新PUBS数据库中已t开头的所有表的统计:
EXEC sp_MSforeachtable @whereand="and name like 't%'",
@replacechar='*',
@precommand="print 'Updating Statistics.....' print ''",
@command1="print '*' update statistics * ",
@postcommand= "print''print 'Complete Update Statistics!'"
领导刚才说,5000个表查询速度不能超过5秒
他说可能是用游标
SELECT P1.*,P2.*,P3.* FROM A AS P1 INNER JOIN (SELECT 你的字段 FROM B ) P2 ON P1.字段= P2.字段 INNER JOIN (SELECT 字段 FROM C )P3 ON P1.字段= P3.字段
declare @sql varchar(max)
set @sql='';
select @sql=@sql+'selct nm from '+ name +' union all ' from sys.objects s where s.type='u'
and name like 'table%'
--print (@sql);
print( left(@sql,len(@sql)-LEN(' union all ')))
exec( left(@sql,len(@sql)-LEN(' union all ')))
declare @sql nvarchar(max)
set @sql = N''
select @sql = @sql + N'select [nm] from ' + QUOTENAME([name]) + N' union all ' + CHAR(13) + CHAR(10) from sys.tables where [name] like 'table%'
select @sql = N'select * from(' + CHAR(13) + CHAR(10) + LEFT(@sql, len(@sql) - LEN(N' union all ') -2) + N') a where [nm] = ''xxx'''
print @sql
exec(@sql)
--RESULT
/*
select * from(
select [nm] from [table2] union all
select [nm] from [table1] union all
select [nm] from [table3]) a where [nm] = 'xxx'
*/