SQL Server常用语句

查询所有表名和行数

SELECT A.NAME ,B.ROWS FROM sysobjects A JOIN sysindexes B 
ON A.id = B.id WHERE A.xtype = 'U' AND B.indid IN(0,1) 
ORDER BY B.ROWS DESC;

 效果如下：

 获取所有存储过程信息
  SELECT * FROM sysobjects WHERE type = 'P' AND category = 0 ORDER BY name

多个case when判断

select *,
case when or_result = 1 
then N'合格'
when or_result = 2 
then N'合格2'
else 
N'不合格'
end as result
from OlSingleFastRecird

生成uuid

--56D21FD0-B731-4293-8240-7164C85E7E18
select cast(NEWID() as varchar(36)) as uuid


--select upper('abc') 将小写转换成大写
--select Lower('ABC') 将大写转换成小写
--4f1caeb8-52bd-4440-92c7-1a0336de004c
select Lower(cast(NEWID() as varchar(36))) as uuid

--生成一个不带“-”的UUID
select cast(REPLACE(NEWID(), '-', '') as varchar(32))

获取表字段详情

SELECT  
表名=case when a.colorder=1 then d.name else '' end, 
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder, 
字段名=a.name, 
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end, 
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
  SELECT name FROM sysindexes WHERE indid in(
   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid 
   ))) then '√' else '' end, 
类型=b.name, 
占用字节数=a.length, 
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'), 
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), 
允许空=case when a.isnullable=1 then '√'else '' end, 
默认值=isnull(e.text,''), 
字段说明=isnull(g.[value],'') 
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 sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id 
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0 
where d.name='glp_OlSingleFastRecird' --如果只查询指定表,加上此条件   
order by a.id,a.colorder 

@@IDENTITY

常用来获取当前页面最后生成的自增主键id（多用于编写存储过程,关联多表数据的变动）

存储过程

-- 查看所有存储过程

select  * from sys.all_objects order by name

-- 查看存储过程的参数情况

select '参数名称' = name, 
         '类型' = type_name(xusertype), 
         '长度' = length,    
         '参数顺序' = colid, 
         '排序方式' = collation 
   from    syscolumns 
   where   id=object_id('存储过程名称')



--查看所有存储过程内容 
   select   b.name   ,a.text   
from   syscomments   a,sysobjects   b   
where   object_id(b.name)=a.id   and   b.xtype   in('P','TR')


--查看包含字符串内容的存储过程 

select   b.name   ,a.text   from   syscomments   a,sysobjects   b 
where 
charindex('字符串内容',a.text)>0    and 
object_id(b.name)=a.id   and   b.xtype   in('P','TR')