During development projects, you often need to record the table structure of the database as a document to facilitate future maintenance. You can use the following SQL statement to get the table structure definition, which is convenient for copying to the document:
Select -- T. object_id,
T. Name as tablename
, C. Name as columnname
, Case when Ty. Name = 'varchar 'then
Case when C. max_length =-1
Then Ty. Name + '(max )'
Else Ty. Name + '(' + convert (varchar, C. max_length) + ')'
End
When Ty. Name = 'nvarchar 'then
Case when C. max_length =-1
Then Ty. Name + '(max )'
Else Ty. Name + '(' + convert (varchar, C. max_length/2) + ')'
End
When Ty. Name = 'numeric 'then
Ty. Name + '(' + convert (varchar, C. Precision) + ',' + convert (varchar, C. Scale) + ')'
Else Ty. Name
End as typename
, Case when C. is_identity = 1
Then 'yes'
Else 'no'
End as is_identity
, Case when C. is_nullable = 1
Then 'yes'
Else 'no'
End as is_nullable
, C. max_length
, C. Precision
, (Select Value
From SYS. extended_properties as ex
Where ex. major_id = C. object_id
And Ex. major_id = C. column_id) as notes
, Scale
From SYS. Tables as t
Left join SYS. columns as C
On C. object_id = T. object_id
Inner join
(Select name
, System_type_id
From SYS. Types
Where name <> 'sysname') as ty
On C. system_type_id = Ty. system_type_id
-- Where T. name like 'vs _ %' -- filter table name
Order by T. Name, C. column_id
Result After executing the preceding statement