SQL statement used to obtain the remarks and primary keys of fields in the MSSQL table structure

1. MSSQL2000
Copy codeThe Code is as follows:
SELECT
Table name = case when a. colorder = 1 then d. name else ''end,
Table description = case when a. colorder = 1 then isnull (f. value, '') else'' end,
FIELD No. = a. colorder,
Field name = a. name,
Id = case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' end,
Primary Key = case when exists (SELECT 1 FROM sysobjects where xtype = 'pk' and parent_obj = a. id and name in (
SELECT name FROM sysindexes WHERE indid in (
SELECT indid FROM sysindexkeys WHERE id = a. id AND colid = a. colid) then' √ 'else' end,
Type = B. name,
Bytes occupied = a. length,
Length = COLUMNPROPERTY (a. id, a. name, 'precision '),
Decimal places = isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0 ),
Allow null = case when a. isnullable = 1 then '√ 'else' 'end,
Default Value = isnull (e. text ,''),
Field description = isnull (g. [value], '')
FROM
Syscolumns
Left join
Policypes B
On
A. xusertype = B. xusertype
Inner join
Sysobjects d
On
A. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties'
Left join
Syscomments e
On
A. cdefault = e. id
Left join
Sysproperties g
On
A. id = g. id and a. colid = g. smallid
Left join
Sysproperties f
On
D. id = f. id and f. smallid = 0
Where
D. name = 'fi _ dept '-- if only the specified table is queried, add this condition.
Order

A. id, a. colorder


2. MSSQL2005

Use test -- Database
Go
-- 2005 implement field property statistics (System Table sysproperties in 2000 describes the table and the field does not exist, and sys. extended_properties view is used in 2005)
Select
[Table Name] = c. Name,
[Table description] = isnull (f. [value], ''),
[Column Name] = a. Name,
[Column number] = a. Column_id,
[Identifier] = case when is_identity = 1 then '√ 'else' 'end,
[Primary key] = case when exists (select 1 from sys. objects where parent_object_id = a. object_id and type = N 'pk' and name in
(Select Name from sys. indexes where index_id in
(Select indid from sysindexkeys where and colid = a. column_id )))
Then' √ 'else' end,
[Type] = B. Name,
[Number of bytes] = case when a. [max_length] =-1 and B. Name! = 'Xml' then' max/2G'
When B. Name = 'xml' then' 2 ^ 31-1 bytes/2 GB'
Else rtrim (a. [max_length]) end,
[Length] = ColumnProperty (a. object_id, a. Name, 'precision '),
[Decimal] = isnull (ColumnProperty (a. object_id, a. Name, 'Scale'), 0 ),
[Null or not] = case when a. is_nullable = 1 then '√ 'else' 'end,
[Column description] = isnull (e. [value], ''),
[Default value] = isnull (d. text ,'')
From
Sys. columns
Left join
Sys. types B on a. user_type_id = B. user_type_id
Inner join
Sys. objects c on a. object_id = c. object_id and c. Type = 'U'
Left join
Syscomments d on a. default_object_id = d. ID
Left join
Sys. extended_properties e on e. major_id = c. object_id and e. minor_id = a. Column_id and e. class = 1
Left join
Sys. extended_properties f on f. major_id = c. object_id and f. minor_id = 0 and f. class = 1
[/Code]
Result: