Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jmagnet
jmagnet
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 21
Comments posted to this topic are about the item List all the fields of a table with its properties + PK
janis.l.murphy
janis.l.murphy
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 127
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].[table 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
jmagnet
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 21
Hi janis.l.murphy

I've modified the script as you suggested.

Thanks for your work.
Vimal Lohani
Vimal Lohani
SSC-Addicted
SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)SSC-Addicted (448 reputation)

Group: General Forum Members
Points: 448 Visits: 659
Good Script..
It somewhat works like select the table and press alt+f1 .

Vimal Lohani
SQL 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
jmagnet
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 21
vimal.lohani (8/5/2014)
Good Script..
It somewhat works like select the table and press alt+f1 .


Thanks.
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10478 Visits: 885
Vimal Lohani (8/5/2014)
Good Script..
It somewhat works like select the table and press alt+f1 .
That's what I use.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search