整理了一些t-sql技巧(收藏)

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

select top 0 * into [t1] from [t2]

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

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

if exists(select 1 from sysobjects where id=object_id('fgetscript') and objectproperty(id,'IsInlineFunction')=0)

drop function fgetscript

create function fgetscript(

@servername varchar(50) --服务器名

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

,@password varchar(50)='' --密码

,@databasename varchar(50) --数据库名称

,@objectname varchar(250) --对象名

) returns varchar(8000)

begin

declare @re varchar(8000) --返回脚本

declare @srvid int,@dbsid int --定义服务器、数据库集id

declare @dbid int,@tbid int --数据库、表id

declare @err int,@src varchar(255), @desc varchar(255) --错误处理变量

--创建sqldmo对象

exec @err=sp_oacreate 'sqldmo.sqlserver',@srvid output

if @err<>0 goto lberr

--连接服务器

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

begin

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

if @err<>0 goto lberr

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

end

else

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

if @err<>0 goto lberr

--获取数据库集

exec @err=sp_oagetproperty @srvid,'databases',@dbsid output

if @err<>0 goto lberr

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

exec @err=sp_oamethod @dbsid,'item',@dbid output,@databasename

if @err<>0 goto lberr

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

exec @err=sp_oamethod @dbid,'getobjectbyname',@tbid output,@objectname

if @err<>0 goto lberr

--取得脚本

exec @err=sp_oamethod @tbid,'script',@re output

if @err<>0 goto lberr

--print @re

return(@re)

lberr:

exec sp_oageterrorinfo NULL, @src out, @desc out

declare @errb varbinary(4)

set @errb=cast(@err as varbinary(4))

exec master..xp_varbintohexstr @errb,@re out

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

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

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

return(@re)

end

2、用法如下
用法如下,

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

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

declare @name varchar(250)

declare #aa cursor for

select name from sysobjects where xtype not in('S','PK','D','X','L')

open #aa

fetch next from #aa into @name

while @@fetch_status=0

begin

print dbo.fgetscript('onlytiancai','sa','sa','database',@name)

fetch next from #aa into @name

end

close #aa

deallocate #aa

4、声明，此函数是csdn邹建邹老大提供的
三、分隔字符串
如果有一个用逗号分割开的字符串，比如说"a,b,c,d,1,2,3,4"，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。
1、获取元素个数的函数

create function getstrarrlength (@str varchar(8000))

returns int

begin

declare @int_return int

declare @start int

declare @next int

declare @location int

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、获取指定索引的值的函数

create function getstrofindex (@str varchar(8000),@index int =0)

returns varchar(8000)

begin

declare @str_return varchar(8000)

declare @start int

declare @next int

declare @location int

select @start =1

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

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

while (@location <>0 and @index > @next )

begin

select @start = @location +1

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

select @next =@next +1

end

if @location =0 select @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 * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').库名.dbo.表名

第二种方法:
先使用联结服务器:

EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;'

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

然后你就可以如下：

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

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

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

五、怎样获取一个表中所有的字段信息
蛙蛙推荐：怎样获取一个表中所有字段的信息
先创建一个视图

Create view fielddesc

select o.name as table_name,c.name as field_name,t.name as type,c.length as

length,c.isnullable as isnullable,convert(varchar(30),p.value) as desp

from syscolumns c

join systypes t on c.xtype = t.xusertype

join sysobjects o on o.id=c.id

left join sysproperties p on p.smallid=c.colid and p.id=o.id

where o.xtype='U'

查询时:

Select * from fielddesc where table_name = '你的表名'

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

SELECT

(case when a.colorder=1 then d.name else '' end) N'表名',

a.colorder N'字段序号',

a.name N'字段名',

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

(case when (SELECT count(*)

FROM sysobjects

WHERE (name in

(SELECT name

FROM sysindexes

WHERE (id = a.id) AND (indid in

(SELECT indid

FROM sysindexkeys

WHERE (id = a.id) AND (colid in

(SELECT colid

FROM syscolumns

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

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

b.name N'类型',

a.length N'占用字节数',

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

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

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

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

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

--into ##tx

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join syscomments e

on a.cdefault=e.id

left join sysproperties g

on a.id=g.id AND a.colid = g.smallid

order by object_name(a.id),a.colorder

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

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

UPDATE lvshi

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

WHERE (zhiyezheng = '139770070153')

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

SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,

SUBSTRING(shengri, 9, 2) AS day

FROM lvshi

WHERE (zhiyezheng = '139770070153')

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

UPDATE lvshi

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

+ '-' + CASE WHEN LEN(MONTH(shenling)) = 1 THEN '0' + CONVERT(varchar(2),

month(shenling)) ELSE CONVERT(varchar(2), month(shenling))

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

day(shenling)) ELSE CONVERT(varchar(2), day(shenling)) END

WHERE (zhiyezheng = '139770070153')

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

--看下面的示例

--示例表

create table tempdb.dbo.t_10(

id int primary key check(id between 1 and 10),name varchar(10))

create table pubs.dbo.t_20(

id int primary key check(id between 11 and 20),name varchar(10))

create table northwind.dbo.t_30(

id int primary key check(id between 21 and 30),name varchar(10))

--分区视图

create view v_t

select * from tempdb.dbo.t_10

union all

select * from pubs.dbo.t_20

union all

select * from northwind.dbo.t_30

--插入数据

insert v_t select 1 ,'aa'

union all select 2 ,'bb'

union all select 11,'cc'

union all select 12,'dd'

union all select 21,'ee'

union all select 22,'ff'

--更新数据

update v_t set name=name+'_更新' where right(id,1)=1

--删除测试

delete from v_t where right(id,1)=2

--显示结果

select * from v_t

--删除测试

drop table northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10

drop view v_t

/*--测试结果

id name

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

1 aa_更新

11 cc_更新

21 ee_更新

（所影响的行数为 3 行）

==*/

八、树型的实现

--参考

--树形数据查询示例
--作者: 邹建

--示例数据
create table [tb]([id] int identity(1,1),[pid] int,name varchar(20))
insert [tb] select 0,'中国'
union  all  select 0,'美国'
union  all  select 0,'加拿大'
union  all  select 1,'北京'
union  all  select 1,'上海'
union  all  select 1,'江苏

点滴积累,融会贯通