导出数据结构

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO

--此存储过程主要用来导出表的相关结构CREATE PROCEDURE Get_TableInfo

(@TableName nvarchar(250)

)

AS

SELECT

CAST(a.name AS nvarchar(250)) fieldname,

CAST((case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√' else '×' end) AS nvarchar(250)) fieldbs,

(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) fieldpk,

CAST(b.name AS nvarchar(250)) fieldtype, CAST(COLUMNPROPERTY(a.id,a.name,'PRECISION') AS nvarchar(250)) as fieldlength,

CAST((case when a.isnullable=1 then '√' else '×' end) AS nvarchar(250)) fieldallownull,

CAST((case when isnull(e.text,'')='' then '无' else e.text end) AS nvarchar(250)) fielddefault,

CAST((case when isnull(g.[value],'')='' then '无' else g.[value] end) AS nvarchar(250)) AS fielddescribe

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=@TableName

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

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO

--此存储过程用来导出当前数据库所有用户表CREATE PROCEDURE Get_TableName

ASselect O.name TableName from sysobjects O where O.Type='U' and O.name<>'dtproperties'

GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

ASP导出：

<!--#include file="conn/conn.asp"--><%Set mycommtable=Server.CreateObject("Adodb.Command")With mycommtable .ActiveConnection=connstr.CommandText="[Get_TableName]".CommandType=4.PrePared = TrueEnd WithSet Rstable=mycommtable.ExecuteIf Not Rstable.eof Then Do While Not Rstable.eof%><table width="100%" border="1" cellspacing="0" cellpadding="0"> <tr> <td><div align="center" width="20%">&nbsp;</div></td> <td><div align="center" width="60%">表名:<%=Rstable(0)%></div></td> <td><div align="center" width="20%">&nbsp;</div></td> </tr></table><table width="100%" border="1" cellspacing="0" cellpadding="0"> <tr> <td><div align="center" width="20%">字段名称</div></td> <td><div align="center" width="5%">是否唯一标识</div></td> <td><div align="center" width="5%">是否为主键</div></td> <td><div align="center" width="10%">字段类型</div></td> <td><div align="center" width="10%">字段长度</div></td> <td><div align="center" width="5%">是否不能为空</div></td> <td><div align="center" width="20%">默认值</div></td> <td><div align="center" width="25%">字段描述</div></td> </tr>

<%Set mycommfield=Server.CreateObject("Adodb.Command")With mycommfield .ActiveConnection=connstr.CommandText="[Get_TableInfo]".CommandType=4.PrePared = True.ParaMeters.append .CreateParameter("@TableName",200,1,255,Rstable(0))End WithSet Rsfield= mycommfield.ExecuteIf Not Rsfield.eof Then Do While Not Rsfield.eof%> <tr> <td><div align="center" width="20%"><%=Rsfield(0)%></div></td> <td><div align="center" width="5%"><%=Rsfield(1)%></div></td> <td><div align="center" width="5%"><%=Rsfield(2)%></div></td> <td><div align="center" width="10%"><%=Rsfield(3)%></div></td> <td><div align="center" width="10%"><%=Rsfield(4)%></div></td> <td><div align="center" width="5%"><%=Rsfield(5)%></div></td> <td><div align="center" width="20%"><%=Rsfield(6)%></div></td> <td><div align="center" width="25%"><%=Rsfield(7)%></div></td> </tr>

<% Rsfield.MoveNext LoopEnd If%>

</table><% Rstable.MoveNext LoopEnd if%>