这个偶尔自己会用到T-SQL查询

例子一

有时我会需要查询一些数据直接生成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