环境:MS SQL 2000
内有用户自建数据库db1,db2,db3等等(个数不确定)
求SQL语句
1:显示这些数据库中所有的用户表,即显示整个数据库里面的用户表
2:在某一存储过程中,有两个入口参数,@dbname varchar(10),@tablename varchar(20)
@dbname为某个数据库的名称,@tablename为表名
如何判断该表在该库内。谢谢。
内有用户自建数据库db1,db2,db3等等(个数不确定)
求SQL语句
1:显示这些数据库中所有的用户表,即显示整个数据库里面的用户表
2:在某一存储过程中,有两个入口参数,@dbname varchar(10),@tablename varchar(20)
@dbname为某个数据库的名称,@tablename为表名
如何判断该表在该库内。谢谢。
select name from sysobjects where xtype='u'2.
declare @SQL nvarchar(max),@dbname varchar(100),@tbname varchar(100),@r varchar(10)
set @dbname='数据库名'
set @tbname='表名'
set @SQL='use '+@dbname+'
if exists(select name from sysobjects where xtype=''u'' and name like '''+@tbname+''')
set @r=''存在''
else
set @r=''不存在'''
exec sp_executesql @SQL,N'@r varchar(10) out',@r out
print @r
2. jia_guijun 的方法不错,不过方法看起来不是很巧,不知道有没有更好的办法。
实际上,1和2其实是一个问题,呵呵,欢迎大家继续讨论
SET @SQL='';
SELECT
@SQL=@SQL+CASE WHEN @SQL='' THEN 'SELECT '''
ELSE ' UNION ALL SELECT '''
END+name+''' AS dbname,name COLLATE CHINESE_PRC_CI_AI AS name FROM ['+name+'].dbo.sysobjects WHERE xtype=''U'''
FROM master.dbo.sysdatabases
WHERE dbid>4EXEC(@SQL)
declare cur cursor for select rtrim(name) from master..sysdatabases where name like 'db_' order by name
open curdeclare @cDBName varchar(50)
declare @cSQL varchar(2000)
set @cSQL = ''
fetch next from cur into @cDBName
while( @@fetch_status = 0 )
begin
set @cSQL = 'select '''+ @cDBName +''' as 数据库名, rtrim(name) as 表名 from '+ @cDBName + '..sysobjects where type = ''U'''
exec( @cSQL )
fetch next from cur into @cDBName
end
close cur
deallocate cur
set nocount off
最后要执行这个存储过程才行
exec pr_IsExists '数据库名', '表名'
if( object_id('pr_IsExists') is not null )
drop procedure pr_IsExists
go
create pr_IsExists
@dbname varchar(50)
@tablename varchar(50)
as
begin
declare @cSQL varchar(2000)
set @cSQL = ''set @cSQL = 'select (case isnull(max(id), 0) when 0 then ''不存在'' else ''存在'' end) as 结果 from '+ @cDBName + '..sysobjects where type = ''U'' and name = ''' + @tablename + ''''
exec( @cSQL )
end
go
FROM ['+@dbname+'].dbo.sysobjects
WHERE xtype=''U'' AND name='''+@tbname+''')
PRINT ''存在''
ELSE
PRINT ''不存在''')
1.
declare @sql varchar(3000)
SELECT @sql = isnull(@sql+ ' union all ','') + 'SELECT '''+name+''',name FROM '+name+'.dbo.sysobjects where type = ''U'''
FROM master.dbo.sysdatabases --where name like 'db%'
exec(@sql)
2.
declare @dbname sysname,@tablename sysname
set @tablename = 'aa'
set @dbname = 'b'exec ('select case count(*) when 0 then ''不存在'' else ''存在'' end from '+@dbname+'.dbo.sysobjects where type = ''U'' and name = '''+@tablename+'''')
-- 第二个忘该存储过程了
create is_existtale
@dbname varchar(50)
@tablename varchar(50)
as
begin
exec ('select case count(*) when 0 then ''不存在'' else ''存在'' end from '+@dbname+'.dbo.sysobjects where type = ''U'' and name = '''+@tablename+'''')
end
go