1.生成标准的表结构SQL语句:用于整理表结构
/***************************************************
create Procedure GetAllTableInfo Asbegin DECLARE @au_id varchar(11), @strName varchar(50), @Tid int,@strOwner varchar(50) --获取所有的用户表信息 DECLARE All_Tables CURSOR FOR select convert(varchar,Name),ID from sysobjects where xtypE='U' order by Name Create Table #ColInfo ( TName varchar(50), --表名称 ColName varchar(50), --列名称 REMARK sql_variant, --注释 Type varchar(50), --数据类型 DefaultValue varchar(255), --缺省值 IsAllowNull varchar(10), --是否允许为空 IsPrimaryKey char(2) --是否为主键,用*标记 ) --主键信息 Create Table #PkInfo ( TABLE_QUALIFIER varchar(50), TABLE_OWNER varchar(50), TABLE_NAME varchar(50), COLUMN_NAME varchar(50), KEY_SEQ int, PK_NAME varchar(50) ) set @strOwner=user_name() OPEN All_Tables FETCH NEXT FROM All_Tables into @strName,@Tid while @@FETCH_STATUS = 0 begin --插入表及其注释信息 Insert Into #ColInfo SELECT @StrName,'',isnull(Value,''),'','','','' FROM (select @strName TName) A left join ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL) --表的注释信息 ON 1=1 --插入列信息 insert into #ColInfo select @strName,C.name CName,convert(varchar,R.value)+(case c.status when 0x80 then char(13)+char(10)+'自动增长列' else '' end), (case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')' else T.name+'('+convert(varchar,C.length)+')' end), isnull(Def.text,''), case isnullable when 1 then '是' else '否' end, '' from (systypes T inner join ( syscolumns C left join SysComments Def on C.cdefault=Def.id and 1=Def.colid ) on T.xtype=C.xtype and T.xusertype=C.xusertype ) left join ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息 on convert(varchar,R.objname)=convert(varchar,C.Name) where C.id=object_id(@strName) insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT fetch next from All_Tables into @strName,@Tid end --更新主键信息 update #ColInfo set IsPrimaryKey='是' from #PkInfo where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName Close All_Tables DEALLOCATE All_Tables select '%%%%' ObjName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,TName,0 flag from #ColInfo where ColName='' union select '表名:'+TName,REMARK,'','','','',TName,1 flag from #ColInfo where ColName='' union select '列名','注释','数据类型','缺省值','是否允许空','主键',TName,2 from #ColInfo where ColName='' union select ColName,REMARK,Type,DefaultValue,IsAllowNull, IsPrimaryKey,TName,3 from #ColInfo where ColName<>'' order by 7,8,3 drop table #PkInfo drop table #ColInfo end
---测试 Exec GetAllTableInfo2. MS SQL Sever 提供的查询分析器中,
Exec GetAllTableInfo3. 将查询结果信息,复制到MS Excel Sheet中
4. 在MS Excel Sheet中,删除掉最后两列
5. 全选Sheet信息,复制到MS Word中,执行下面 Word宏命令
Sub Macro1()'' Macro1 Macro' 宏在 2004-3-12 由 徐侠君 录制'A: Selection.Find.ClearFormatting With Selection.Find .Text = "%%%%" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchByte = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With If Selection.Find.Execute Then Selection.Delete Unit:=wdCharacter, Count:=1 Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Cells.Merge Selection.MoveDown Unit:=wdLine, Count:=1 Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.MoveRight Unit:=wdCharacter, Count:=1 Selection.SelectCell Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend Selection.Cells.Merge Selection.MoveDown Unit:=wdLine, Count:=1 Selection.HomeKey Unit:=wdLine, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=2 Selection.HomeKey Unit:=wdLine Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.MoveDown Unit:=wdLine, Count:=2 GoTo A Else MsgBox "修改各是成功" End If End Sub
/***************************************************
create Procedure GetAllTableInfo Asbegin DECLARE @au_id varchar(11), @strName varchar(50), @Tid int,@strOwner varchar(50) --获取所有的用户表信息 DECLARE All_Tables CURSOR FOR select convert(varchar,Name),ID from sysobjects where xtypE='U' order by Name Create Table #ColInfo ( TName varchar(50), --表名称 ColName varchar(50), --列名称 REMARK sql_variant, --注释 Type varchar(50), --数据类型 DefaultValue varchar(255), --缺省值 IsAllowNull varchar(10), --是否允许为空 IsPrimaryKey char(2) --是否为主键,用*标记 ) --主键信息 Create Table #PkInfo ( TABLE_QUALIFIER varchar(50), TABLE_OWNER varchar(50), TABLE_NAME varchar(50), COLUMN_NAME varchar(50), KEY_SEQ int, PK_NAME varchar(50) ) set @strOwner=user_name() OPEN All_Tables FETCH NEXT FROM All_Tables into @strName,@Tid while @@FETCH_STATUS = 0 begin --插入表及其注释信息 Insert Into #ColInfo SELECT @StrName,'',isnull(Value,''),'','','','' FROM (select @strName TName) A left join ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL) --表的注释信息 ON 1=1 --插入列信息 insert into #ColInfo select @strName,C.name CName,convert(varchar,R.value)+(case c.status when 0x80 then char(13)+char(10)+'自动增长列' else '' end), (case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')' else T.name+'('+convert(varchar,C.length)+')' end), isnull(Def.text,''), case isnullable when 1 then '是' else '否' end, '' from (systypes T inner join ( syscolumns C left join SysComments Def on C.cdefault=Def.id and 1=Def.colid ) on T.xtype=C.xtype and T.xusertype=C.xusertype ) left join ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息 on convert(varchar,R.objname)=convert(varchar,C.Name) where C.id=object_id(@strName) insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT fetch next from All_Tables into @strName,@Tid end --更新主键信息 update #ColInfo set IsPrimaryKey='是' from #PkInfo where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName Close All_Tables DEALLOCATE All_Tables select '%%%%' ObjName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,TName,0 flag from #ColInfo where ColName='' union select '表名:'+TName,REMARK,'','','','',TName,1 flag from #ColInfo where ColName='' union select '列名','注释','数据类型','缺省值','是否允许空','主键',TName,2 from #ColInfo where ColName='' union select ColName,REMARK,Type,DefaultValue,IsAllowNull, IsPrimaryKey,TName,3 from #ColInfo where ColName<>'' order by 7,8,3 drop table #PkInfo drop table #ColInfo end
---测试 Exec GetAllTableInfo2. MS SQL Sever 提供的查询分析器中,
Exec GetAllTableInfo3. 将查询结果信息,复制到MS Excel Sheet中
4. 在MS Excel Sheet中,删除掉最后两列
5. 全选Sheet信息,复制到MS Word中,执行下面 Word宏命令
Sub Macro1()'' Macro1 Macro' 宏在 2004-3-12 由 徐侠君 录制'A: Selection.Find.ClearFormatting With Selection.Find .Text = "%%%%" .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchByte = True .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With If Selection.Find.Execute Then Selection.Delete Unit:=wdCharacter, Count:=1 Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Cells.Merge Selection.MoveDown Unit:=wdLine, Count:=1 Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.MoveRight Unit:=wdCharacter, Count:=1 Selection.SelectCell Selection.MoveRight Unit:=wdCharacter, Count:=4, Extend:=wdExtend Selection.Cells.Merge Selection.MoveDown Unit:=wdLine, Count:=1 Selection.HomeKey Unit:=wdLine, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=2 Selection.HomeKey Unit:=wdLine Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.Font.Bold = wdToggle Selection.MoveDown Unit:=wdLine, Count:=2 GoTo A Else MsgBox "修改各是成功" End If End Sub
解决方案 »
- 一sql语句能实现吗
- sql 语句 求助
- SQL Server 2000 数据库文件被破坏怎样修复
- 卸载SQL SERVER 2005 是否影响SQL Server 2000
- SQL Server和Oracle并行处理比较分析 请问这篇文章的分析正确么?
- 我在win2003R2标准版+sp1上安装sql2000出现如下问题:,很急!在线等!!!!
- 一个SQL SERVER 2000 的登陆问题。
- Insert Into 目标数据表 Select * From 源数据表 的问题????????
- 各位来指教,迷茫,迷茫,这么多数据库技术,我要从哪开始呢?有分,有分.............
- 登录问题,为何我的C/S程序用户如不登录到NT域内就不能使用程序,我已设好了服务器的IP
- 交换两条记录中某一列的值
- 请教这三个表的设计,碰到麻烦。
在Sqlserver的其业管理器选中你要导出结构的表,按Shift可以多选,
然后点右键->复制
然后在文本文件中粘贴,这些表的结构全都过来了