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

  • jmagnet

    SSC Enthusiast

    Points: 184

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

  • janis.l.murphy

    SSC Veteran

    Points: 239

    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

    Hi janis.l.murphy

    I've modified the script as you suggested.

    Thanks for your work.

  • Vimal Lohani

    SSCommitted

    Points: 1650

    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

    vimal.lohani (8/5/2014)


    Good Script..

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

    Thanks.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Vimal Lohani (8/5/2014)


    Good Script..

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

    That's what I use.

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

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