sqlserver数据库里怎么查找一个表里的约束,要查出 约束名、列名、约束类型?用sql语句查询
5个回答
展开全部
主键约束
SELECT
tab.name AS [表名],
idx.name AS [主键名称],
col.name AS [主键列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_primary_key = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
唯一约束
SELECT
tab.name AS [表名],
idx.name AS [约束名称],
col.name AS [约束列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_unique_constraint = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
外键约束
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
Check约束
SELECT
tab.name AS [表名],
chk.name AS [Check约束名],
col.name AS [列名],
chk.definition
FROM
sys.check_constraints chk
JOIN sys.tables tab
ON (chk.parent_object_id = tab.object_id)
JOIN sys.columns col
ON (chk.parent_object_id = col.object_id
AND chk.parent_column_id = col.column_id)
SELECT
tab.name AS [表名],
idx.name AS [主键名称],
col.name AS [主键列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_primary_key = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
唯一约束
SELECT
tab.name AS [表名],
idx.name AS [约束名称],
col.name AS [约束列名]
FROM
sys.indexes idx
JOIN sys.index_columns idxCol
ON (idx.object_id = idxCol.object_id
AND idx.index_id = idxCol.index_id
AND idx.is_unique_constraint = 1)
JOIN sys.tables tab
ON (idx.object_id = tab.object_id)
JOIN sys.columns col
ON (idx.object_id = col.object_id
AND idxCol.column_id = col.column_id);
外键约束
select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
Check约束
SELECT
tab.name AS [表名],
chk.name AS [Check约束名],
col.name AS [列名],
chk.definition
FROM
sys.check_constraints chk
JOIN sys.tables tab
ON (chk.parent_object_id = tab.object_id)
JOIN sys.columns col
ON (chk.parent_object_id = col.object_id
AND chk.parent_column_id = col.column_id)
追问
执行查找主键约束sql语句,提示:对象名 'sys.indexes' 无效。对象名 'sys.index_columns' 无效。对象名 'sys.tables' 无效。对象名 'sys.columns' 无效。是怎么回事?我用的是sqlserver2000,在sqlserver2000版本下sql语句怎么写?
追答
上面的 SQL 都是在 SQL Server 2005/2008 下测试过的。
SQL Server 2000 应该是用不了的。 因为很多视图是 SQL 2005 版本才新增的。
本机无 SQL Server 2000 版本。
就等等别人回答吧。
展开全部
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
查询出用户自建表约束
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
查询出用户自建表约束
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
执行 :EXEC sp_help 表名 或者按快捷键 alt+F1 可以看到表中的所有信息,当然包括约束。
追问
我如果获取索引用“sp_helpindex 表名 ”;用这个存储过程获得结果集时报错:java.lang.NullPointerException 是执行到 while (rs.next()) 时出错。我的代码:
public void getIndexs(String tablename) throws SQLException {
String sql = "sp_helpindex " + tablename;
ResultSet rs = executeQuery(sql);
List indexs = new ArrayList();
while (rs.next()){}
}
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
上面的各楼对SQLSERVER的系统对象操作的语句,要切换到master数据库中。
切记切记 。
切记切记 。
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
展开全部
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
追问
我执行这段sql语句,提示:'SCHEMA_NAME' 不是可以识别的 函数名。
把SCHEMA_NAME(schema_id) AS SchemaName, 去掉在执行,提示对象名 'sys.objects' 无效。这是怎么回事?你们用的是sqlserver 那个版本?我的是sqlserver2000
已赞过
已踩过<
评论
收起
你对这个回答的评价是?
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询