急, 用一条sql语句查询整个数据库的表结构

[蓝色风影]

查询结果如下:


表名, 字段名称 字段类型 字段长度 小数点后几位

table1 id nvarchar 20 0
table1 name nvarchar 40 0
table1 qty number 18 6
table2 code nvarchar 20 0
table2 name nvarchar 40 0

[jstoic]

SELECT * FROM syscolumns
SELECT * FROM systypes
SELECT * FROM sysobjects

三个表组合查询。吃饭去了

[蓝色风影]

能不能写给我看阿,我对系统表不熟阿.

[vivai2010]

--sql 2008
use databasename;
sp_columns '%';

[蓝色风影]

[Quote=引用 3 楼 vivai2010 的回复:]
--sql 2008
use databasename;
sp_columns '%';
[/Quote]

我是用sql2005 的

[zhangyangziwo]

经过测试，05也管

[q107770540]

http://blog.csdn.net/q107770540/archive/2010/05/20/5610838.aspx

[SQLCenter]

select
	c.name as object_name,
	a.column_id,
	a.name as column_name,
	b.name +
		case
			when a.max_length = -1 and b.name in ('varbinary', 'varchar', 'nvarchar') then '(max)'
			when b.name in ('decimal', 'numeric') then '(' + ltrim(a.precision) + ',' + ltrim(a.scale) + ')'
			when b.name in ('varbinary', 'varchar', 'binary', 'char') then '(' + ltrim(a.max_length) + ')'
			when b.name in ('nvarchar', 'nchar') then '(' + ltrim(a.max_length / 2) + ')'
			else ''
		end as data_type_definition
from
	sys.columns as a
inner join
	sys.types as b
	on a.user_type_id = b.user_type_id
inner join
	sys.objects as c
	on a.object_id = c.object_id and c.type = 'U'

[xyj052]

[Quote=引用 6 楼 q107770540 的回复:]
http://blog.csdn.net/q107770540/archive/2010/05/20/5610838.aspx
[/Quote]

这个里面的没问题了