34,575
社区成员
发帖
与我相关
我的任务
分享
USE master
declare @str varchar(200)
declare @dataname varchar(20)
declare cr cursor fast_forward for
select name+'.dbo.BB' from sysdatabases
open cr
fetch next from cr into @dataname
while @@fetch_status<>-1
begin
print @str
exec('select top 1* from '+@dataname)
if @@rowcount=0
begin
select @str='insert into '+@dataname +'
select 1 ,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0'
print @str
exec('insert into '+@dataname +'
select 1,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0')
end
fetch next from cr into @dataname
end
close cr
deallocate cr
USE master
declare @str varchar(200)
declare @dataname varchar(20)
declare cr cursor fast_forward for
select name+'.dbo.BB' from sysdatabases WHERE name='IT_PUBLIC'
open cr
fetch next from cr into @dataname
while @@fetch_status<>-1
begin
print @str
exec('select top 1* from '+@dataname)
if @@rowcount=0
begin
select @str='insert into '+@dataname +'
select 1 ,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0'
print @str
exec('insert into '+@dataname +'
select 1,1 union all
select 2,0 union all
select 3,0 union all
select 4,0 union all
select 5,0')
end
fetch next from cr into @dataname
end
close cr
deallocate cr
select *from IT_PUBLIC.dbo.BB
EXEC sp_msforeachdb N'
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'
EXEC sp_msforeachdb N'
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF OBJECT_ID(''BB'') IS NOT NULL -- 判断表是否存在
EXEC(N''
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'');
'
EXEC sp_msforeachdb N'
-- 不是需要的数据库, 则跳过
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
IF NOT EXISTS( -- 没有数据插入
SELECT * FROM BB)
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'
EXEC sp_msforeachdb N'
IF N''?'' NOT LIKE ''USER%''
RETURN;
USE ?;
INSERT BB
SELECT 1, 1 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0;
'