MSSQL 语句收集(不断)

1.sysobjects

在数据库内创建的每个对象（约束、默认值、日志、规则、存储过程等）在表中占一行。只有在 tempdb 内，

每个临时对象才在该表中占一行

select * from sysobjects where type='u'  选出用户表

 

2.begin ...........end

3 if   aa<>3   begin.................end

4.while ()  begin.................end

5.declare @sqlstr varchar(10)

6.SET @sqlstr = 'DELETE FROM [dnt_topictags] WHERE [tid] IN (' + @tidlist + ')'

7.IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'proc_calculate_taxes' AND type = 'P')

8.
CREATE PROCEDURE proc_calculate_taxes (@p1 smallint = 42, @p2 char(1),
      @p3 varchar(8) = 'CAR')
    AS
   SELECT *
   FROM mytable

9.执行存储过程

declare @c varchar(30)
set @c='Procedure'
exec @c

 

declare @se varchar(100)

set @se='select * from table1'

exec (@se)

'

10      将这些同一字段的值放在一起    declare @a varchar(100)

select @a=@a+','+LoginId  from Account
select @a

11. alter table test add columna int not null

alter table test drop columna 

12.  动态存储过程

CREATE proc test(@v1 varchar(100))
as
begin
declare @55 varchar(100)

set @55='select  *  from tt '+@v1
exec (@55)
end
GO

13随机取记录

SELECT TOP 10 *, NewID() as Random
FROM tt
ORDER BY Random

 

 

随机提取10条记录的例子：
SQL Server：Select Top 10 * From 表 Order By NewID()

Access：Select Top 10 * From 表 Order By Rnd(ID)  
Rnd(ID) 其中的ID是自动编号字段，可以利用其他任何数值来完成，比如用姓名字段(UserName)
Select Top 10 * From 表 Order BY Rnd(Len(UserName))

MySql：Select * From 表 Order By Rand() Limit 10
 

14. case...when...else...end   (http://lavasoft.blog.51cto.com/62575/23373)

update e_ztbg set jddxh=(case isnull(jddxh,'') when '' then isnull(jddxh,'')+'0001-080001' else isnull(jddxh,'')+','+'0001-080001' end)

where lsh='0001-080001'

15 

select replace('hello','e','xxx')