How to Get SQL Table Field Structure?

Modified: 2017/04/30

It should be easy to print the structure of your SQL tables – right? Not so fast. Try to search that topic and only find a handful of relevant links. I wrote this T-SQL Query to use as a quick reference. Every time I need to get a structure of SQL table, I am using this syntax.

One little twist here is that different field types store its size in different columns. This SQL command combines an important CHARACTER_OCTET_LENGTH and NUMERIC_PRECISION and NUMERIC_SCALE fields all in one column:

ORDINAL_POSITION                       AS [Nu],
COLUMN_NAME                            AS [Field Name],
DATA_TYPE                              AS [Type],
                                       AS [Size],
    IS_NULLABLE                        AS [Nullable] ,
    ISNULL(COLUMN_DEFAULT, ' ')        AS [Default Value]
TABLE_NAME = 'tYourTable'

Here is what the final result might look like:

Nu   Field Name   Type   Size   Nullable   Default Value
1   Unique ID   int   10,0   NO    
2   Customer ID   int   10,0   YES    
3   Project ID   int   10,0   YES    
4   Service Provider ID   int   10,0   YES    
5   Date   smalldatetime       YES   (getdate())
6   Billing Units   decimal   5,2   YES    
7   Task Description   nchar   128   YES    
8   Billable Y/N   bit       YES   ((1))
9   Completed Y/N   bit       YES   ((1))
10   Invoice Number   int   10,0   YES    
11   Date Entered   smalldatetime       YES   (getdate())
12   Date Billed   smalldatetime       YES    
13   Date Updated   smalldatetime       YES   (getdate())
14   Updated By   nchar   10   YES   (N’LOCALHOST’)


[Personal Note:  last used 2015-12-21 TU 17:28]


Beware of false prophets:

Few people think more than two or three times a year. I’ve made an international reputation for myself by thinking once or twice a week.”
Bernard Shaw

Tags: , , , , ,

One Response to How to Get SQL Table Field Structure?

  1. Maria
    2012/06/19 at 15:13

    Hallo, PeterI usually use sql stnatmeet via sql analyzer or query.I think something missing in your sql stnatmeet, so I’m sure your stnatmeet won’t work.Your stnatmeet is:SET SUB_DMO = CONCAT(’11′, SUBSTRING(SUM_DMO,3))It should be:SET SUB_DMO = CONCAT(’11′, SUBSTRING(SUM_DMO,3,2))However you are also able to use below stnatmeet (use plus sign (+)):SET SUB_DMO = ’11′ + SUBSTRING(SUM_DMO,3,2)I try the stnatmeet, and it works.Printer Info recently posted..

Add Your Comment Ваш Комментарий

Your email address will not be published. Required fields are marked *

* Ваше Имя *
* Ваш Email (не будет показан на сайте)*