列出Database所有Key列或者获取表主键名称
从SQL Server的视图INFORMATION_SCHEMA 中用INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 和INFORMATION_SCHEMA.TABLE_CONSTRAINTS两个视图INNER JOIN在一起，可以列出需要的数据，下面Insus.NET把它写成一个自定义函数：
udf_KeyColumns
CREATE FUNCTION [dbo].[udf_KeyColumns]
(
)
RETURNS TABLE
AS
RETURN
(
SELECT kcu.[TABLE_NAME],kcu.[COLUMN_NAME], kcu.[ORDINAL_POSITION],tc.[CONSTRAINT_TYPE]
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.table_constraints AS tc ON
(kcu.table_name = tc.table_name AND kcu.[constraint_name] = kcu.[constraint_name])
)
(
)
RETURNS TABLE
AS
RETURN
(
SELECT kcu.[TABLE_NAME],kcu.[COLUMN_NAME], kcu.[ORDINAL_POSITION],tc.[CONSTRAINT_TYPE]
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.table_constraints AS tc ON
(kcu.table_name = tc.table_name AND kcu.[constraint_name] = kcu.[constraint_name])
)
如果我们想获取某一个表的主键名称，可以写一个存储过程：
usp_PrimaryKey
CREATE PROCEDURE [dbo].[usp_PrimaryKey] (
@TableName SYSNAME
)
AS
DECLARE @ReturnValue NVARCHAR(MAX)
SELECT @ReturnValue = [COLUMN_NAME] FROM [dbo].[udf_KeyColumns]()
WHERE [CONSTRAINT_TYPE] = 'PRIMARY KEY' AND [TABLE_NAME] = @TableName
SELECT @ReturnValue
@TableName SYSNAME
)
AS
DECLARE @ReturnValue NVARCHAR(MAX)
SELECT @ReturnValue = [COLUMN_NAME] FROM [dbo].[udf_KeyColumns]()
WHERE [CONSTRAINT_TYPE] = 'PRIMARY KEY' AND [TABLE_NAME] = @TableName
SELECT @ReturnValue
引用存储过程：
EXECUTE [dbo].[usp_PrimaryKey] 'Member'
参考相关：
SQL Server自定义函数(Scalar-valued Functions)
SQL Server自定义函数(Table-valued Functions)
