澳门新萄京sqlserver 临时表创立 查询 删除

sqlserver 有的时候表创制 查询 删除

从SQLSELANDVECR-V6.5初叶,MS提供了一个十二分实用的系统存款和储蓄进程sp_MSforeachtable和sp_MSforeachDB;作为DBA会平时索要检查有着的数据库或客户表,例如:检查有着数据库的体积;看看钦赐数据库全数客户表的容积,全部表的记录数…,大家常常管理那样的主题材料都以用游标分别管理管理,比如:在数据库检索效用相当的慢时,大家想检查数据库全体的顾客表,我们就必须要那样写游标:
DECLARE @TableName varchar(255)
DECLARE @ExeSQL varchar(4000)

澳门新萄京 1–定期三头服务器上的数据 
澳门新萄京 2–例子:
澳门新萄京 3–测验意况,SQL Server二零零四,远程服务器名:xz,客户名为:sa,无密码,测验数据库:test
澳门新萄京 4–服务器上的表(查询解析器连接到服务器上创造卡塔尔(قطر‎
澳门新萄京 5create table [user](id int primary key,number varchar(4),name varchar(10))
澳门新萄京 6go
澳门新萄京 7–以下在局域网(本机操作卡塔尔
澳门新萄京 8–本机的表,state表明:null 表示新添记录,1 代表改正过的笔录,0 代表无变化的记录
澳门新萄京 9if exists (select * from dbo.sysobjects where id = object_id(N'[user]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
澳门新萄京 10 drop table [user]
澳门新萄京 11GO
澳门新萄京 12create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
澳门新萄京 13go
澳门新萄京 14–成立触发器,维护state字段的值
澳门新萄京 15create trigger t_state on [user]
澳门新萄京 16after update
澳门新萄京 17as
澳门新萄京 18update [user] set state=1
澳门新萄京 19from [user] a join inserted b on a.id=b.id
澳门新萄京 20where a.state is not null
澳门新萄京 21go
澳门新萄京 22
澳门新萄京 23–为了有助于同步管理,创立链接服务器到要一并的服务器
澳门新萄京 24–这里的长间距服务器名称叫:xz,顾客名叫:sa,无密码
澳门新萄京 25if exists(select 1 from master..sysservers where srvname=’srv_lnk’)
澳门新萄京 26 exec sp_dropserver ‘srv_lnk’,’droplogins’
澳门新萄京 27go
澳门新萄京 28exec sp_addlinkedserver  ‘srv_lnk’,”,’SQLOLEDB’,’xz’
澳门新萄京 29exec sp_addlinkedsrvlogin ‘srv_lnk’,’false’,null,’sa’
澳门新萄京 30go
澳门新萄京 31
澳门新萄京 32–创造同步管理的囤积进度
澳门新萄京 33if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
澳门新萄京 34 drop procedure [dbo].[p_synchro]
澳门新萄京 35GO
澳门新萄京 36create proc p_synchro
澳门新萄京 37as
澳门新萄京 38–set  XACT_ABORT on
澳门新萄京 39–运营远程服务器的MSDTC服务
澳门新萄京 40–exec master..xp_cmdshell ‘isql /S”xz” /U”sa” /P”” /q”exec master..xp_cmdshell ”net start msdtc”,no_output”‘,no_output
澳门新萄京 41
澳门新萄京 42–运维本机的MSDTC服务
澳门新萄京 43–exec master..xp_cmdshell ‘net start msdtc’,no_output
澳门新萄京 44
澳门新萄京 45–实行布满事务管理,假使表用标志列做主键,用上边包车型大巴方式
澳门新萄京 46–BEGIN DISTRIBUTED TRANSACTION
澳门新萄京 47 –同步删除的数据
澳门新萄京 48 delete from srv_lnk.test.dbo.[user]
澳门新萄京 49  where id not in(select id from [user])
澳门新萄京 50
澳门新萄京 51 –同步大幅度增加的数码
澳门新萄京 52 insert into srv_lnk.test.dbo.[user]
澳门新萄京 53 select id,number,name from [user] where state is null
澳门新萄京 54 
澳门新萄京 55 –同步修正的多少
澳门新萄京 56 update srv_lnk.test.dbo.[user] set
澳门新萄京 57  number=b.number,name=b.name
澳门新萄京 58 from srv_lnk.test.dbo.[user] a
澳门新萄京 59  join [user] b on a.id=b.id
澳门新萄京 60 where b.state=1
澳门新萄京 61 
澳门新萄京 62 –同步后更新本机的标识
澳门新萄京 63 update [user] set state=0 where isnull(state,1)=1
澳门新萄京 64–COMMIT TRAN
澳门新萄京 65go
澳门新萄京 66
澳门新萄京 67–创制作业,按时施行多少同步的积累进度
澳门新萄京 68if exists(SELECT 1 from msdb..sysjobs where name=’数据管理’卡塔尔
澳门新萄京 69 EXECUTE msdb.dbo.sp_delete_job @job_name=’数据管理’
澳门新萄京 70exec msdb..sp_add_job @job_name=’数据管理’
澳门新萄京 71
澳门新萄京 72–成立作业步骤
澳门新萄京 73declare @sql varchar(800),@dbname varchar(250)
澳门新萄京 74select @sql=’exec p_synchro’   –数据管理的下令
澳门新萄京 75 ,@dbname=db_name(卡塔尔国   –施行多少管理的数码库名
澳门新萄京 76
澳门新萄京 77exec msdb..sp_add_jobstep @job_name=’数据管理’,
澳门新萄京 78 @step_name = ‘数据同步’,
澳门新萄京 79 @subsystem = ‘TSQL’,
澳门新萄京 80 @database_name=@dbname,
澳门新萄京 81    @command = @sql,
澳门新萄京 82 @retry_attempts = 5,   –重试次数
澳门新萄京 83 @retry_interval = 5    –重试间距
澳门新萄京 84
澳门新萄京 85–创造调解
澳门新萄京 86EXEC msdb..sp_add_jobschedule @job_name = ‘数据管理’, 
澳门新萄京 87 @name = ‘时间安插’,
澳门新萄京 88 @freq_type = 4,     –每天
澳门新萄京 89 @freq_interval = 1,    –天天推行三次
澳门新萄京 90 @active_start_time = 00000  –0点执行
澳门新萄京 91go
澳门新萄京 92
澳门新萄京 93

create table temptablename ( id int identity (1,1卡塔尔国 not null, a1
varchar(50卡塔尔, a2 varchar(50State of Qatar, a3 varchar(50卡塔尔, primary key (id)–定义id为不常表#tmp的主键 )

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

select [澳门新萄京,字段1,字段2,…,] into #tmp from table

OPEN Table_Cursor
FETCH NEXT FROM   Table_Cursor INTO @TableName

询问有的时候表的数量 select * from #tmp

WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @TableName
SELECT @ExeSQL=”DBCC
CHECKTABLE(””+@TableName+””)””)
EXEC(@EXESQL)
FETCH NEXT FROM   Table_Cursor INTO @TableName
END

除去一时表 drop table #tmp

CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO

创制三个不被电动回笼的大局一时表。未有经过测量检验,尽管平时看上去没啥意思,和常规表有何样界别?有可能今后有用,所以依旧记下来:

     要是大家用sp_MSforeachtable就足以非常常有益的实现相似的目标:
EXEC sp_MSforeachtable @command1=”print ”?” DBCC CHECKTABLE(”?”)”
世家能够看看这样就越来越洗练(就算在后台也是由此游标来拍卖的卡塔尔国,下边大家就留神解析一下sp_MSforeachtable这一个蕴藏进程:

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’;

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

独有体现删除##globals才会被消亡

–下面时sp_MSforeachtable的原始代码

注脚本地一时表的名号以单个数字符号 (#)打头;它们仅对目前的顾客连接是可以知道的;当客户从 sql server
实例断开连接时被删去。全局不常表的名号以四个数字符号 (##卡塔尔(قطر‎打头,创立后对别的客商都以可以知道的,当有着引用该表的客商从 sql server
断开连接时被去除。

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

表变量相当于ado的recordset,速度比有的时候表快得多。
表变量无法用在下列语句中:

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

insert into table_variable exec 存款和储蓄进程。 select select_list into
table_variable 语句。

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

在概念 table 变量的函数、存款和储蓄进程或批处理完成时,自动驱除 table 变量。
但一时表辅助。 .表变量速度比一时表快得多 若是数据量十分的小:

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

/* Create the select */
    exec(N”declare hCForEach cursor global for select ”[” +
REPLACE(user_name(uid), N”]”, N”]]”) + ”]” + ”.” + ”[”

+ REPLACE(object_name(id), N”]”, N”]]”) + ”]” from
dbo.sysobjects o ”
          + N” where OBJECTPROPERTY(o.id, N”IsUserTable”) = 1 ” +
N” and o.category & ” + @mscat + N” = 0 ”
          + @whereand)
declare @retval int
select @retval = @@error
if (@retval = 0)
   exec @retval = sp_MSforeach_worker @command1, @replacechar,
@command2, @command3

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

return @retval

那一个系统存款和储蓄进程有7个参数:
@command1 nvarchar(二〇〇三卡塔尔国,   –第一条运营的T-SQL指令
@replacechar nchar(1卡塔尔(قطر‎ = N”?”,    –钦定的占位符号
@command2 nvarchar(二零零一卡塔尔(قطر‎ = null,–第二条运营的T-SQL指令
     @command3 nvarchar(2004卡塔尔(قطر‎ = null, –第三条运转的T-SQL指令
@whereand nvarchar(二零零零卡塔尔(قطر‎ = null, –可选条件来采纳表
@precommand nvarchar(二〇〇二卡塔尔国 = null, –在表前试行的命令
@postcommand nvarchar(二零零零卡塔尔(قطر‎ = null –在表后执行的授命

为此地点的口舌也得以这么写:
EXEC sp_MSforeachtable @command1=”print ”?””,
          @command2= “DBCC CHECKTABLE(”?”)”

刺探参数现在,就让大家做多少个实列吧:
1.拿走种种表的记录数和容积:
EXEC sp_MSforeachtable @command1=”print ”?””,
          @command2=”sp_spaceused ”?””,
          @command3= “SELECT count(*) FROM ? “

2.更新PUBS数据库中已t开始的全体表的总结:
EXEC sp_MSforeachtable @whereand=”and name like ”t%””,
          @replacechar=”*”,
          @precommand=”print ”Updating Statistics…..” print ””,
          @command1=”print ”*” update statistics * “,
          @postcommand= “print”print ”Complete Update Statistics!””

参数@whereand的用法:

@whereand参数在存款和储蓄进程中起到指令条件节制的功力,具体的写法如下:
@whereend,能够如此写 @whereand=’ AND o.name in
(”Table1”,”Table2”,…….卡塔尔(قطر‎’
譬喻说:笔者想翻新Table1/Table2/Table3中NOTE列为NULL的值
sp_MSforeachtable @command1=’Update ? Set NOTE=”” Where NOTE is
NULL’,@whereand=’ AND o.name in
(”Table1”,”Table2”,”Table3”)’

sp_MSforeachDB除了@whereand外,和sp_MSforeachtable的参数是一模二样的,大家得以透过这么些蕴藏过程检查评定全体的数据库,比方:
1.收获全数的数据库的积累空间:
        EXEC sp_MSforeachdb   @command1=”print ”?””,
                                          @command2=”sp_spaceused “
2.检查有着的数据库
        EXEC sp_MSforeachdb   @command1=”print ”?””,
                                            @command2=”DBCC CHECKDB (?)

有了上面包车型客车解析,我们得以创建和煦的sp_MSforeachObject:
USE MASTER
GO
CREATE proc sp_MSforeachObject
@objectType int=1,
@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

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

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

发表评论

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

网站地图xml地图