枚举数据库中所有表的列名(转)

枚举数据库中所有表的列名

SELECT 

CASE WHEN C.column_id = 1 THEN O.name ELSE N'' END AS TableName,--表名

C.column_id AS Column_id,--列序号

C.name AS ColumnName,--列表

ISNULL(PFD.value, N'') As Value,--列值

ISNULL(IDX_1.PrimaryKey, N'') AS PrimaryKey,--主键

CASE WHEN C.is_identity = 1 THEN N'√' ELSE N'' END AS [Identity],--是否自增列

T.name AS Type,--列类型

C.max_length AS Length,--列长度

C.precision AS Precision,--精确度

C.scale AS Scale,--小数位数

CASE WHEN C.is_nullable = 1 THEN N'√' ELSE N'' END AS NullAble,--是否为空

ISNULL(D.definition, N'') AS [Default],--默认值

ISNULL(IDX_1.IndexName, N'') AS IndexName,--索引名称

ISNULL(IDX_1.Sort, N'') AS IndexSort,--索引排序方式

O.create_date AS Create_Date,--创建时间

O.modify_date AS Modify_Date--最后修改时间

FROM

sys.columns AS C 

INNER JOIN

    sys.objects AS O ON C.object_id = O.object_id AND O.type = 'U' AND O.is_ms_shipped = 0 

    INNER JOIN

    sys.types AS T ON C.user_type_id = T.user_type_id 

    LEFT OUTER JOIN

    sys.default_constraints AS D ON C.object_id = D.parent_object_id AND C.column_id = D.parent_column_id AND C.default_object_id = D.object_id 

    LEFT OUTER JOIN

sys.extended_properties AS PFD ON PFD.class = 1 AND C.object_id = PFD.major_id AND C.column_id = PFD.minor_id 

    LEFT OUTER JOIN

    ( SELECT 

IDXC.object_id, 

IDXC.column_id, 

CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending') 

WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END AS Sort, 

CASE WHEN IDX.is_primary_key = 1 THEN N'√' ELSE N'' END AS PrimaryKey, IDX.name AS IndexName

      FROM

sys.indexes AS IDX 

INNER JOIN

        sys.index_columns AS IDXC ON IDX.object_id = IDXC.object_id AND IDX.index_id = IDXC.index_id ) AS IDX_1 ON C.object_id = IDX_1.object_id AND C.column_id = IDX_1.column_id

ORDER BY O.name, Column_id