怎么将SQL Server中所有表和列信息显示出来
谁可以帮我写个SQL执行后返回的列分别是：表名、列名、列类型、列长度、列描述、是否主键
写完整来。就是我一拿过来就可以运行的那种！~！~谢谢哦。
问题点数：50、回复次数：10Top
1 楼lsqkeke（可可）回复于 2006-03-01 11:56:23 得分 20
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorderTop
2 楼lsqkeke（可可）回复于 2006-03-01 11:57:19 得分 0
这是SQL 版主写的Top
3 楼nhr（无名氏）回复于 2006-03-01 12:03:00 得分 0
谢谢楼上的，不过怎么我运行起来有问题啊？Top
4 楼zlp321002（Life Is Good,Let's Shine）回复于 2006-03-01 12:24:59 得分 10
--你的是什么数据库？？2005?Top
5 楼shareinfo（长江之怀）回复于 2006-03-01 12:37:14 得分 10
第一步创建一个视图
create view v_all_table as SELECT o.name t_name, l.name t_columns, t .name t_type, l.length, l.isnullable, c.text, l.colorder
FROM sysobjects o, syscolumns l, systypes t, syscomments c
WHERE o.xtype = 'u' AND o.id = l.id AND l.xtype = t .xtype AND l.cdefault = c.id AND l.cdefault <> 0
UNION SELECT o.name t_name, l.name t_columns, t .name t_type, l.length, l.isnullable, '' AS text, l.colorder
FROM sysobjects o, syscolumns l, systypes t
WHERE o.xtype = 'u' AND o.id = l.id AND l.xtype = t .xtype AND l.cdefault = 0
第二步查询
SELECT
(case when a.colorder=1 then d.name else '' end)表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by a.id,a.colorder
不是我写的,是引用别人的Top
6 楼Teng_s2000（分涨的太慢,升星难啊!!）回复于 2006-03-01 12:48:12 得分 10
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
SET LANGUAGE 'Simplified Chinese'
go
DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
PRINT @sql
print @sample
print @tbl
EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
select * from ##tx
这个代码好像是邹老大写的。
在2000，2005 上都可以Top
7 楼nhr（无名氏）回复于 2006-03-01 13:40:07 得分 0
我用的是SQL2000！~谢谢楼上各位大大。我现在每个试下：）Top
8 楼nhr（无名氏）回复于 2006-03-01 13:45:56 得分 0
哦。是这样的。数据库不是在我机器上。我是远程调用啊。因为原来的服务器进不去了。所以我想在本机通过程序来把数据库的结构读出来。然后再重新做一个库！~~Top
9 楼lsqkeke（可可）回复于 2006-03-01 14:39:29 得分 0
我提供的那个是在SQL2000下运行的，显示出本机数据库中所有的表中字段的信息Top
10 楼nhr（无名氏）回复于 2006-03-01 15:21:49 得分 0
哎。。我用新建SQL注册就提示超时~~好郁闷啊Top
相关问题
- SQL Server 表默认不显示 Foreign Key , 怎么让他们显示出来
- 在sql server 2000中如何让Enterprice Manage 中不显示系统表，只显示用户表？
- 为什么我的sql server服务管理器 里面不显示列表？
- 如何在SQL SERVER里实时地显示SYBASE数据库的表记录
- 如何使excel表读取显示数据库中（sql server）指定的数据
- 想显示一个数据库里所有的表名称该用什么Sql 语句（ms sql server)
- 如何把几个表合成一个大表然后显示出来？类似Sql Server 2000的视图!
- 请问在Access中,哪个系统表可以显示 象Sql server 2000 中的系统表syscolumns 中的信息?
- 怎样让SQL只显示用户表？
- 求显示表结构SQL命令
