這個偶爾自己會用到T-SQL查詢

還是自己寫的代碼用著比較舒服

declare @sql varchar(3000),@cname varchar(100),@ctype varchar(20),@tablename varchar(50),@condition varchar(200)
--------------------------------------------------------------------

set @tablename='table_1' --表名
set @condition=' where createdate>='+char(39)+'2010.03.01'+char(39) --條件

--------------------------------------------------------------------
set @sql='select '
declare column_name cursor for
select column_name, data_type from information_schema.columns where table_name=@tablename order by ordinal_position
open column_name
fetch column_name into @cname,@ctype
while @@fetch_status=0
begin
if @ctype='varchar'  or @ctype='nvarchar' or @ctype='char' or @ctype='text'
set @sql=@sql+'char(39)+'+@cname+'+char(39) '+@cname+','
else if @ctype='datetime'
set @sql=@sql+char(39)+'getdate()'+char(39)+' '+@cname+','
else
set @sql=@sql+@cname+','

fetch column_name into @cname,@ctype
end
set @sql=@sql+'1 from '+@tablename+@condition
print @sql
exec (@sql)
close column_name
deallocate column_name

 

資料量有多大等等一些資訊

簡易的寫一段代碼查詢

declare @tablename varchar(100),@tablename2 varchar(50)
create table #xsage(sumcount int,tablename varchar(50))
declare tablename_Xsage cursor for
select table_name from information_schema.tables
where table_type='BASE TABLE'
open tablename_Xsage
fetch tablename_xsage into @tablename
while @@fetch_status=0
begin
set @tablename2=char(39)+@tablename+char(39)
insert into #xsage
exec ('select count(*),'+@tablename2+' from ' +@tablename)
fetch tablename_xsage into @tablename
end
close tablename_Xsage
deallocate tablename_Xsage
select sumcount,tablename from #xsage
select sum(sumcount) from #xsage
drop table #xsage