一些 T-SQL 技巧

一、只复制一个表结构，不复制数据

selecttop0*into[t1]from[t2]

二、获取数据库中某个对象的创建脚本

1、先用下面的脚本创建一个函数

ifexists(select1fromsysobjectswhereid=object_id('fgetscript')andobjectproperty(id,'IsInlineFunction')=0)

dropfunctionfgetscript

createfunctionfgetscript(

@servernamevarchar(50)--服务器名

,@useridvarchar(50)='sa'--用户名,如果为nt验证方式,则为空

,@passwordvarchar(50)=''--密码

,@databasenamevarchar(50)--数据库名称

,@objectnamevarchar(250)--对象名

)returnsvarchar(8000)

begin

declare@revarchar(8000)--返回脚本

declare@srvidint,@dbsidint--定义服务器、数据库集id

declare@dbidint,@tbidint--数据库、表id

declare@errint,@srcvarchar(255),@descvarchar(255)--错误处理变量

--创建sqldmo对象

exec@err=sp_oacreate'sqldmo.sqlserver',@srvidoutput

if@err〈〉0gotolberr

--连接服务器

ifisnull(@userid,'')=''--如果是Nt验证方式

begin

exec@err=sp_oasetproperty@srvid,'loginsecure',1

if@err〈〉0gotolberr

exec@err=sp_oamethod@srvid,'connect',null,@servername

end

else

exec@err=sp_oamethod@srvid,'connect',null,@servername,@userid,@password

if@err〈〉0gotolberr

--获取数据库集

exec@err=sp_oagetproperty@srvid,'databases',@dbsidoutput

if@err〈〉0gotolberr

--获取要取得脚本的数据库id

exec@err=sp_oamethod@dbsid,'item',@dbidoutput,@databasename

if@err〈〉0gotolberr

--获取要取得脚本的对象id

exec@err=sp_oamethod@dbid,'getobjectbyname',@tbidoutput,@objectname

if@err〈〉0gotolberr

--取得脚本

exec@err=sp_oamethod@tbid,'script',@reoutput

if@err〈〉0gotolberr

--print@re

return(@re)

lberr:

execsp_oageterrorinfoNULL,@srcout,@descout

declare@errbvarbinary(4)

set@errb=cast(@errasvarbinary(4))

execmaster..xp_varbintohexstr@errb,@reout

set@re='错误号:'+@re

+char(13)+'错误源:'+@src

+char(13)+'错误描述:'+@desc

return(@re)

end

2、用法如下

用法如下,

printdbo.fgetscript('服务器名','用户名','密码','数据库名','表名或其它对象名')

3、如果要获取库里所有对象的脚本，如如下方式

declare@namevarchar(250)

declare#aacursorfor

selectnamefromsysobjectswherextypenotin('S','PK','D','X','L')

open#aa

fetchnextfrom#aainto@name

while@@fetch_status=0

begin

printdbo.fgetscript('onlytiancai','sa','sa','database',@name)

fetchnextfrom#aainto@name

end

close#aa

deallocate#aa

4、声明，此函数是csdn邹建邹老大提供的

三、分隔字符串

如果有一个用逗号分割开的字符串，比如说“a,b,c,d,1,2,3,4“，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢?因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。

1、获取元素个数的函数

createfunctiongetstrarrlength(@strvarchar(8000))

returnsint

begin

declare@int_returnint

declare@startint

declare@nextint

declare@locationint

select@str=','+@str+','

select@str=replace(@str,',,',',')

select@start=1

select@next=1

select@location=charindex(',',@str,@start)

while(@location〈〉0)

begin

select@start=@location+1

select@location=charindex(',',@str,@start)

select@next=@next+1

end

select@int_return=@next-2

return@int_return

end

2、获取指定索引的值的函数

createfunctiongetstrofindex(@strvarchar(8000),@indexint=0)

returnsvarchar(8000)

begin

declare@str_returnvarchar(8000)

declare@startint

declare@nextint

declare@locationint

select@start=1

select@next=1--如果习惯从0开始则select@next=0

select@location=charindex(',',@str,@start)

while(@location〈〉0and@index〉@next)

begin

select@start=@location+1

select@location=charindex(',',@str,@start)

