收集了一些t-sql的技巧示例

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

UPDATE lvshi
SET shengri = '19' + REPLACE(shengri, '.', '-')
WHERE (zhiyezheng = '139770070153')
 

SELECT SUBSTRING(shengri, 3, 2) AS year, SUBSTRING(shengri, 6, 2) AS month,
      SUBSTRING(shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '139770070153')

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))
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 行）
==*/

--参考

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

--示例数据
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,'江苏'
union  all  select 6,'苏州'
union  all  select 7,'常熟'
union  all  select 6,'南京'
union  all  select 6,'无锡'
union  all  select 2,'纽约'
union  all  select 2,'旧金山'
go

--查询指定id的所有子
create function f_cid(
@id int
)returns @re table([id] int,[level] int)
as
begin
 declare @l int
 set @l=0
 insert @re select @id,@l
 while @@rowcount>0
 begin
  set @l=@l+1
  insert @re select a.[id],@l
  from [tb] a,@re b
  where a.[pid]=b.[id] and b.[level]=@l-1
 end
/**//**//**//*--如果只显示最明细的子(下面没有子),则加上这个删除
 delete a from @re a
 where exists(
  select 1 from [tb] where [pid]=a.[id])
--*/
 return
end
go

--调用(查询所有的子)
select a.*,层次=b.[level] from [tb] a,f_cid(2)b where a.[id]=b.[id]
go

--删除测试
drop table [tb]
drop function f_cid
go

CREATE TABLE [t] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

insert t values (newid())

select * from t
 order by case id when 4 then 1
                  when 5 then 2
                  when 1 then 3
                  when 2 then 4
                  when 3 then 5 end

select * from t order by (id+2)%6

select * from t order by charindex(cast(id as varchar),'45123')

select * from t
WHERE id between 0 and 5
order by charindex(cast(id as varchar),'45123')

select * from t order by case when id >3 then id-5 else id end

select * from t order by id / 4 desc,id asc

delete from [fujian] where charindex(','+cast([id] as varchar)+',',','+'5,6,8,9,10,11,'+',')>0

delete from table1 where id in(1,2,3,4 )

CREATE   FUNCTION fn_Get05LvshiNameBySuo  (@p_suo Nvarchar(50))
RETURNS Nvarchar(2000)
AS
BEGIN 
 DECLARE @LvshiNames varchar(2000), @name varchar(50)
 select @LvshiNames=''
 DECLARE lvshi_cursor CURSOR FOR
 

create table 库存(DVD编号 int,name varchar(20))
create trigger ttr
on 库存 for insert
as
if exists(select * from inserted where DVD编号 is null)
begin
raiserror('自定义的错误',16,-1)
end

insert into 库存(DVD编号,name ) select null,'aa'
--消息 50000，级别 16，状态 1，过程 ttr，第 7 行
--自定义的错误
select * from 库存