查询数据库表和字段sql语句
今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句，很强! 就是忘了当初谁写的了，印像中该是邹建所创。也贴出来以后备用吧。
1
SELECT
2
表名 = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
3
序 = a.colorder,
4
字段名 = a.name,
5
标识 = CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,
6
主键 = CASE
7
WHEN EXISTS (
8
SELECT *
9
FROM sysobjects
10
WHERE xtype='PK' AND name IN (
11
SELECT name
12
FROM sysindexes
13
WHERE id=a.id AND indid IN (
14
SELECT indid
15
FROM sysindexkeys
16
WHERE id=a.id AND colid IN (
17
SELECT colid
18
FROM syscolumns
19
WHERE id=a.id AND name=a.name
20
)
21
)
22
)
23
)
24
THEN '√'
25
ELSE ''
26
END,
27
类型 = b.name,
28
字节数 = a.length,
29
长度 = COLUMNPROPERTY(a.id,a.name,'Precision'),
30
小数 = CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
31
WHEN 0 THEN ''
32
ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)
33
END,
34
允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
35
默认值 = ISNULL(d.[text],''),
36
说明 = ISNULL(e.[value],'')
37
FROM syscolumns a
38
LEFT JOIN systypes b ON a.xtype=b.xusertype
39
INNER JOIN sysobjects c ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'
40
LEFT JOIN syscomments d ON a.cdefault=d.id
41
LEFT JOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid
42
ORDER BY c.name, a.colorder
我修改一下，变个精简版本的：
1
2select a.name, b.xtype,b.name
3from syscolumns a
4inner JOIN systypes b
5ON a.xtype=b.xusertype
6inner join sysobjects c ON
7a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties' where c.name = 表名
http://ring1981.cnblogs.com/archive/2006/06/22/432857.html
免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
