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
