sqlserver获取字段信息

                                    WITH indexCTE AS
                                    (
                                     SELECT
                                        ic.column_id,
                                        ic.index_column_id,
                                        ic.object_id   
                                        FROM nopCom.sys.indexes idx
                                        INNER JOIN nopCom.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id
                                        WHERE  idx.object_id =OBJECT_ID('nopCom.dbo.BlogPost') AND idx.is_primary_key=1
                                    )
                                    select
         colm.column_id ColumnID,
                                    CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey,
                                    colm.name ColumnName,
                                    systype.name ColumnType,
                                    colm.is_identity IsIdentity,
                                    colm.is_nullable IsNullable,
                                    cast(colm.max_length as int) ByteLength,
                                    (
                                        case
                                            when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2
                                            when systype.name='nchar' and colm.max_length>0 then colm.max_length/2
                                            when systype.name='ntext' and colm.max_length>0 then colm.max_length/2
                                            else colm.max_length
                                        end
                                    ) CharLength,
                                    cast(colm.precision as int) Precision,
                                    cast(colm.scale as int) Scale,
                                    prop.value Remark
                                    from nopCom.sys.columns colm
                                    inner join nopCom.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
                                    left join nopCom.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
                                    LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id                                       
                                    where colm.object_id=OBJECT_ID('nopCom.dbo.BlogPost')
                                    order by colm.column_id

转载于:https://www.cnblogs.com/ZhengGuoQing/archive/2012/12/15/2819561.html