导出数据结构
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%"> </div></td> <td><div align="center" width="60%">表名:<%=Rstable(0)%></div></td> <td><div align="center" width="20%"> </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 %>