select@next=@next+1

end

if@location=0select@location=len(@str)+1--如果是因为没有逗号退出，则认为逗号在字符串后

select@str_return=substring(@str,@start,@location-@start)--@start肯定是逗号之后的位置或者就是初始值1

if(@index〈〉@next)select@str_return=''--如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。

return@str_return

end

3、测试

SELECT[dbo].[getstrarrlength]('1,2,3,4,a,b,c,d')

SELECT[dbo].[getstrofindex]('1,2,3,4,a,b,c,d',5)

四、一条语句执行跨越若干个数据库

我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?

第一种方法:

select*fromOPENDATASOURCE('SQLOLEDB','DataSource=远程ip;UserID=sa;Password=密码').库名.dbo.表名

第二种方法:

先使用联结服务器:

EXECsp_addlinkedserver'别名','','MSDASQL',NULL,NULL,'DRIVER={SQLServer};SERVER=远程名;UID=用户;PWD=密码;'

execsp_addlinkedsrvlogin@rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

然后你就可以如下：

select*from别名.库名.dbo.表名

insert库名.dbo.表名select*from别名.库名.dbo.表名

select*into库名.dbo.新表名from别名.库名.dbo.表名

五、怎样获取一个表中所有的字段信息

蛙蛙推荐：怎样获取一个表中所有字段的信息

先创建一个视图

Createviewfielddesc

selecto.nameastable_name,c.nameasfield_name,t.nameastype,c.lengthas

length,c.isnullableasisnullable,convert(varchar(30),p.value)asdesp

fromsyscolumnsc

joinsystypestonc.xtype=t.xusertype

joinsysobjectsoono.id=c.id

leftjoinsyspropertiesponp.smallid=c.colidandp.id=o.id

whereo.xtype='U'

查询时:

Select*fromfielddescwheretable_name='你的表名'

还有个更强的语句，是邹建写的，也写出来吧

SELECT

(casewhena.colorder=1thend.nameelse''end)N'表名',

a.colorderN'字段序号',

a.nameN'字段名',

(casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)N'标识',

(casewhen(SELECTcount(*)

FROMsysobjects

WHERE(namein

(SELECTname

FROMsysindexes

WHERE(id=a.id)AND(indidin

(SELECTindid

FROMsysindexkeys

WHERE(id=a.id)AND(colidin

(SELECTcolid

FROMsyscolumns

WHERE(id=a.id)AND(name=a.name)))))))AND

(xtype='PK'))〉0then'√'else''end)N'主键',

b.nameN'类型',

a.lengthN'占用字节数',

COLUMNPROPERTY(a.id,a.name,'PRECISION')asN'长度',

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)asN'小数位数',

(casewhena.isnullable=1then'√'else''end)N'允许空',

isnull(e.text,'')N'默认值',

isnull(g.[value],'')ASN'字段说明'

--into##tx

FROMsyscolumnsaleftjoinsystypesb

ona.xtype=b.xusertype

innerjoinsysobjectsd

ona.id=d.idandd.xtype='U'andd.name〈〉'dtproperties'

leftjoinsyscommentse

ona.cdefault=e.id

leftjoinsyspropertiesg

ona.id=g.idANDa.colid=g.smallid

orderbyobject_name(a.id),a.colorder

六、时间格式转换问题

因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有“+“操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。

1、把所有“70.07.06“这样的值变成“1970-07-06“

UPDATElvshi

SETshengri='19'+REPLACE(shengri,'.','-')

WHERE(zhiyezheng='139770070153')

2、在“1970-07-06“里提取“70“,“07“,“06“

SELECTSUBSTRING(shengri,3,2)ASyear,SUBSTRING(shengri,6,2)ASmonth,

SUBSTRING(shengri,9,2)ASday

FROMlvshi

WHERE(zhiyezheng='139770070153')

3、把一个时间类型字段转换成“1970-07-06“

UPDATElvshi

SETshenling=CONVERT(varchar(4),YEAR(shenling))

+'-'+CASEWHENLEN(MONTH(shenling))=1THEN'0'+CONVERT(varchar(2),

month(shenling))ELSECONVERT(varchar(2),month(shenling))

