Query for select field names,data types etc..

Get   names, data types, description, and size of fields in table

While making documentation for Db tables, we require its field names, data types, size, and its descriptions etc system info.

So that u can just copy and paste it from result pane of sql server instead of doing copy paste from modifying table

Here is sql -query for same…Hope u found it interesting…

SELECT

[Column Name] = c.name,

sysTypes.name AS DataType  ,

c.is_nullable As Nullable,

c.Max_Length AS Length ,

[Description] = ex.value

FROM

sysTypes,sys.columns c

LEFT OUTER JOIN

sys.extended_properties ex

ON

ex.major_id = c.object_id

AND ex.minor_id = c.column_id

WHERE

c.system_type_id= sysTypes.xtype  AND

OBJECT_NAME(c.object_id) = TableName and sysTypes.name <> ‘sysname’

ORDER

BY OBJECT_NAME(c.object_id), c.column_id

About these ads

2 Responses to “Query for select field names,data types etc..”


  1. 1 aa July 13, 2009 at 7:54 pm

    appreciating your efforts ….looking for same … keep it up your work

  2. 2 Cathleen July 10, 2013 at 8:01 am

    Thank you, I’ve just been looking for information approximately this topic for a while and yours is the greatest I have came upon till now. However, what in regards to the conclusion? Are you sure about the source?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow me on Twitter

Error: Twitter did not respond. Please wait a few minutes and refresh this page.

Enter your email address to follow this blog and receive notifications of new posts by email.


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: