| SELECT colorder=a.colorder,--列号 ColumnName=a.name, --列名称 TypeName=b.name, --列类型 Length=(case when b.name='nvarchar' then a.length/2 else a.length end), --列长度 Preci=COLUMNPROPERTY(a.id,a.name,'PRECISION'), --列精度 Scale=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0), --刻度 IsIdentity=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end, --是否自动增长 isPK=case when exists(SELECT 1 FROM sysobjects where xtype='PK' 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, --是否是主键 cisNull=case when a.isnullable=1 then '√'else '' end, --是否允许为空 defaultVal=isnull(e.text,''), --默认值 deText=isnull(g.[value],'') --注释文字 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' or d.xtype='V') and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id and a.colid=g.smallid where d.name='OrderLog' --这里是表的名称 order by a.id,a.colorder |
二。Sql2005得到表字段详细信息。
| SELECT colorder=C.column_id, ColumnName=C.name, TypeName=T.name, Length=C.max_length, Preci=C.precision, Scale=C.scale, IsIdentity=CASE WHEN C.is_identity=1 THEN N'\u221a'ELSE N'' END, isPK=ISNULL(IDX.PrimaryKey,N''), Computed=CASE WHEN C.is_computed=1 THEN N'\u221a'ELSE N'' END, IndexName=ISNULL(IDX.IndexName,N''), IndexSort=ISNULL(IDX.Sort,N''), Create_Date=O.Create_Date, Modify_Date=O.Modify_date, cisNull=CASE WHEN C.is_nullable=1 THEN N'\u221a'ELSE N'' END, defaultVal=ISNULL(D.definition,N''), deText=ISNULL(PFD.[value],N'') FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id]=O.[object_id] AND O.type='U' AND O.is_ms_shipped=0 INNER JOIN sys.types T ON C.user_type_id=T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id]=D.parent_object_id AND C.column_id=D.parent_column_id AND C.default_object_id=D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class=1 AND C.[object_id]=PFD.major_id AND C.column_id=PFD.minor_id LEFT JOIN sys.extended_properties PTB ON PTB.class=1 AND PTB.minor_id=0 AND C.[object_id]=PTB.major_id LEFT JOIN ( SELECT IDXC.[object_id], IDXC.column_id, Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END, PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'\u221a'ELSE N'' END, IndexName=IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id]=IDXC.[object_id] AND IDX.index_id=IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id]=KC.[parent_object_id] AND IDX.index_id=KC.unique_index_id INNER JOIN ( SELECT [object_id], Column_id, index_id=MIN(index_id) FROM sys.index_columns GROUP BY [object_id], Column_id ) IDXCUQ ON IDXC.[object_id]=IDXCUQ.[object_id] AND IDXC.Column_id=IDXCUQ.Column_id AND IDXC.index_id=IDXCUQ.index_id ) IDX ON C.[object_id]=IDX.[object_id] AND C.column_id=IDX.column_id WHERE O.name=N'" + TableName + "' --这里是表的名称 ORDER BY O.name,C.column_id |
三。Oracle得到表字段详细信息。
| select COLUMN_ID as colorder, COLUMN_NAME as ColumnName, DATA_TYPE as TypeName, DATA_LENGTH as Length, DATA_PRECISION as Preci, DATA_SCALE as Scale, '' as IsIdentity, '' as isPK, "NULLABLE as cisNull , DATA_DEFAULT as defaultVal, '' as deText from USER_TAB_COLUMNS where TABLE_NAME='" + TableName + "' //这里是表名称 order by COLUMN_ID |
四。其他的数据库的表字段结构的详细。
| public DataTable GetOleDbSchemaTable(Guid schema, object[] restrictions) case "5": case "6": case "7": case "11": case "130": |