Building Schneider Building Control system Database Background Server example project four (SQL Server query statement)

Since what is currently done is a simple class of database operations, it involves the following query statements, which are recorded here.
1. Query table name

select object_id,namenamefrom sys.tables

2. Querying column information

SELECT( Case          whenA.colorder =1  ThenD.nameElse          NULL       End) Table name, A.colorder field ordinal, a.name field name, ( Case          whenColumnProperty (a.ID, A.name,' isidentity ') =1  Then          ' √ '         Else          "'       End) Identification, ( Case          when(SELECT Count(*) fromsysobjectsWHERE(Nameinch(SELECTName fromsysindexesWHERE(id = a.id) and(indidinch(SELECTIndid fromSysindexkeysWHERE(id = a.id) and(colidinch(SELECTColid fromsyscolumnsWHERE(id = a.id) and(name = A.name)))))) and(Xtype =' PK ')) >0  Then          ' √ '         Else          "'       End) primary key, B.name type, A.length takes up bytes, ColumnProperty (a.id, A.name,' PRECISION ') asLength, IsNull (ColumnProperty (a.id, A.name,' scale '),0) asNumber of decimal digits, ( Case          whenA.isnullable =1  Then          ' √ '         Else          "'       End) allow null, IsNull (E.text,"') default value, IsNull (g.value,"') asDescription fromSyscolumns A Left JoinSystypes b onA.xtype = B.xusertypeInner Joinsysobjects D ona.ID = D.id andD.xtype =' U '    andD.name <>' dtproperties '   Left Joinsyscomments E onA.cdefault = e.id Left JoinSys.extended_properties g ona.ID = g.major_id andA.colid = g.minor_id Left JoinSys.extended_properties F onD.id = F.class andf.minor_id =0 whereB.name is  not NULL    andD.name =?Order  bya.ID, A.colorder;

3. Paging Query

select *  from (select row_number() over(orderbyas rownumber,*          fromeventwhere0and10

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Building Schneider Building Control system Database Background Server example project four (SQL Server query statement)