SQL statement used to read the field name, field type, field length, and field attribute description of the SQL server table

Obtain the table field name (SORT ):

SELECT. name as field name FROM syscolumns a inner join sysobjects d on. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' where d. name = 'table name' order by. id,. colorder

Expected table field name:

Select name from syscolumns Where ID = OBJECT_ID ('table name ')

 

Field names can be obtained horizontally:

Select * from Table Name
Declare @ str varchar (4000)
Set @ str =''
Select @ str = @ str + ',' + field name from Table Name
Select right (@ str, len (@ str)-1)
Print @ str

STR long characters can be printed.

 

You can also obtain the field name, field type, field length, and field attributes of the table:

SELECT. name field name, COLUMNPROPERTY (. id,. name, 'isidentity ') identifier, (case when (SELECT count (*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id =. id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id =. id) AND (colid in (SELECT
Colid FROM syscolumns WHERE (id =. id) AND (name =. name) AND (xtype = 'pk')> 0 then '1' else '0' end) primary key, B. name type, COLUMNPROPERTY (. id,. name, 'precision ') as length, isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) as decimal places, (case when. isnullable = 1 then '1' else
'0' end) can be null, isnull (g. [value], '') AS field description FROM syscolumns
Left join policypes B on a. xtype = 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
Where d. name = 'input'
Order by a. id, a. colorder

 

SQL to view all table names:
Select name from sysobjects where type = 'U'

Query the names of all fields in the table:
Select name from syscolumns Where ID = OBJECT_ID ('table name ')

Select * from information_schema.tables
Select * from information_schema.views
Select * from information_schema.columns
Access
View All table names:
Select name from MSysObjects where type = 1 and flags = 0

MSysObjects is a system object, which is hidden by default. You can use tools, options, views, displays, and system objects to display them.

 

SQL query table remarks

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
FROM syscolumns
Inner join sysobjects d
On a. id = d. id
And d. xtype = 'U'
And d. name <> 'sys. extended_properties'
Left join sys. extended_properties f
On a. id = f. major_id
And f. minor_id = 0

Description of all fields in the SQL query table

SQL _1:

SELECT Sysobjects. name AS TABLE_NAME, syscolumns. Id, syscolumns. name AS COLUMN_NAME,
Policypes. name AS DATA_TYPE, syscolumns. length as CHARACTER_MAXIMUM_LENGTH,
Sys. extended_properties. [value] AS COLUMN_DESCRIPTION, syscomments. text
COLUMN_DEFAULT, syscolumns. isnullable as IS_NULLABLE FROM syscolumns
Inner join policypes
ON syscolumns. xtype = policypes. xtype
Left join sysobjects ON syscolumns. id = sysobjects. id
Left outer join sys. extended_properties ON
(Sys. extended_properties.minor_id = syscolumns. colid
AND sys. extended_properties.major_id = syscolumns. id)
Left outer join syscomments ON syscolumns. cdefault = syscomments. id
WHERE syscolumns. id IN
(SELECT id from sysobjects where xtype = 'U') AND (policypes. name <> 'sysname ')
Order by syscolumns. colid

SQL _2:

SELECT
(Case when a. colorder = 1 then d. name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else''
End) n' identifiers ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(Select colid
From syscolumns
Where (ID = A. ID) and (name = A. Name) and
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. Name n' type ',
A. length N 'number of bytes occupied ',
Columnproperty (A. ID, A. Name, 'precision ') as N 'length ',
Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (e. text, '') n' default value ',
Isnull (g. [value], '') AS n' field description'
FROM syscolumns
Left join policypes B
On a. xtype = 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 sys. extended_properties g
On a. id = g. major_id AND a. colid = g. minor_id
Order by object_name (a. id), a. colorder

 

SQL to view all table names:
Select name from sysobjects where type = 'U'

Query the names of all fields in the table:
Select name from syscolumns Where ID = OBJECT_ID ('table name ')

Select * from information_schema.tables
Select * from information_schema.views
Select * from information_schema.columns
Access
View All table names:
Select name from MSysObjects where type = 1 and flags = 0

MSysObjects is a system object, which is hidden by default. You can use tools, options, views, displays, and system objects to display them.

 

SQL query table remarks

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
FROM syscolumns
Inner join sysobjects d
On a. id = d. id
And d. xtype = 'U'
And d. name <> 'sys. extended_properties'
Left join sys. extended_properties f
On a. id = f. major_id
And f. minor_id = 0

Description of all fields in the SQL query table

SQL _1:

SELECT Sysobjects. name AS TABLE_NAME, syscolumns. Id, syscolumns. name AS COLUMN_NAME,
Policypes. name AS DATA_TYPE, syscolumns. length as CHARACTER_MAXIMUM_LENGTH,
Sys. extended_properties. [value] AS COLUMN_DESCRIPTION, syscomments. text
COLUMN_DEFAULT, syscolumns. isnullable as IS_NULLABLE FROM syscolumns
Inner join policypes
ON syscolumns. xtype = policypes. xtype
Left join sysobjects ON syscolumns. id = sysobjects. id
Left outer join sys. extended_properties ON
(Sys. extended_properties.minor_id = syscolumns. colid
AND sys. extended_properties.major_id = syscolumns. id)
Left outer join syscomments ON syscolumns. cdefault = syscomments. id
WHERE syscolumns. id IN
(SELECT id from sysobjects where xtype = 'U') AND (policypes. name <> 'sysname ')
Order by syscolumns. colid

SQL _2:

SELECT
(Case when a. colorder = 1 then d. name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else''
End) n' identifiers ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(SELECT colid
From syscolumns
Where (ID = A. ID) and (name = A. Name) and
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. Name n' type ',
A. length N 'number of bytes occupied ',
Columnproperty (A. ID, A. Name, 'precision ') as N 'length ',
Isnull (columnproperty (A. ID, A. Name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (E. Text, '') n' default value ',
Isnull (G. [value], '') as n' field description'
From syscolumns
Left join policypes B
On a. xtype = 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 SYS. extended_properties g
On a. ID = G. major_id and A. colid = G. minor_id
Order by object_name (A. ID), A. colorder