Oracle,MySql,SQL Server查看表的创建语句

很多时候,我们想查看数据库中表的结构,当然,如果我们手头有工具的话,只是轻而易举的,但是在没有现成的工具的时候,我们应该怎么做呢,下面就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采用自带的工具,由于操作简单,这里就不在过多的介绍了,有兴趣的朋友可以试试。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值