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)