临时表创建,server中遍历系统所有表及库的存储过程

select [字段1,字段2,…,] into #tmp from table

own result set */
/* @precommand and @postcommand may be used to force a single result
set via a temp table. */

在定义 table 变量的函数、存储过程或批处理结束时,自动清除 table 变量。
但临时表支持。 .表变量速度比临时表快得多 如果数据量不大:

sp_MSforeachDB除了@whereand外,和sp_MSforeachtable的参数是一样的,我们可以通过这个存储过程检测所有的数据库,比如:
1.获得所有的数据库的存储空间:
        EXEC sp_MSforeachdb   @command1=”print ”?””,
                                          @command2=”sp_spaceused “
2.检查所有的数据库
        EXEC sp_MSforeachdb   @command1=”print ”?””,
                                            @command2=”DBCC CHECKDB (?)

表变量相当于ado的recordset,速度比临时表快得多。
表变量不能用在下列语句中:

–先禁用所有外键约束 
exec sp_msforeachtable “alter table ? nocheck CONSTRAINT all” 
–然后删除数据 
exec sp_msforEachTable “TRUNCATE TABLE?” 

查询临时表的数据 select * from #tmp

return @retval

创建一个不被自动回收的全局临时表。没有经过测试,虽然貌似看上去没啥意义,和常规表有什么区别?说不定以后有用,所以还是记下来:

所有表都加上一个自增长的字段

insert into table_variable exec 存储过程。 select select_list into
table_variable 语句。

这个系统存储过程有7个参数:
@command1 nvarchar(2000),   –第一条运行的T-SQL指令
@replacechar nchar(1) = N”?”,    –指定的占位符号
@command2 nvarchar(2000) = null,–第二条运行的T-SQL指令
     @command3 nvarchar(2000) = null, –第三条运行的T-SQL指令
@whereand nvarchar(2000) = null, –可选条件来选择表
@precommand nvarchar(2000) = null, –在表前执行的指令
@postcommand nvarchar(2000) = null –在表后执行的指令

只有显示删除##globals才会被清除

if (@precommand is not null)
   exec(@precommand)

create table temptablename ( id int identity (1,1) not null, a1
varchar(50), a2 varchar(50), a3 varchar(50), primary key (id)
–定义id为临时表#tmp的主键 )

REPLACE(object_name(id), N”]”, N”]]”) + ”]” from
dbo.sysobjects o ”
         + N” where OBJECTPROPERTY(o.id,
N””+@isobject+””)
= 1 ”+N” and o.category & ” + @mscat + N” = 0 ”
        + @whereand)

sqlserver 临时表创建 查询 删除

DECLARE Table_Cursor CURSOR FOR SELECT [name] FROM sysobjects WHERE
xtype=”U”

删除临时表 drop table #tmp

select @isobject= case @objectType when 1 then ”IsUserTable”
          when 2 then ”IsView”
          when 3 then ”IsTrigger”
          when 4 then ”IsProcedure”
          when 5 then ”IsDefault”  
          when 6 then ”IsForeignKey”
          when 7 then ”IsScalarFunction”
          when 8 then ”IsInlineFunction”
          when 9 then ”IsPrimaryKey”
          when 10 then ”IsExtendedProc”   
          when 11 then ”IsReplProc”
          when 12 then ”IsRule”
                   end

use master;goif object_id(‘dbo.sp_globals’) is not nullnbsp; drop
proc dbo.sp_globalsgocreate proc dbo.sp_globalsascreate table
##globals(id varchar(36) not null primary keyvalue
varchar(500))gosp内容

exec dbo.sp_procoption ‘dbo.sp_globals’,’startup’,’true’;

if (@retval = 0 and @postcommand is not null)
   exec(@postcommand)

注明本地临时表的名称以单个数字符号 (#)
打头;它们仅对当前的用户连接是可见的;当用户从 sql server
实例断开连接时被删除。全局临时表的名称以两个数字符号 (##)
打头,创建后对任何用户都是可见的,当所有引用该表的用户从 sql server
断开连接时被删除。

eg3:

所以上面的语句也可以这样写:
EXEC sp_MSforeachtable @command1=”print ”?””,
          @command2= “DBCC CHECKTABLE(”?”)”

大家知道对一个表加的话只需: alter table 表名 add iid int identity(1,1)

结论: 看了很多例子以后发现sp_msforeachtable
里面那个问号指代数据库中所有的表

大家知道,如果将一个表所有者改为dbo,只需


delete from AAA

了解参数以后,就让我们做几个实列吧:
1.获得每个表的记录数和容量:
EXEC sp_MSforeachtable @command1=”print ”?””,
          @command2=”sp_spaceused ”?””,
          @command3= “SELECT count(*) FROM ? “

exec sp_msforeachtable ”insert AAA(tablename, [rowcount]) select
N””?””, count(*) from ?”

当然,可能运行时会有错(比如有些表中没有num字段),不用理它,运行一遍就行了。

我们看看sp_MSforeachtable详细的CODE:
USE MASTER
GO
SP_HELPTEXT sp_MSforeachtable

只需exec sp_MSForEachTable ”sp_changeobjectowner ””?””,
””dbo”””

若需对所有的表都加的话只需: sp_MSForEachTable ”alter table ? add iid
int identity(1,1)”

系统未公开的存储过程sp_msforeachtable 的用法

sp_changeobjectowner ”表名”,”dbo”即可

当然,可能运行时会有错(比如加字段时该表中已有该字段,删除字段时没有该字段),不用理它,运行一遍就行了。

同理,删除所有表中的iid字段只需 sp_MSForEachTable ”alter table ? drop
column iid”

2、成功。然后导入你的数据或重新填写数据。

return @retval

CREATE proc sp_MSforeachtable
@command1 nvarchar(2000), @replacechar nchar(1) = N”?”, @command2
nvarchar(2000) = null,
    @command3 nvarchar(2000) = null, @whereand nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
/* This proc returns one or more rows for each table (optionally,
matching @where), with each table defaulting to its

从SQLSERVER6.5开始,MS提供了一个非常有用的系统存储过程sp_MSforeachtable和sp_MSforeachDB;作为DBA会经常需要检查所有的数据库或用户表,比如:检查所有数据库的容量;看看指定数据库所有用户表的容量,所有表的记录数…,我们一般处理这样的问题都是用游标分别处理处理,比如:在数据库检索效率非常慢时,我们想检查数据库所有的用户表,我们就必须这样写游标:
DECLARE @TableName varchar(255)
DECLARE @ExeSQL varchar(4000)

select * from AAA where AAA.[rowcount]>0


sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

if (@retval = 0 and @postcommand is not null)
   exec(@postcommand)

/* Preprocessor won”t replace within quotes so have to use str().
*/
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

if (@precommand is not null)
   exec(@precommand)

发表评论

电子邮件地址不会被公开。 必填项已用*标注