Êý¾Ý×ֵ䣨SQLÓï¾ä£©

declare @tmp table
(
id int identity(1,1),
TableName varchar(100),
Column_name varchar(100),
Type varchar(50),
Lenght int,
Scale int,
Nullable varchar(1),
Defaults varchar(4000),
PrimaryKey varchar(1)
)
select iid = identity(int,1,1), * into #a from SysObjects where xtype = 'U'
declare @i int
declare @max int
declare @table varchar(100)
set @i = 1
select @max = max(iid) from #a
while @i <= @max
begin
    select @table = name from #a where iid = @i
    if @@rowcount > 0
    begin
        insert @tmp (TableName, Column_name, Type, Lenght, Scale, Nullable, Defaults,PrimaryKey)
        select @table, a.name, c.name, a.length, a.xscale, case a.isnullable when 0 then 'N' else 'Y' end, isnull(d.text,''), case when x.PrimaryKey is null then '' else x.PrimaryKey end
        from SysColumns a with(nolock)
        inner join (select * from SysObjects with(nolock) where xtype = 'U' and  id = object_id(@table)) b on a.id = b.id
        inner join SysTypes c with(nolock) on a.xtype = c.xusertype
        left join syscomments d with(nolock) on a.cdefault = d.id
        left join
            (select f.id, colid, 'Y' as PrimaryKey from SysIndexKeys f with(nolock), SysIndexes e, SysObjects g
            where f.id = e.id and f.indid = e.indid and f.id = g.parent_obj and e.name = g.name
            and g.xtype = 'PK' and g. parent_obj = object_id(@table)) x on a.id = x.id and a.colid = x.colid
    end
    set @i = @i + 1
end
select * from @tmp