查询表的所有字段属性及其是否是主外键

CREATE PROC [dbo].[sp_help2]
@TableName VARCHAR(50) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET XACT_ABORT ON


   DECLARE @ErrCode nvarchar(100) = '', @ErrParam  nvarchar(1000) = ''
, @ProcName nvarchar(200) = OBJECT_NAME(@@PROCID)
  


BEGIN TRY


select 
b.name as ColumnName,
--CN_Name = (select Top(1) China from Dictionary dic where dic.Eng = b.name),
f.name AS Column_Type,
b.max_length as [Length],
CASE b.is_nullable WHEN 1 THEN 'Y' ELSE 'N' END as Nullable,
case when pktable.column_id is not null THEN 'O' ELSE '' END AS IS_PK,
case when d.object_id is not null THEN 'O' ELSE ''END AS IS_FK,
isnull(d.name,'') as RefTableName,
isnull(e.name,'') as RefColumnName
from sys.tables a
join sys.columns b on a.object_id = b.object_id
left join sys.foreign_key_columns c on a.object_id = c.parent_object_id
and b.column_id = c.parent_column_id
left join sys.tables d on c.referenced_object_id = d.object_id
left join sys.columns e on e.object_id = c.referenced_object_id
and e.column_id = c.referenced_column_id
join sys.types f on f.user_type_id = b.user_type_idand f.user_type_id = f.system_type_id
left join 
(
select b.object_id,c.column_id 
from sys.index_columns a
join sys.objects b on a.object_id = b.object_id
join sys.columns c on b.object_id = c.object_id 
and c.column_id = a.column_id
join sys.key_constraints d on a.object_id = d.parent_object_id
and d.unique_index_id = a.index_id
and d.type = 'PK'
) pktable on pktable.object_id = a.object_id
and pktable.column_id = b.column_id
where a.object_id = OBJECT_ID(@TableName)
order by b.column_id




END TRY
BEGIN CATCH






END CATCH