marc_s's answer is good but it has a flaw if the primary key column(s) appear in other indexes in that those columns will appear more than once. e.g.
Demo:
create table dbo.DummyTable
(
id int not null identity(0,1) primary key,
Msg varchar(80) null
);
create index NC_DummyTable_id ON DummyTable(id);
Here's my stored procedure to solve problem:
create or alter procedure dbo.GetTableColumns
(
@schemaname nvarchar(128),
@tablename nvarchar(128)
)
AS
BEGIN
SET NOCOUNT ON;
with ctePKCols as
(
select
i.object_id,
ic.column_id
from
sys.indexes i
join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
where
i.is_primary_key = 1
)
SELECT
c.name AS column_name,
t.name AS typename,
c.max_length AS MaxLength,
c.precision,
c.scale,
c.is_nullable,
is_primary_key = CASE WHEN ct.column_id IS NOT NULL THEN 1 ELSE 0 END
FROM
sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
LEFT JOIN ctePKCols ct ON ct.column_id = c.column_id AND ct.object_id = c.object_id
WHERE
c.object_ID = OBJECT_ID(quotename(@schemaname) + '.' + quotename(@tablename))
END
GO
exec dbo.GetTableColumns 'dbo', 'DummyTable'