構建施耐德樓控系統資料庫後台伺服器樣本工程四（SQLServer查詢語句）

標籤：sql server

由於目前做的是一個資料庫操作的一個簡易類，涉及到如下查詢語句，在此記錄一下。
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

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。

構建施耐德樓控系統資料庫後台伺服器樣本工程四（SQLServer查詢語句）