sql server 语句获取表的结构

select b.[name] tableName,a.[name] columnName,c.[name] typeName,a.[length] typeLength
,(case when a.colstat=1 then 1 else 0 end )isIdentity
,(case when a.colstat=4 then 1 else 0 end )isColstat
,(case when isnull(e.[name],'')<>'' then 1 else 0 end) as isPrimaryKey
--,(case when isnull(f.column_id,0)<>0 then 1 else 0 end) as isPrimaryKey
,a.colorder columnOrder
from syscolumns a 
left join sys.tables b on b.[object_id]=a.id
left join sys.types c on c.system_type_id=a.xtype and c.user_type_id=a.xusertype
left join information_schema.key_column_usage d on b.[name]=d.table_name and a.[name]=d.column_name
left join sys.indexes e on e.[object_id]=a.id and e.[name]=d.constraint_name and e.is_primary_key=1
--left join 
--    (select b.* from sys.indexes a
--    left join sys.index_columns b on a.[object_id]=b.[object_id] and a.index_id=b.index_id and a.is_primary_key=1
--    where b.object_id is not null
--    ) f on f.[object_id]=a.id and f.column_id=a.colid
where b.[name] in ('User','Role')
order by b.[name],a.colid