Sql,根据表名查询的信息：Type，Length，Name，ISidentity，isPK，isFK...
0
[已解决问题]
解决于 2011-09-13 19:53
Sql语句查询
我想在一个查询语句中查出以下信息：
要求：
根据表名查询的信息：
类型，长度，字段名，是否自动编号列，是否为主键，是否为外键：若是外键，要查询它的外键表，主键，和表名
最佳答案
0
你自己根据需要增减：
1 SELECT
2 表名 =casewhen a.colorder=1then d.name else''end,
3 表说明 =casewhen a.colorder=1thenisnull(f.value,'') else''end,
4 字段序号 = a.colorder,
5 字段名 = a.name,
6 标识 =casewhenCOLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then'√'else''end,
7 主键 =casewhenexists(SELECT1FROM sysobjects where xtype='PK'and parent_obj=a.id and name in (
8 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then'√'else''end,
9 外键 =casewhenexists(SELECT1FROM sysforeignkeys WHERE sysforeignkeys.fkeyid = d.id and sysforeignkeys.fkey=a.colid) then'√'else''end,
10 类型 = b.name,
11 占用字节数 = a.length,
12 长度 =COLUMNPROPERTY(a.id,a.name,'PRECISION'),
13 小数位数 =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
14 允许空 =casewhen a.isnullable=1then'√'else''end,
15 默认值 =isnull(e.text,''),
16 字段说明 =isnull(g.[value],'')
17 FROM
18 syscolumns a
19 leftjoin
20 systypes b
21 on
22 a.xusertype=b.xusertype
23 innerjoin
24 sysobjects d
25 on
26 a.id=d.id and d.xtype='U'and d.name<>'dtproperties'
27 leftjoin
28 syscomments e
29 on
30 a.cdefault=e.id
31 leftjoin
32 sys.extended_properties g
33 on
34 a.id=G.major_id and a.colid=g.minor_id
35 leftjoin
36
37 sys.extended_properties f
38 on
39 d.id=f.major_id and f.minor_id=0
40 where
41 d.name='SC'--这里为要查询的表名
42 orderby
43 a.id,a.colorder
2 表名 =casewhen a.colorder=1then d.name else''end,
3 表说明 =casewhen a.colorder=1thenisnull(f.value,'') else''end,
4 字段序号 = a.colorder,
5 字段名 = a.name,
6 标识 =casewhenCOLUMNPROPERTY( a.id,a.name,'IsIdentity')=1then'√'else''end,
7 主键 =casewhenexists(SELECT1FROM sysobjects where xtype='PK'and parent_obj=a.id and name in (
8 SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then'√'else''end,
9 外键 =casewhenexists(SELECT1FROM sysforeignkeys WHERE sysforeignkeys.fkeyid = d.id and sysforeignkeys.fkey=a.colid) then'√'else''end,
10 类型 = b.name,
11 占用字节数 = a.length,
12 长度 =COLUMNPROPERTY(a.id,a.name,'PRECISION'),
13 小数位数 =isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
14 允许空 =casewhen a.isnullable=1then'√'else''end,
15 默认值 =isnull(e.text,''),
16 字段说明 =isnull(g.[value],'')
17 FROM
18 syscolumns a
19 leftjoin
20 systypes b
21 on
22 a.xusertype=b.xusertype
23 innerjoin
24 sysobjects d
25 on
26 a.id=d.id and d.xtype='U'and d.name<>'dtproperties'
27 leftjoin
28 syscomments e
29 on
30 a.cdefault=e.id
31 leftjoin
32 sys.extended_properties g
33 on
34 a.id=G.major_id and a.colid=g.minor_id
35 leftjoin
36
37 sys.extended_properties f
38 on
39 d.id=f.major_id and f.minor_id=0
40 where
41 d.name='SC'--这里为要查询的表名
42 orderby
43 a.id,a.colorder
根据外键找表名：
1 --返回表名
2 select name from sysobjects where xtype='U'and id in
3 (
4 select id from syscolumns where name ='sno'--这里为外键字段名
5 and id in(select rkeyid from sysforeignkeys)
6 )
2 select name from sysobjects where xtype='U'and id in
3 (
4 select id from syscolumns where name ='sno'--这里为外键字段名
5 and id in(select rkeyid from sysforeignkeys)
6 )
根据得到的表名再用第一段SQL语句查询其他所需信息
根据外键找表名：代码有点问题啊！~
我有两个外键有一个外键可以查询表名，
但另外一个无法查询到表名！
楼上的第二个查询有时候会有问题的！~
比如，
select id from syscolumns where name ='sno'--这里为外键字段名
如果查询到2条记录或以上就没有问题，不过当只能查询到一条记录的时候，而id又不匹配的情况下，是外键都无法查询到相关信息。
我是经过自己测试的。若有什么问题，可以继续探讨一下....
我参考了别人写的SQL语句：改成如下代码，可以根据需要查询信息
1 SELECT
2 f.name AS ForeignKey,--外键编号
3 OBJECT_NAME(f.parent_object_id) AS TableName,--主表
4 COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,--主表列名
5 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,--外表名
6 COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName--外表主键
7 FROM sys.foreign_keys AS f
8 INNERJOIN sys.foreign_key_columns AS fc
9 ON f.OBJECT_ID= fc.constraint_object_id
10 whereOBJECT_NAME(f.parent_object_id)='c'--表名
11 andCOL_NAME(fc.parent_object_id,fc.parent_column_id)='bid'--外键名
2 f.name AS ForeignKey,--外键编号
3 OBJECT_NAME(f.parent_object_id) AS TableName,--主表
4 COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,--主表列名
5 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,--外表名
6 COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName--外表主键
7 FROM sys.foreign_keys AS f
8 INNERJOIN sys.foreign_key_columns AS fc
9 ON f.OBJECT_ID= fc.constraint_object_id
10 whereOBJECT_NAME(f.parent_object_id)='c'--表名
11 andCOL_NAME(fc.parent_object_id,fc.parent_column_id)='bid'--外键名
