如何取主键字段名称及字段类型--得到主键字段名1:
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME<>'dtproperties'2:
EXEC sp_pkeys @table_name='表名'3:
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' and o.name='要查询的表名'
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
order by o.name,k.colid
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME<>'dtproperties'2:
EXEC sp_pkeys @table_name='表名'3:
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = 'U' and o.name='要查询的表名'
and exists(select 1 from sysobjects where xtype = 'PK' and parent_obj=i.id and name = i.name)
order by o.name,k.colid
解决方案 »
- 如何插入换行?
- 很急,大家帮忙看看这个触发器为什么没有执行呢?
- 与大家讨论一下,银行ATM取款机的系统,密码验证的问题.(纯技术性讨论,不是给银行挑毛病)
- 关于数据库所有者的迷惑
- 如何在fluidsim中通过PC Access和s7-200plc联接
- SQL排序问题
- mysql语句中怎么多个合并重复的行,并且求出合并的行里的某字段的和?
- 求救!高手请赐教!存储过程分页按某字段显示!!!
- 很奇怪的外主键问题,建立外主键后表无法设计。估计老鸟才能解决。
- 怎么把2002-6-14 下午 11:14:13 转化成2002-6-14 23:14:13请教
- sql2005 "sp_send_dbmail"发送邮件时密送功能不能用。
- 求每分组取前3条纪录的sql语句
Select ColOrder = col.colorder, --排序号
ColumnName = col.name, --列名
TypeName = type.name,--数据类型名称
Length = (Case When type.name='nvarchar' Or type.name='nchar' Then col.length/2 Else col.length End), --长度
[PRECISION] = COLUMNPROPERTY(col.id, col.name, 'PRECISION'), --精度
Scale = ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0), --小数
IsIdentity = Case When COLUMNPROPERTY(col.id, col.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 = col.id And colid = col.colid
)
)
) Then '√' Else '' End, --是否为主键
AllowNull = Case When col.isnullable=1 Then '√' Else '' End, --是否允许为空
DefalutValue = isnull(com.text, '') --默认值
From syscolumns col
Left Join systypes type On col.xtype = type.xusertype
Inner Join sysobjects obj On col.id = obj.id And (obj.xtype = 'U' Or obj.xtype = 'V') And obj.name <> 'dtproperties'
Left Join syscomments com On col.cdefault = com.id
Where obj.name = 'Territories'
这里面的方法和邹老师的思路是基本一样的,也出现了同样的问题,请老师帮忙~
syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN '' ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, 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 = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')
syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN '' ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, 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 = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')
1,看看你是不是对表上设置了联合主健.
2,blog里的文件是针对于库名的,可能你看到的结果是库下几个表的主健.期待老大出现,给出答案.
http://topic.csdn.net/u/20070625/10/490e3182-9c15-4a42-bf5e-6c99cafda258.html
其中有一处判断不太严谨,修改了一下,请zjcxc指正:SELECT CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(CONVERT(nvarchar, f.value), '')
ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE parent_obj = object_id('MyTab') and /*!!!只增加此行!!!*/
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 AS 主键, b.name AS 类型, a.length AS 占用字节数,
......
那个帖子的完整代码为:
SELECT CASE WHEN a.colorder = 1 THEN d .name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(CONVERT(nvarchar, f.value), '')
ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE parent_obj = object_id(d .name) AND 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 AS 主键, b.name AS 类型, a.length AS 占用字节数,
COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度,
ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(CONVERT(nvarchar, g.[value]), '') AS 字段说明
FROM syscolumns a LEFT OUTER JOIN
systypes b ON a.xusertype = b.xusertype INNER JOIN
sysobjects d ON a.id = d .id AND d .xtype = 'U' AND
d .name <> 'dtproperties' LEFT OUTER JOIN
syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN
sysproperties f ON d .id = f.id AND f.smallid = 0
ORDER BY d .name, a.colorder
CASE WHEN a.colorder = 1 THEN isnull(CONVERT(nvarchar, f.value), '')
ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名,
CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE parent_obj = object_id('MyTab') and /*!!!只增加此行!!!*/
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 AS 主键, b.name AS 类型, a.length AS 占用字节数,
......
改成
WHERE parent_obj = d.id AND xtype = 'PK' AND name IN
好一些, 少用一层函数
SELECT GETDATE()
/*
2009-08-24 22:33:54.717
*/