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

来源：互联网发布：ubuntu桌面版当服务器编辑：程序博客网时间：2024/05/22 03:51

由于目前做的是一个数据库操作的一个简易类，涉及到如下查询语句，在此记录一下。
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

0 0