这么简单的一个存储过程功能语法有误吗?帮我看一下,谢谢,散分

Question

create procedure p_DbToDb(@vFromDb varchar(50)) 
as 
begin    
   declare @tablename varchar(50) 
   declare @sql nvarchar(4000) 
   declare @num int
   declare @s as varchar(8000)
   if exists(Select name from sysobjects where Name='#') drop table #
   CREATE TABLE dbo.#(tab_name sysname)
   set @sql='IF (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM '+@vFromDb+'..?) a)>0 INSERT INTO # VALUES(''?'')'
   exec sp_msforeachtable @sql   
   declare tb_cursor scroll cursor for SELECT * FROM #
   open tb_cursor 
   fetch next from tb_cursor into @tablename
   while @@fetch_status=0 
   begin        
       if exists(Select name from sysobjects where Name=@tablename)
       begin
         set @sql='select @num=count(1) from '+@tablename
         exec sp_executesql @sql,N'@num int output',@num output 
         if @num=0 
         begin
           exec p_GetTableField_Ex @tablename,',','',@vFromDb,@s output
           select @s
           set @sql='insert into '+ @tablename+' ( '+ @s+') select '+@s+' from '+@vFromDb+'..'+@tablename
           exec(@sql) 
         end
       end
       fetch next from tb_cursor into @tablename
   end 
   close tb_cursor 
   deallocate tb_cursor 
   drop TABLE dbo.#
end
这个过程完成的功能是一个库导到另外一个库,导的时候要求源库和目标库存在同样的表,且目标库的表没有记录
像上面的这样的写法有误吗?
if exists(Select name from sysobjects where Name=@tablename)

Answer 1

if exists(Select name from sysobjects where Name=@tablename)
--------------------------------这种没有语法错误。

Answer 2

if exists(Select name from sysobjects where Name=@tablename)表示：如果存在指定的表名...

Answer 3

if exists(Select name from sysobjects where Name='#') drop table #
----------------
不好意思,这样的写法是错的,不能对临时表进行这样的操作

Answer 4

exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM  northwind..? )>0 INSERT INTO # VALUES(''?'')'
---------------------
服务器: 消息 7202，级别 11，状态 2，行 1
在 sysservers 中未能找到服务器 'northwind'。请执行 sp_addlinkedserver 以将服务器添加到 sysservers。只能这样的写法是对的
exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''----------------------
有什么办法得到另外一个数据库的每表操作

Answer 5

你的写法有问题，应该是：exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM  northwind.? )>0 INSERT INTO # VALUES(''?'')'因为?本身就带了dbo.

Answer 6

我在pubs库测试会出现以下提示exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM  northwind.? )>0 INSERT INTO # VALUES(''?'')'
----------
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.titleauthor' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.stores' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.sales' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.roysched' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.discounts' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.jobs' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.pub_info' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.employee' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.authors' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.publishers' 无效。
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'northwind.dbo.titles' 无效。------------------------------------------------
sp_msforeachtable只能对当前库的每个表进行操作,而不能跨库操作

Answer 7

有什么办法得到另外一个数据库的每表操作
-------------------------------------------exec northwind..sp_msforeachtable ....

Answer 8

在sp_msforeachtable前加上你的库名即可。

Answer 9

楼上真强,但是如果前面这个northwind是动态的就不行了

Answer 10

declare @sql nvarchar(4000) ,@vFromDb varchar(50)
  set @vFromDb='northwind'
  drop TABLE dbo.#
   CREATE TABLE dbo.#(tab_name sysname)
   set @sql='IF (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM ?) a)>0 INSERT INTO # VALUES(''?'')'
    exec('exec'+ @vFromDb+'..sp_msforeachtable'+ @sql  )
-----------
服务器: 消息 170，级别 15，状态 1，行 1
第 1 行: '?' 附近有语法错误。

Answer 11

>1
exec sp_msforeachtable 'select ''?'' as ''表名'',(select count(1) from ?) as ''行数'''
>2
select DISTINCT o.name,i.rows from sysobjects o
inner join sysindexes i
on o.id=i.id
where xtype='u' and OBJECTPROPERTY(o.id, N'IsUserTable') = 1 and i.rows>0 
>3
select DISTINCT a.name,c.rows
from  sysobjects  a , syscolumns b , sysindexes c
where  a.id=b.id and a.id=c.id 
and  a.type='u'  and c.rows<>0
>4
CREATE TABLE dbo.#(tab_name sysname)exec sp_msforeachtable 'IF (SELECT COUNT(*) FROM (SELECT TOP 1 * FROM ?) a)>0 INSERT INTO # VALUES(''?'')'SELECT COUNT(*) FROM #
SELECT * FROM #>   
drop TABLE dbo.#>   
CREATE TABLE dbo.#(tab_name sysname)>
declare @sql nvarchar(4000) ,@vFromDb varchar(50)
set    @vFromDb='northwind'
set @sql='
INSERT INTO # 
select DISTINCT a.name
from  '+@vFromDb+'..sysobjects  a , '+@vFromDb+'..syscolumns b , '+@vFromDb+'..sysindexes c
where  a.id=b.id and a.id=c.id 
and  a.type=''u''  and c.rows<>0'
exec(@sql)
>
select * from #

Answer 12

