List all the fields of a table with its properties + PK

Question

Post reply

List all the fields of a table with its properties + PK

jmagnet

SSC Enthusiast

Points: 184
More actions
May 4, 2014 at 3:00 am

#286460

Comments posted to this topic are about the item List all the fields of a table with its properties + PK

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

janis.l.murphy SSC Veteran Points: 239 More actions · Answer 1

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

jmagnet SSC Enthusiast Points: 184 More actions · Answer 2

Hi janis.l.murphy

I've modified the script as you suggested.

Thanks for your work.

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 3

Good Script..

It somewhat works like select the table and press alt+f1 .

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

jmagnet SSC Enthusiast Points: 184 More actions · Answer 4

vimal.lohani (8/5/2014)
Good Script..
It somewhat works like select the table and press alt+f1 .

Thanks.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

Vimal Lohani (8/5/2014)
Good Script..
It somewhat works like select the table and press alt+f1 .

That's what I use.