获取SQL Server 数据库结构

SELECT
     表名       = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END,
     表说明     = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE,'') ELSE '' END,
     字段序号   = A.COLORDER,
     字段名     = A.NAME,
     字段说明   = ISNULL(G.VALUE,''),
     标识       = CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,'IsIdentity') = 1 THEN '是'ELSE '否' END,
     主键       = CASE WHEN EXISTS(
					SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK' AND PARENT_OBJ = A.ID AND NAME IN (
                      SELECT NAME FROM SYSINDEXES WHERE INDID IN(SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID = A.COLID)
					)
				  ) THEN '是' ELSE '否' END,
     类型       = B.NAME,
     占用字节数 = A.LENGTH,
     长度       = COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'),
     小数位数   = ISNULL(COLUMNPROPERTY(A.ID,A.NAME,'SCALE'),0),
     允许空     = CASE WHEN A.ISNULLABLE = 1 THEN '是'ELSE '否' END,
     默认值     = ISNULL(E.TEXT,'')
 FROM SYSCOLUMNS A
 LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE 
 INNER JOIN SYSOBJECTS D ON A.ID = D.ID  AND D.XTYPE = 'U' AND  D.NAME <> 'dtproperties'
 LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT = E.ID
 LEFT JOIN SYS.EXTENDED_PROPERTIES G ON A.ID = G.MAJOR_ID AND A.COLID = G.MAJOR_ID
 LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID = F.MAJOR_ID AND F.MAJOR_ID = 0 --where d.name='OrderInfo'    --如果只查询指定表,加上此条件
 Order By
 A.ID,
 A.COLORDER