SQL SERVER导出所有表及表字段

/*
Goal：MS SQL SERVER：Output all the field and its’ table
author:AllisonHuang
created date :20140613
*/
--select [id], [name] from [sysobjects] where [type] = 'u' order by [name]
drop table  #temping
create table #temping (id varchar(200),name varchar(200),namedetail varchar(200))
--SELECT name,id FROM SysColumns WHERE id=Object_Id('ABUSEDSTOCK') 
declare @id varchar(20)
declare @name varchar(20)

declare cursor1 cursor for         --定义游标cursor1
select [id], [name] from [sysobjects] where [type] = 'u' order by [name]
open cursor1                       --打开游标
fetch next from cursor1 into @id,@Name --将游标向下移行，获取的数据放入之前定义的变量@id,@NUM中
while @@fetch_status=0           --判断是否成功获取数据
begin
insert into  #temping 
SELECT id,@name,name FROM SysColumns WHERE id=Object_Id(@Name) 
--id is Object_Id(@Name)'s id ,not [sysobjects]'s.
fetch next from cursor1 into @id,@Name  --将游标向下移行
end
close cursor1                   --关闭游标
deallocate cursor1
select * from #temping

 oracle：

导出所有表名+字段名：

select t.TABLE_NAME 表名 ,t.COLUMN_ID 序号 ,t.COLUMN_NAME 字段名 ,t.DATA_TYPE 类型 ,t. DATA_LENGTH 长度 ,t.NULLABLE 是否为空
from user_tab_columns t

 

其他：

SELECT OBJECT_NAME(sc.object_id)TABLE_NAME ,* FROM sys.index_columns SC

SELECT OBJECT_NAME(I.object_id),* FROM sys.indexes I
--AND sc.object_id = i.object_id
--AND sc.index_id = i.index_id
SELECT * FROM sys.columns --所有表字段

转载于:https://www.cnblogs.com/watermarks/p/3793843.html