取数据库字段信息

create procedure [dbo].[proGetColumnInfo] @tblName nvarchar(50) as

select 

table_name=c.Name,

  col_name=a.Name,

  is_identity,

    is_primary=case when exists(select 1 from sys.objects 

INNER JOIN sys.indexes ON sys.indexes.Name=sys.objects.Name

INNER JOIN sysindexkeys ON sysindexkeys.indid=sys.indexes.index_id

   where parent_object_id=a.object_id and sys.objects.type=N'PK' AND sysindexkeys.ID=a.object_id and sysindexkeys.colid=a.column_id)

                   then 1 else 0 end,

    col_typename=b.Name,

   col_len=ColumnProperty(a.object_id,a.Name,'Precision'),

    decimal_len=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0),

    a.is_nullable,

    col_description=isnull(e.[value],''),

    default_value=isnull(d.text,'')    

from 

    sys.columns a

left join

    sys.types b on a.user_type_id=b.user_type_id

inner join

    sys.objects c on a.object_id=c.object_id and c.Type='U'

left join

    syscomments d on a.default_object_id=d.ID

left join

    sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1 

where c.name=@tblName

----order by a.Column_id