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