如何查看所建立的表约束如:主键约束,Check约束等等
如何知道如下表的主键约束
Create table #temp(
No int primary key ,
Name char(10)
)
谢谢!
[解决办法]
sp_pkeys '表名 ' 获取主键信息
[解决办法]
寫錯了，是這個
Create table TEST(
No int primary key ,
Name char(10) CHECK(Name not LIKE '%[^0-9]% ')
)
GO
--获取某表的约束信息以及归属列
sp_helpconstraint 'TEST '
GO
Drop Table TEST
[解决办法]
查找check约束
SELECT a.tablename,a.columnname,
b.constid AS checkid, b.checkname, b.status, b.content
FROM (SELECT sysobjects.name AS tablename, sysobjects.id,
syscolumns.name AS columnname, syscolumns.colid,
systypes.name AS datatype, syscolumns.length AS length
FROM sysobjects, syscolumns, systypes
WHERE sysobjects.xtype = 'u ' AND sysobjects.id = syscolumns.id AND
syscolumns.xtype = systypes.xtype AND
systypes.xtype = systypes.xusertype )
a inner JOIN
(SELECT sysobjects.name checkname, sysobjects.status, sysconstraints.constid,
sysconstraints.id, sysconstraints.colid, syscomments.text AS content
FROM sysobjects, sysconstraints, syscomments
WHERE xtype = 'c ' AND sysobjects.id = sysconstraints.constid AND
sysconstraints.constid = syscomments.id) b ON a.id = b.id AND
a.colid = b.colid
ORDER BY a.tablename, a.columnname, b.checkname
