帮我看看如何实现这个需求
求助一个存储过程的写法
我有个需求
就是首先要
遍历所有实例拥有的database
执行select count(1) from sysobjects where xtype='U'
如果返回值大于0就把dbname显示出来
简单说就是判断当前实例是否存在空库
以下是我写的procedure
create procedure IF_NULL_DATABASE
as
declare @db_name varchar(20)
declare @sql_body varchar(200)
declare @cnt int
set @cnt=0
declare cur_name cursor for select name from sys.sysdatabases for read only
open cur_name ;
fetch next from cur_name into @db_name ;
while @@FETCH_STATUS = 0
begin
exec('use ' + @db_name) exec ('select count(1) from sysobjects where xtype ='U'')
print @db_name
print @cnt
Fetch From cur_name into @db_name;
if @cnt = 0
begin
insert into tmp_db_name(name) values (@db_name)
end
end
close cur_name
deallocate cur_name
但是抛出
Msg 102, Level 15, State 1, Procedure IF_NULL_DATABASE, Line 13
Incorrect syntax near 'U'.
大家给点意见吧