END+'-'+CASEWHENLEN(day(shenling))=1THEN'0'+CONVERT(char(2),

day(shenling))ELSECONVERT(varchar(2),day(shenling))END

WHERE(zhiyezheng='139770070153')

七、分区视图

分区视图是提高查询性能的一个很好的办法

--看下面的示例

--示例表

createtabletempdb.dbo.t_10(

idintprimarykeycheck(idbetween1and10),namevarchar(10))

createtablepubs.dbo.t_20(

idintprimarykeycheck(idbetween11and20),namevarchar(10))

createtablenorthwind.dbo.t_30(

idintprimarykeycheck(idbetween21and30),namevarchar(10))

--分区视图

createviewv_t

select*fromtempdb.dbo.t_10

unionall

select*frompubs.dbo.t_20

unionall

select*fromnorthwind.dbo.t_30

--插入数据

insertv_tselect1,'aa'

unionallselect2,'bb'

unionallselect11,'cc'

unionallselect12,'dd'

unionallselect21,'ee'

unionallselect22,'ff'

--更新数据

updatev_tsetname=name+'_更新'whereright(id,1)=1

--删除测试

deletefromv_twhereright(id,1)=2

--显示结果

select*fromv_t

--删除测试

droptablenorthwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10

dropviewv_t

/**//*--测试结果

idname

---------------------

1aa_更新

11cc_更新

21ee_更新

(所影响的行数为3行)

==*/

八、树型的实现

--参考

--树形数据查询示例

--作者:邹建

--示例数据

createtable[tb]([id]intidentity(1,1),[pid]int,namevarchar(20))

insert[tb]select0,'中国'

unionallselect0,'美国'

unionallselect0,'加拿大'

unionallselect1,'北京'

unionallselect1,'上海'

unionallselect1,'江苏'

unionallselect6,'苏州'

unionallselect7,'常熟'

unionallselect6,'南京'

unionallselect6,'无锡'

unionallselect2,'纽约'

unionallselect2,'旧金山'

--查询指定id的所有子

createfunctionf_cid(

@idint

)returns@retable([id]int,[level]int)

begin

declare@lint

set@l=0

insert@reselect@id,@l

while@@rowcount〉0

begin

set@l=@l+1

insert@reselecta.[id],@l

from[tb]a,@reb

wherea.[pid]=b.[id]andb.[level]=@l-1

end

/**//**//**//*--如果只显示最明细的子(下面没有子),则加上这个删除

deleteafrom@rea

whereexists(

select1from[tb]where[pid]=a.[id])

--*/

return

end

--调用(查询所有的子)

selecta.*,层次=b.[level]from[tb]a,f_cid(2)bwherea.[id]=b.[id]

--删除测试

droptable[tb]

dropfunctionf_cid

九、排序问题

CREATETABLE[t](

[id][int]IDENTITY(1,1)NOTNULL,

[GUID][uniqueidentifier]NULL

)ON[PRIMARY]

下面这句执行5次

inserttvalues(newid())

查看执行结果

select*fromt

1、第一种

select*fromt

orderbycaseidwhen4then1

when5then2

when1then3

when2then4

when3then5end

2、第二种

select*fromtorderby(id+2)%6

3、第三种

select*fromtorderbycharindex(cast(idasvarchar),'45123')

4、第四种

select*fromt

WHEREidbetween0and5

orderbycharindex(cast(idasvarchar),'45123')

5、第五种

select*fromtorderbycasewhenid〉3thenid-5elseidend

6、第六种

select*fromtorderbyid/4desc,idasc

十、一条语句删除一批记录

首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的“5,6,8,9,10,11“可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删

除了，比循环用多条语句高效吧应该。

deletefrom[fujian]wherecharindex(','+cast([id]asvarchar)+',',','+'5,6,8,9,10,11,'+',')〉0

还有一种就是

deletefromtable1whereidin(1,2,3,4)

十一、获取子表内的一列数据的组合字符串

下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。

CREATEFUNCTIONfn_Get05LvshiNameBySuo(@p_suoNvarchar(50))

RETURNSNvarchar(2000)

BEGIN

DECLARE@LvshiNamesvarchar(2000),@namevarchar(50)

select@LvshiNames=''

DECLARElvshi_cursorCURSORFOR