学步园

SQL SERVER 2000：

SELECT</p> <p>表名=case when a.colorder=1 then d.name else '' end,</p> <p>表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,</p> <p>字段序号=a.colorder,</p> <p>字段名=a.name,</p> <p>标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,</p> <p>主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (</p> <p>SELECT name FROM sysindexes WHERE indid in(</p> <p>SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid</p> <p>))) then '√' else '' end,</p> <p>类型=b.name,</p> <p>占用字节数=a.length,</p> <p>长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),</p> <p>小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),</p> <p>允许空=case when a.isnullable=1 then '√'else '' end,</p> <p>默认值=isnull(e.text,''),</p> <p>字段说明=isnull(g.[value],'')</p> <p>FROM syscolumns a</p> <p>left join systypes b on a.xusertype=b.xusertype</p> <p>inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'</p> <p>left join syscomments e on a.cdefault=e.id</p> <p>left join sysproperties g on a.id=g.id and a.colid=g.smallid</p> <p>left join sysproperties f on d.id=f.id and f.smallid=0</p> <p>where d.name='DF_User' --在这里改为你的数据库名称</p> <p>order by a.id,a.colorder<br />

SQL SERVER 2005：

SELECT 序 = a.colorder,<br /> 字段名 = a.name,<br /> 标识 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,<br /> 主键 = CASE<br /> WHEN EXISTS ( SELECT *<br /> FROM sysobjects<br /> WHERE xtype='PK'<br /> AND name IN (SELECT [name]<br /> FROM sysindexes<br /> WHERE id=a.id<br /> AND indid IN (SELECT indid<br /> FROM sysindexkeys<br /> WHERE id=a.id<br /> AND colid IN (SELECT colid<br /> FROM syscolumns<br /> WHERE id=a.id<br /> AND name=a.name))))<br /> THEN '√' ELSE '' END,<br /> 类型 = b.name,<br /> 字节数 = a.length,<br /> 长度 = COLUMNPROPERTY(a.id,a.name,'Precision'),<br /> 小数 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0) WHEN 0 THEN '' ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR) END,<br /> 允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,<br /> 默认值 = ISNULL(d.[text],''),<br /> 说明 = ISNULL(e.[value],'')<br /> FROM syscolumns a<br /> LEFT JOIN systypes b ON a.xtype=b.xusertype<br /> INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'<br /> LEFT JOIN syscomments d ON a.cdefault=d.id<br /> LEFT JOIN sys.extended_properties e ON a.id=e.major_id AND a.colid=e.minor_id<br /> WHERE c.name='DifficultLevel'--在这里改为你的数据库名称<br /> ORDER BY c.name, a.colorder<br />