SQL获取表名、列名、数据类型、描述、表最后修改时间

SELECT SO.Name--表名
,ISNULL(EP.VALUE,'-') Description --描述
,CONVERT(varchar, SO.refdate, 120) as ModifiedTime --最后修改时间
FROM SYSCOLUMNS SC INNER JOIN SYSOBJECTS SO ON SC.ID = SO.ID AND SO.XTYPE = 'U' AND SO.NAME <> 'SYSDIAGRAMS' 
LEFT JOIN SYS.EXTENDED_PROPERTIES EP ON SC.ID = EP.MAJOR_ID AND EP.MINOR_ID = 0 
WHERE (CASE WHEN SC.COLORDER = 1 THEN SO.NAME ELSE '' END) <> '' 
ORDER BY ModifiedTime DESC

这里其实要说一下的是【最后修改时间】。

当时是我们一个项目组里面用到，原因是因为老是有人私自对数据库进行修改。

于是我们通过讨论，如何才能最快的找出那些表被修改了。

于是就有了这段代码。

下面再补一段通过表名获取列，数据类型，描述等信息。

SELECT SC.Name --查询【列名】【类型】【长度】【描述】
    ,[TYPE]  --类型名称
    = ST.Name +  
    CASE ST.user_type_id  
    WHEN 41 THEN '('+CAST(SC.scale AS VARCHAR) +')'                                      -- time
    WHEN 42 THEN '('+CAST(SC.scale AS VARCHAR) +')'                                       -- datetime2
    WHEN 43 THEN '('+CAST(SC.scale AS VARCHAR) +')'                                       -- datetimeoffset
    WHEN 106 THEN '('+CAST(SC.precision  AS VARCHAR)+','+ CAST(SC.scale AS VARCHAR) +')'  -- decimal
    WHEN 108 THEN '('+CAST(SC.precision  AS VARCHAR)+','+ CAST(SC.scale AS VARCHAR) +')'  -- numeric
    WHEN 165 THEN '('+ISNULL(CAST(NULLIF(SC.max_length,-1) AS VARCHAR),'max') +')'        -- varbinary
    WHEN 167 THEN '('+ISNULL(CAST(NULLIF(SC.max_length,-1) AS VARCHAR),'max') +')'        -- varchar
    WHEN 173 THEN '('+ISNULL(CAST(NULLIF(SC.max_length,-1) AS VARCHAR),'max') +')'        -- binary
    WHEN 175 THEN '('+ISNULL(CAST(NULLIF(SC.max_length,-1) AS VARCHAR),'max') +')'        -- char
    WHEN 231 THEN 
        CASE SC.max_length 
        WHEN -1 THEN '(max)'
        ELSE '('+ISNULL(CAST(NULLIF(SC.max_length / 2,-1) AS VARCHAR),'max') +')'     -- nvarchar
        END 
    WHEN 239 THEN '('+ISNULL(CAST(NULLIF(SC.max_length,-1) AS VARCHAR),'max') +')'    -- nchar
    ELSE '' 
    END  
        ,CASE WHEN SC.Is_Nullable = 0 THEN 'NOT NULL'  --是否允许为空
        WHEN SC.Is_Nullable = 1 THEN 'NULL' 
        ELSE 'UNKNOWN' END IsNullable
,(SELECT SYS.EXTENDED_PROPERTIES.VALUE FROM SYSCOLUMNS 
INNER JOIN SYS.EXTENDED_PROPERTIES ON SYSCOLUMNS.ID = SYS.EXTENDED_PROPERTIES.MAJOR_ID 
AND SYSCOLUMNS.COLID = SYS.EXTENDED_PROPERTIES.MINOR_ID 
INNER JOIN SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID 
WHERE SYSOBJECTS.NAME = SO.NAME AND SYSCOLUMNS.NAME = SC.NAME) Description --SO.NAME表名、SC.NAME列名 
FROM SYS.COLUMNS SC,SYS.TYPES ST,SYS.SYSOBJECTS SO --列名信息和表结构信息
WHERE SC.user_type_id = ST.user_type_id AND SC.object_id = SO.ID AND SO.XTYPE='U' AND SO.NAME='你的表名'

先直接复制代码运行下，看看结果。

这里需要对数据类型的查询方式说下：

SYS.TYPES表的user_type_id

数据库里面【数据类型】的Id，就目前来看只有这几种类型是需要用户设置长度的（这段代码是从网上找的，分享给大家）

最后把上面所用到的表和其他系统表拿出来看看。

SELECT * FROM SYS.syscolumns	--存储每个表和视图中的每一列的信息以及存储过程中的每个参数的信息
SELECT * FROM SYS.syscomments	--存储包含每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的原始 SQL 文本语句。
SELECT * FROM SYS.sysconstraints--存储当前数据库中每一个约束的基本信息。
SELECT * FROM SYS.sysdatabases	--存储当前服务器上每一个数据库的基本信息。
SELECT * FROM SYS.sysindexes	--存储当前数据库中的每个索引的信息。
select * from sys.objects       --在数据库中创建的每个用户定义的架构范围内的对象在该表中均对应一行
SELECT * FROM SYS.sysobjects	--存储数据库内的每个对象（约束、默认值、日志、规则、存储过程等）的基本信息。
SELECT * FROM SYS.sysreferences --存储所有包括 FOREIGN KEY 约束的列。
SELECT * FROM SYS.systypes      --存储系统提供的每种数据类型和用户定义数据类型的详细信息。