How to Get SQL Table Field Structure?

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:

SELECT
ORDINAL_POSITION                       AS [Nu],
COLUMN_NAME                            AS [Field Name],
DATA_TYPE                              AS [Type],
LTRIM(COALESCE(
    STR(CHARACTER_MAXIMUM_LENGTH),
    STR(NUMERIC_PRECISION)+','+LTRIM(STR(NUMERIC_SCALE)), ' '))
                                       AS [Size],
    IS_NULLABLE                        AS [Nullable] ,
    ISNULL(COLUMN_DEFAULT, ' ')        AS [Default Value]
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'tYourTable'
ORDER BY
ORDINAL_POSITION ASC

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

(Visited 37 times, 1 visits today)

1 Comment

  1. 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..

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *