Thanks for sharing. I took what you wrote and added some additional code to make it a bit more robust:

--

--

-- set @TableName before to use it

--

DECLARE @ObjectFilter VARCHAR(128), @TableName VARCHAR(100), @SchemaName VARCHAR(28);

SET @ObjectFilter = 'your_table_name'; --Can be in the form on [schema name].

SET @TableName = parsename( @ObjectFilter,1); -- Captures the unquoted table name

SET @SchemaName = parsename( @ObjectFilter,2); -- Captures the unquoted schema name; NULL is handled

select

cols.name

,cols.column_id

,cols.max_length as size

,cols.precision

,cols.scale

,cols.is_identity

,cols.is_nullable

,tipus.name as [type]

,domain.name as [user_type]

,(select key_ordinal

from sys.index_columns as ic

where

ic.object_id = (select parent_object_id

from sys.key_constraints

where type = 'PK'

and parent_object_id = cols.object_id)

and ic.index_id = (select unique_index_id

from sys.key_constraints

where type = 'PK'

and parent_object_id = cols.object_id)

and ic.column_id = cols.column_id) as pk_ordinal

from

sys.columns as cols

left join sys.types as tipus

on tipus.system_type_id = cols.system_type_id

and tipus.user_type_id = cols.system_type_id

and tipus.is_user_defined = 0

left join sys.types as domain

on domain.user_type_id = cols.user_type_id

and domain.is_user_defined = 1

where cols.object_id = (select object_id

from sys.tables

where name = @TableName

and (@SchemaName is null or object_schema_name(object_id) = @SchemaName)

)

order by cols.column_id