1 SELECT( Case whenA.colorder=1 ThenD.nameElse NULL End) Table name,2 a.colorder field ordinal, a.name field name,3( Case when ColumnProperty(A.id,a.name,'isidentity')=1 Then '√'Else "' End) identification,4( Case when(SELECT Count(*) fromsysobjects5 WHERE(Nameinch(SELECTName fromsysindexes6 WHERE(ID=a.ID) and(indidinch 7(SELECTIndid fromSysindexkeys8 WHERE(ID=a.ID) and(colidinch 9(SELECTColid fromsyscolumnsWHERE(ID=a.ID) and(Name=( a.name) )))))Ten and(xtype= 'PK'))>0 Then '√' Else "' End) primary key, B.name type, A.length takes up the number of bytes, One ColumnProperty(A.id,a.name,'PRECISION') aslength, A IsNull(ColumnProperty(A.id,a.name,' Scale'),0) asNumber of decimal digits, ( Case whenA.isnullable=1 Then '√'Else "' End) allows null, - IsNull(E.text,"') Default value,IsNull(g.[value],' ') as [Description] - fromsyscolumns a the Left JoinSystypes b onA.xtype=B.xusertype - Inner 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 + --where d.name= ' table to query '--if you only query the specified table, add this condition A Order byA.id,a.colorder
Get all database table names:
SELECT name from SysObjects Where xtype= ' U ' ORDER by name
SQL Server obtains all table structures (including table names and fields)