Sqlserver queries the column name, description, remarks, comments and types of a table or library

Time:2021-7-25

Comments on tables or table fields are extended attributes of database objects. In MSSQL, some annotative contents can be put into the database or database objects to enhance readability and contribute to future management and maintenance. The contents of extended attributes can be added, modified or deleted through SSMS, queried through system view, and maintained by executing relevant stored procedures.
information_ Schema: System View
sys.extended_ Properties: System View

1、 SQL server queries the column name, description, remarks and type of a table

--SQL server queries the column name, description, remarks and type of a table
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 serial number = 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, 
  Occupied bytes = a.length, 
  Length = COLUMNPROPERTY (a.id, a.name, 'precision'), 
  Scale = isnull(
    COLUMNPROPERTY(a.id, a.name, 'Scale'), 
    0
  ), 
  Allow null = case when a.isnullable = 1 then '√' else 'end, 
  Default = isnull (e.text, ''), 
  Field description = isnull (G. [value], '') 
FROM 
  syscolumns a 
  left join systypes 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 sys.extended_properties g on a.id = G.major_id 
  and a.colid = g.minor_id 
  left join sys.extended_properties f on d.id = f.major_id 
  and f.minor_id = 0 
where 
  d. Name = 'database table name' -- if only the specified table is queried and this where condition is added, tablename is the table name to query; Remove the where condition to query all table information
order by 
  a.id, 
  a.colorder

The effect is shown in the figure:
查询某个表的列名称、说明、备注、类型等

2、 Query all column names of a table

--Query all column names of a table
Select Name FROM SysColumns Where id=Object_ ID ('Database table name queried ')
GO
select t.column_ name from information_ schema.columns t where t.table_ Name = 'database table name queried';

Effect drawing of the first Bureau:
查询一个表的所有列名_1
Execution effect of the second sentence:
查询一个表的所有列名_2

3、 Query all column names and field comments of a table

------Query all column names and field comments of a table
SELECT
A. Name as table name,
B. Name as field name,
C. Value as field description
FROM sys.tables A
INNER JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id
Where a.name = 'database table name queried'

The effect is shown in the figure:
查询一个表的所有列名,字段的注释

4、 Query entire database statement

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2020/3/24 8:50:02
 ************************************************************/
 
--Name and field name 
SELECT 
  (
    CASE WHEN a.colorder = 1 THEN d.name ELSE '' END
  )Table name, 
  a. Colorder field sequence number, 
  a. Name field name, 
  (
    CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END
  )Identification, 
  (
    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
  )Primary key, 
  b. Name type, 
  a. Length bytes occupied, 
  COLUMNPROPERTY (a.id, a.name, 'precision') as length, 
  ISNULL(
    COLUMNPROPERTY(a.id, a.name, 'Scale'), 
    0
  )As decimal places, 
  (
    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END
  )Null allowed, 
  Isnull (e.text, '') default value, 
  Isnull (G. [value], '') as field description 
FROM 
  syscolumns a 
  LEFT JOIN systypes 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 
  a.id, 
  a.colorder