得到数据库中所有表的列数方法

-- 得到数据库中所有表的列数方法

declare xcursor cursor for

select name from sysobjects where xtype= 'u'

declare @name varchar ( 100)

open xcursor

fetch xcursor into @name

create table #temptable ( tablename varchar ( 300), rowcount1 int )

while @@fetch_status = 0

begin

exec ( 'insert into #temptable select ''' + @name+ ''',count(*) from ' + @name)

fetch xcursor into @name

end

close xcursor

deallocate xcursor

select b. name, count ( a. name) as 列数 from syscolumns a, sysobjects b , #temptable c

where b. xtype= 'u' and a. id= object_id ( b. name) and b. name= c. tablename

group by b. name

drop table #temptable

posted on 2010-04-08 20:20 gaojier 阅读(300) 评论(0) 编辑收藏举报