create procedure p_DbToDb(@vFromDb varchar(50)) 
as 
begin    
   declare @tablename varchar(50) 
   declare @sql nvarchar(4000)    
   declare @num int
   declare @s as varchar(8000)
   CREATE TABLE dbo.#(tab_name sysname)
   set @sql='INSERT INTO # 
             select DISTINCT a.name
             from  '+@vFromDb+'..sysobjects  a , '+@vFromDb+'..syscolumns b , '+@vFromDb+'..sysindexes c
             where  a.id=b.id and a.id=c.id 
             and  a.type=''u''  and c.rows<>0'
   exec(@sql)
   declare tb_cursor scroll cursor for SELECT * FROM #
   open tb_cursor 
   fetch next from tb_cursor into @tablename
   while @@fetch_status=0 
   begin        
       if exists(Select name from sysobjects where Name=@tablename)
       begin
         set @sql='select @num=count(1) from '+@tablename
         exec sp_executesql @sql,N'@num int output',@num output 
         if @num=0 
         begin
           exec p_GetTableField_Ex @tablename,',','',@vFromDb,@s output
           select @s
           set @sql='insert into '+ @tablename+' ( '+ @s+') select '+@s+' from '+@vFromDb+'..'+@tablename
           print @sql
           exec(@sql) 
         end
       end
       fetch next from tb_cursor into @tablename
   end 
   close tb_cursor 
   deallocate tb_cursor 
   drop TABLE dbo.#
end exec p_DbToDb 'northwind'

Answer 13

服务器: 消息 544，级别 16，状态 1，行 1
当 IDENTITY_INSERT 设置为 OFF 时，不能向表 'Employees' 中的标识列插入显式值。

Answer 14

create procedure p_GetTableField(
@vTableName varchar(50),
@vSplitChar varchar(1),
@vPrefix varchar(2),
@vFromDBName varchar(50),
@vTableField varchar(8000) output
) 
as 
begin 
   declare @s as varchar(8000) 
   declare @sqls nvarchar(4000) 
   set @vTableField='' 
   select @s=name from Sysobjects where xtype=N'U' and objectproperty(object_id(@vTableName),N'IsUserTable')=1 and name=@vTableName 
   if @s=@vTableName 
   begin 
       set @s='' 
       if @vFromDBName<>'' 
       begin
         set @sqls='select @s=@s+'''+@vSplitChar+''''+@vPrefix+'+name from syscolumns where id=object_id('''+@vTableName+''') and name in (select name from '+@vFromDBName+'..syscolumns where id=object_id('''+@vFromDBName+'..'+@vTableName+'''))'
         print  @sqls
         exec sp_executesql @sqls,N'@s varchar(8000) output',@s output 
       end
       else select @s=@s+@vSplitChar+@vPrefix+name from syscolumns where id=object_id(@vTableName)        
       set @s=stuff(@s,1,1,'') 
       set @vTableField=isnull(@s,'') 
   end 
end

Answer 15

create procedure p_GetTableField(
@vTableName varchar(50),
@vSplitChar varchar(1),
@vPrefix varchar(2),
@vFromDBName varchar(50),
@vTableField varchar(8000) output
) 
as 
begin 
   declare @s as varchar(8000) 
   declare @sqls nvarchar(4000) 
   set @vTableField='' 
   select @s=name from Sysobjects where xtype=N'U' and objectproperty(object_id(@vTableName),N'IsUserTable')=1 and name=@vTableName 
   if @s=@vTableName 
   begin 
       set @s='' 
       if @vFromDBName<>'' 
       begin
         set @sqls='select @s=@s+'''+@vSplitChar+''''+@vPrefix+'+name from syscolumns where id=object_id('''+@vTableName+''') and name in (select name from '+@vFromDBName+'..syscolumns where id=object_id('''+@vFromDBName+'..'+@vTableName+'''))'
         print  @sqls
         exec sp_executesql @sqls,N'@s varchar(8000) output',@s output 
       end
       else select @s=@s+@vSplitChar+@vPrefix+name from syscolumns where id=object_id(@vTableName)        
       set @s=stuff(@s,1,1,'') 
       set @vTableField=isnull(@s,'') 
   end 
end

Answer 16

樓主的
if exists(Select name from sysobjects where Name='#') drop table #
應該改為：
if object_id('tempdb..#') is not null drop table #

Answer 17

楼上的说法正确if object_id('tempdb..#') is not null drop table #

Answer 18

OBJECT_ID
返回数据库对象标识号。语法
OBJECT_ID ( 'object' )参数
'object'要使用的对象。object 的数据类型为 char 或 nchar。如果 object 的数据类型是 char，那么隐性将其转换成 nchar。返回类型
int注释
当该参数对系统函数可选时，则系统采用当前数据库、主机、服务器用户或数据库用户。内置函数后面必须跟圆括号。如果指定一个临时表名，则必须在临时表名前面加上数据库名，例如：SELECT OBJECT_ID('tempdb..#mytemptable')系统函数可以在选择列表、WHERE 子句和任何允许使用表达式的地方使用。有关更多信息，请参见表达式和 WHERE。 示例
下面的示例为 pubs 数据库中的 authors 表返回对象 ID。USE master
SELECT OBJECT_ID('pubs..authors')下面是结果集：----------- 
1977058079   (1 row(s) affected)