Common SQL statements and SQL statements

Common SQL statements and SQL statements

1. query the database table structure

Select (case when. colorder = 1 then d. name else null end) [Table name],. colorder [serial number],. name [field name], (case when columnproperty (. id,. name, 'isidentity ') = 1 then' √ 'else' end) [id], (case when (select count (id) from sysobjects where (name in (select name from sysindexes where (id =. id) and (indid in (select indid from sysindexkeys where (id =. id) and (colid in (select colid from syscolumns where (id =. id) and (name =. name) and (xtype = 'pk')> 0 then' √ 'else' end) [primary key], B. name [type],. length [number of bytes occupied], columnproperty (. id,. name, 'precision ') [length], isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) [decimal places], (case when. isnullable = 1 then' √ 'else' end) [null allowed], isnull (e. text, '') [default value], isnull (g. [value], '') [Remarks] from syscolumns a left join policypes B on. xtype = B. xusertype inner join sysobjects d on. id = d. id and d. xtype = 'U' and d. name not in ('dtproperties', 'sysdiagrams ') left join syscomments e on. cdefault = e. id left join sys. extended_properties g on. id = g. major_id and. 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 order by d. name --,. id,. colorder