Common SQL statements

1. Querying the database table structure

Select (case if A.colorder=1 then d.name else null end) [table name], A.colorder [ordinal], a.name [field name], (Case is ColumnProperty (a.id,a.name, ' isidentity ') =1 then ' √ ' Else ' end)                   [Identity], (select COUNT (id) from sysobjects where (the name in (select name From sysindexes where (id = a.id) and (in                                                         Did in (select Indid from Sysindexkeys WHERE (id = a.id) and (Colid in (sel                                                                           ECT colid from syscolumns                                                                             WHERE (id = a.id)                      (name = A.name)))))  and (xtype = ' PK ')) >0 then ' √ ' Else ' end ' [primary key], b.name [type], A.length [bytes occupied] , ColumnProperty (a.id,a.name, ' PRECISION ') [length], IsNull (ColumnProperty (a.id,a.name, ' scale '), 0) [Decimal place], (c ASE when a.isnullable=1 then ' √ ' else ' end] [allow null], IsNull (E.text, ') [default], IsNull (G.[value], ') [remarks] from   Syscolumns a left joins systypes B on A.xtype=b.xusertype inner joins sysobjects D on A.id=d.id and d.xtype= ' U ' and D.name not in (' dtproperties ', ' Sysdiagrams ') left joins syscomments E on a.cdefault=e.id left join SYS.EXTENDED_PR Operties g on a.id=g.major_id and a.colid=g.minor_id left joins Sys.extended_properties F on D.id=f.class and F.minor_ Id=0where B.name is not a null order by D.name--, A.id,a.colorder

Common SQL statements