MSSQL 返回表中列的全部信息

经常自己用到，mark一下，多表级联查询，starCoder开发过程用用到的一段代码

 SELECT a.column_id,a.name,b.name AS datatype,a.max_length,a.precision,a.scale,
            a.is_identity,a.is_nullable,a.is_computed,
            g.definition AS defaultValue,c.definition AS expression,
            d.value AS comment,e.index_id AS indexId,f.name AS indexName,
            f.type_desc,e.is_descending_key AS DescOrder,f.is_primary_key,f.is_unique
            FROM sys.columns a
            LEFT JOIN sys.types b ON a.system_type_id=b.system_type_id
            LEFT JOIN sys.computed_columns c ON a.column_id=c.column_id
            LEFT JOIN sys.extended_properties d ON d.major_id=a.object_id AND d.minor_id=a.column_id  
            LEFT JOIN sys.index_columns e ON a.object_id=e.object_id AND a.column_id=e.column_id
            LEFT JOIN sys.indexes f ON f.object_id=a.object_id AND f.index_id=e.index_id AND e.column_id=a.column_id
            LEFT JOIN sys.default_constraints g ON g.object_id=a.default_object_id 
           WHERE a.object_id= OBJECT_ID('dbo.表名') AND b.name!='sysname' ORDER BY a.column_id

 简单版的，返回列名，数据类型，注释

SELECT a.name,b.name AS datatype,d.value AS comment FROM sys.columns a 
 LEFT JOIN sys.types b ON a.system_type_id=b.system_type_id
LEFT JOIN sys.extended_properties d
 ON  d.major_id=a.object_id AND minor_id=a.column_id   WHERE object_id= OBJECT_ID('dbo.表名') AND b.name!='sysname'

 C#对象初始化代码块

SELECT a.name+'=,' AS [name],'//'+b.name+','+ CAST(d.value AS NVARCHAR(50)) AS comment FROM sys.columns a 
LEFT JOIN sys.types b ON a.system_type_id=b.system_type_id
LEFT JOIN sys.extended_properties d
ON  d.major_id=a.object_id AND minor_id=a.column_id   
WHERE object_id= OBJECT_ID('dbo.表名') AND b.name!='sysname'

 C# 类

SELECT '///',d.value AS comment,'public',CASE(b.name) 
WHEN ('char') THEN 'string'  
WHEN 'bit' THEN 'bool' 
WHEN 'varchar' THEN 'string'
WHEN 'datetime' THEN 'DateTime'
WHEN 'nvarchar' THEN 'string'
ELSE b.name end AS datatype,
CONCAT(UPPER(SUBSTRING(a.name,1,1)), SUBSTRING(a.name,2,LEN(a.name)-1)) AS proName,
'{get;set;}'
FROM sys.columns a 
LEFT JOIN sys.types b ON a.system_type_id=b.system_type_id
LEFT JOIN sys.extended_properties d
ON  d.major_id=a.object_id AND minor_id=a.column_id   WHERE object_id= OBJECT_ID('dbo.school') AND b.name!='sysname'