构建施耐德楼控系统数据库后台服务器示例工程四（SQLServer查询语句）

冷不防 2022-08-03 14:44 104阅读 0赞

由于目前做的是一个数据库操作的一个简易类，涉及到如下查询语句，在此记录一下。  
1.查询表名

select object_id,name name from sys.tables

2.查询列信息

SELECT (case when a.colorder = 1 then d.name else null end) 表名, a.colorder 字段序号, a.name 字段名, (case when COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 then '√' else '' end) 标识, (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) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) as 小数位数, (case when a.isnullable = 1 then '√' else '' end) 允许空, isnull(e.text, '') 默认值, isnull(g. value, ' ') AS 说明 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.class and f.minor_id = 0 where b.name is not null and d.name = ? order by a.id, a.colorder;

3.分页查询

select *
      from (select row_number() over(order by AlarmPriority) as rownumber,*
              from event) A
     where rownumber BETWEEN 0 and 10