很多时候，我们想查看数据库中表的结构，当然，如果我们手头有工具的话，只是轻而易举的，但是在没有现成的工具的时候，我们应该怎么做呢，下面就Oracle,MySql,SQL Server分别做介绍：
Oracle
oracle要查看创建表语句使用sqlplus，过程稍微麻烦了一点。
1、调出SQL*Plus
conn scott/tiger@orcl
create table a(a number);
insert into a values(1);
insert into a values(2);
insert into a values(3);
create table b(a number,b varchar2(10));
insert into b values(1,'1111');
insert into b values(2,'2222');
insert into b values(3,'3333');
commit;
2、打开一个DOS窗口、先执行导出
E:\>exp a/a file=a.dmp log=loga.txt
Export: Release 8.1.6.0.0 - Production on 星期五 12月 1 22:24:16 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved. 网管网www_bitscn_com
连接到: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
已导出ZHS16GBK字符集和ZHS16GBK NCHAR 字符集
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户A的外部函数程序库名称
. 正在导出用户A的对象类型定义
即将导出A的对象 ...
. 正在导出数据库链接
. 正在导出序号
. 正在导出群集定义
. 即将导出A的表通过常规路径 ...
. . 正在导出表 A 3 行被导出
. . 正在导出表 B 3 行被导出
. 正在导出同义词
. 正在导出视图
. 正在导出存储的过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图、功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出快照
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计
在没有警告的情况下成功终止导出。
E:\>
3、再执行导入，使用show=y、log这两个选项
E:\>imp a/a file=a.dmp show=y log=logb.txt
Import: Release 8.1.6.0.0 - Production on 星期五 12月 1 22:29:49 2000
(c) Copyright 1999 Oracle Corporation. All rights reserved.
连接到: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
经由常规路径导出由EXPORT:V08.01.06创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
. 正在将A的对象导入到 A
"CREATE TABLE "A" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 LOGGING STORAGE(INITIAL 131072 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483"
"645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLE"
"SPACE "SYSTEM""
. . 正在跳过表 "A"
"CREATE TABLE "B" ("A" NUMBER, "B" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INIT"
"RANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 131072 NEXT 65536 MINEXTENTS 1 " 中国网管联盟bitsCN.com
"MAXEXTENTS 2147483645 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_P"
"OOL DEFAULT) TABLESPACE "SYSTEM""
. . 正在跳过表 "B"
成功终止导入，但出现警告。
E:\>
4、使用编辑器打开logb.txt，里面可以看到DDL语句
MySql
Mysql使用MySQL Command Line Client，操作非常简单
1.打开MySQL Command Line Client，并登陆
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
2.创建表
mysql> use mysql
Database changed
mysql> create table t_test (id int,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.11 sec)
3.下面就查看我们刚才创建表的SQL语句
mysql> show create table t_test;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| Table | Create Table
|
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
| t_test | CREATE TABLE `t_test` (
`id` int(11) NOT NULL default '0',
`name` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------+
1 row in set (0.00 sec)
这样就可以了。
SQL Server
以SQL Server 2000为例，SQL Server显示创建表的SQL语句更加麻烦，要用到存储过程下面的代码摘自互联网
create procedure SP_GET_TABLE_INFO
@ObjName varchar(128) /* The table to generate sql script */
as
declare @Script varchar(255)
declare @ColName varchar(30)
declare @ColID TinyInt
declare @UserType smallint
declare @TypeName sysname
declare @Length TinyInt
declare @Prec TinyInt
declare @Scale TinyInt
declare @Status TinyInt
declare @cDefault int
declare @DefaultID TinyInt
declare @Const_Key varchar(255)
declare @IndID SmallInt
declare @IndStatus Int
declare @Index_Key varchar(255)
declare @DBName varchar(30)
declare @strPri_Key varchar (255)
/*
** Check to see the the table exists and initialize @objid.
*/
if not Exists(Select name from sysobjects where name = @ObjName)
begin
select @DBName = db_name()
raiserror(15009,-1,-1,@ObjName,@DBName)
return (1)
end
create table #spscript
(
id int IDENTITY not null,
Script Varchar(255) NOT NULL,
LastLine tinyint
)
declare Cursor_Column INSENSITIVE CURSOR
for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status, a.cDefault,
case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key
from syscolumns a, systypes b where object_name(a.id) = @ObjName
and a.usertype = b.usertype order by a.ColID
set nocount on
Select @Script = 'Create table ' + @ObjName + '('
Insert into #spscript values(@Script,0)
/* Get column information */
open Cursor_Column
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
Select @Script = ''
while (@@FETCH_STATUS <> -1)
begin
if (@@FETCH_STATUS <> -2)
begin
Select @Script = @ColName + ' ' + @TypeName
if @UserType in (1,2,3,4)
Select @Script = @Script + '(' + Convert(char(3),
@Length) + ') '
else if @UserType in (24)
Select @Script = @Script + '(' + Convert(char(3),@Prec) + ','
+ Convert(char(3),@Scale) + ') '
else
Select @Script = @Script + ' '
if ( @Status & 0x80 ) > 0
Select @Script = @Script + ' IDENTITY(1,1) '
if ( @Status & 0x08 ) > 0
Select @Script = @Script + ' NULL '
else
Select @Script = @Script + ' NOT NULL '
if @cDefault > 0
Select @Script = @Script + ' DEFAULT ' + @Const_Key
end
fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,
@Status,@cDefault,@Const_Key
if @@FETCH_STATUS = 0
begin
Select @Script = @Script + ','
Insert into #spscript values(@Script,0)
end
else
您正在看的SQLserver教程是:MS SQLSERVER 如何得到表的创建语句。; begin
Insert into #spscript values(@Script,1)
Insert into #spscript values(')',0)
end
end
Close Cursor_Column
Deallocate Cursor_Column
/* Get index information */
Declare Cursor_Index INSENSITIVE CURSOR
for Select name,IndID,status from sysindexes where object_name(id)=@ObjName
and IndID > 0 and IndID<>255 order by IndID /*增加了对InDid为255的判断*/
Open Cursor_Index
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
while (@@FETCH_STATUS <> -1)
begin
if @@FETCH_STATUS <> -2
begin
declare @i TinyInt
declare @thiskey varchar(50)
declare @IndDesc varchar(68) /* string to build up index desc in */
Select @i = 1
while (@i <= 16)
begin
select @thiskey = index_col(@ObjName, @IndID, @i)
if @thiskey is null
break
if @i = 1
select @Index_Key = index_col(@ObjName, @IndID, @i)
else
select @Index_Key = @Index_Key + ', ' + index_col(@ObjName, @IndID, @i)
select @i = @i + 1
end
if (@IndStatus & 0x02) > 0
Select @Script = 'Create unique '
else
Select @Script = 'Create '
if @IndID = 1
select @Script = @Script + ' clustered '
if (@IndStatus & 0x800) > 0
select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')'
else
select @strPri_Key = ''
if @IndID > 1
select @Script = @Script + ' nonclustered '
Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName
+ '(' + @Index_Key + ')'
Select @IndDesc = ''
/*
** See if the index is ignore_dupkey (0x01).
*/
if @IndStatus & 0x01 = 0x01
Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ','
/*
** See if the index is ignore_dup_row (0x04).
*/
/* if @IndStatus & 0x04 = 0x04 */
/* Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */ /* 2000 不在支持*/
/*
** See if the index is allow_dup_row (0x40).
*/
if @IndStatus & 0x40 = 0x40
Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ','
if @IndDesc <> ''
begin
Select @IndDesc = SubString( @IndDesc, 1, DataLength(@IndDesc) - 1 )
Select @Script = @Script + ' WITH ' + @IndDesc
end
/*
** Add the location of the data.
*/
end
if (@strPri_Key = '')
Insert into #spscript values(@Script,0)
else
update #spscript set Script = Script + @strPri_Key where LastLine = 1
Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus
end
您正在看的SQLserver教程是:MS SQLSERVER 如何得到表的创建语句。Close Cursor_Index
Deallocate Cursor_Index
Select Script from #spscript
set nocount off
return (0)
补充：采用工具显示创建表SQL是非常简单的，如oracle用PL/SQL，MySql用MySQL Administrator，SQL Server采用自带的工具，由于操作简单，这里就不在过多的介绍了，有兴趣的朋友可以试试。
扫一扫
1588
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
