SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'
什么相当于 SQL Server 的上述查询?
如果有一个查询适用于 MySQL 和 SQL Server 那么这将是一个理想的情况 .
10 回答
4
This should list all the constraints and at the end you can put your filters
/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='' ,
REFERENCE_COL_NAME=''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE='FK',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in ('YOUR_TABLE_NAME')
or REFERENCE_TABLE_NAME in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;
select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE
where CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME='TABLENAME'
AND TABLE_SCHEMA='DATABASENAME'
55
我还发现另一个SQL Server:
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'
6
SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'
WHERE =来自服务器的数据库和 =您要从中查看主键的表名 . 注意:输入您的数据库名称和表名称不带括号 .
136
使用SQL SERVER 2005,您可以尝试
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i INNER JOIN
sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
SELECT KU.table_name as TABLENAME,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name='yourTableName'
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;
我在SQL Server 2003/2005上测试了这个
3
select *
from sysobjects
where xtype='pk' and
parent_obj in (select id from sysobjects where name='tablename')
这将在sql 2005中工作
9
我给你的代码不仅可以工作,也可以检索SQL Server中的表中的大量数据 . 在SQL Server 2k5 / 2k8中测试,dunno大约2k . 请享用!
SELECT DISTINCT
sys.tables.object_id AS TableId,
sys.columns.column_id AS ColumnId,
sys.columns.name AS ColumnName,
sys.types.name AS TypeName,
sys.columns.precision AS NumericPrecision,
sys.columns.scale AS NumericScale,
sys.columns.is_nullable AS IsNullable,
( SELECT
COUNT(column_name)
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE
TABLE_NAME = sys.tables.name AND
CONSTRAINT_NAME =
( SELECT
constraint_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = sys.tables.name AND
constraint_type = 'PRIMARY KEY' AND
COLUMN_NAME = sys.columns.name
)
) AS IsPrimaryKey,
sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM
sys.columns, sys.types, sys.tables
WHERE
sys.tables.object_id = sys.columns.object_id AND
sys.types.system_type_id = sys.columns.system_type_id AND
sys.types.user_type_id = sys.columns.user_type_id AND
sys.tables.name = 'TABLE'
ORDER BY
IsPrimaryKey
10 回答
This should list all the constraints and at the end you can put your filters
如需参考,请阅读 - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
请记住,如果要获得准确的主要字段,则需要将TABLE_NAME和TABLE_SCHEMA置于条件中 .
此解决方案应该工作:
我还发现另一个SQL Server:
使用SQL SERVER 2005,您可以尝试
发现于SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
从记忆中,它就是这个
或这个..
我认为其中一个应该可以工作,具体取决于数据的存储方式,但我担心我无法访问SQL来实际验证相同的数据 .
找到另一个:
我在SQL Server 2003/2005上测试了这个
这将在sql 2005中工作
我给你的代码不仅可以工作,也可以检索SQL Server中的表中的大量数据 . 在SQL Server 2k5 / 2k8中测试,dunno大约2k . 请享用!
您只能使用主键部分,但我认为其余部分可能会变得很方便 . 最好的问候,大卫
它也是(Transact-SQL)......根据BOL .