SQL语句,适用于SQL:
SELECT d.name N'tablename',a.colorder N'fielidno',a.name N'field',(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end) N'bs',(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '' end) N'master',b.name N'datatype',a.length N'datawidth',COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'width',isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'point',(case when a.isnullable=1 then '1'else '0' end) N'nvnull',isnull(g.[value],'') AS N'remake'into spd FROM syscolumns a left join systypes b on a.xtype=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 sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder
SELECT d.name N'tablename',a.colorder N'fielidno',a.name N'field',(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '' end) N'bs',(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '1' else '' end) N'master',b.name N'datatype',a.length N'datawidth',COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'width',isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'point',(case when a.isnullable=1 then '1'else '0' end) N'nvnull',isnull(g.[value],'') AS N'remake'into spd FROM syscolumns a left join systypes b on a.xtype=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 sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder
string sql="SELECT c.name as FieldName,t.name as FieldType, c.length as FieldLength FROM SYSCOLUMNS c inner join systypes t on c.xusertype=t.xusertype WHERE c.ID = OBJECT_ID('"+tableName+"')";
http://www.cnblogs.com/dahuzizyd/archive/2004/07/20/25844.aspx
如果根据DATASET中DATATABLE 获得的结构和数据库本身对不上!除了用SQL查找结构以外还有别的办法获得数据库及其中表的结构吗?
SELECT * FROM sysobjects WHERE type = 'u'
然后就可以根据表名查询出表内的字段名,类型,长度了。
SELECT c.name as FieldName,t.name as FieldType, c.length as FieldLength FROM SYSCOLUMNS c inner join systypes t on c.xusertype=t.xusertype WHERE c.ID = OBJECT_ID(‘tableName’)