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，就目前来看只有这几种类型是需要用户设置长度的（这段代码是从网上找的，分享给大家）