SQLServer 存于数据库 存于那一个库那一个表中
假如有一台数据库服务器,里面有很多库,而自己对这些库结构又不是很了解(比如到了一家新公司),想查找一张表在哪的话,可以借助下面这段SQL语句.
--
--查找当前数据库服务器中某张表存在于哪个数据库中,sqlserver2008测试通过
--
declare @tableName varchar(50)
--这里设置要查询的表名字
set @tableName='Products'
--清理临时表
if object_id('tempdb..#tmpdbs') is not null Begin
drop table #tmpdbs
End
if object_id('tempdb..##tmpResults') is not null Begin
drop table ##tmpResults
End
--手动创建全局临时表,用于保存查询结果.下面插入时只能使用insert into ,不能使用select into ,后者会自动创建临时表
create table ##tmpResults(
DbName varchar(50),
Name varchar(50),
XType varchar(50)
)
Select Name,ROW_NUMBER() over(order by Name) as rowid into #tmpdbs FROM Master..SysDatabases Name
declare @dbName varchar(50)
declare @rowid int
declare @count int
set @rowid=1
select @count=count(*) from #tmpdbs
while @rowid <= @count
begin
--print(@rowid)
select @dbName=[Name] from #tmpdbs where rowid=@rowid
exec ('insert into ##tmpResults Select '''+@dbName+''' as DbName,Name,xtype FROM '+@dbName+'..SysObjects Where (XType=''U'' or XType=''SN'') and Name='''+@tableName+''' ORDER BY Name')
set @rowid=@rowid+1
end
--查看结果
select * from ##tmpResults
--清理临时表
if object_id('tempdb..#tmpdbs') is not null Begin
drop table #tmpdbs
End
if object_id('tempdb..##tmpResults') is not null Begin
drop table ##tmpResults
End