MS-SQL开发常用汇总和t-sql技巧集锦

　  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
as
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)
as
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(WINDOWS平台上强大的数据库平台)};SERVER=远程名;UID=用户;PWD=密码;'
exec sp_addlinkedsrvlogin  @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'
GO
 

然后你就可以如下：
 

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

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

Create view fielddesc　 
as
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 Prima(最完善的虚拟主机管理系统)ry key check(id between 1 and 10),name varchar(10))

create table pubs.dbo.t_20(
id int Prima(最完善的虚拟主机管理系统)ry key check(id between 11 and 20),name varchar(10))

create table northwind.dbo.t_30(
id int Prima(最完善的虚拟主机管理系统)ry key check(id between 21 and 30),name varchar(10))
go

--分区视图
create view v_t
as
select * from tempdb.dbo.t_10
union all
select * from pubs.dbo.t_20
union all
select * from northwind.dbo.t_30
go

--插入数据
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
go

--删除测试
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 行）
==*/