sql脚本 if exists(select * from tempdb..sysobjects where id=object_id("tempdb..#tempTable")) drop t
if exists(select * from tempdb..sysobjects where id=object_id("tempdb..#tempTable"))
drop table #tempTable;
declare databaseNameCursor cursor for select name from master.dbo.SysDatabases;
declare @databaseName nvarchar(512),@databaseCount int;
set @databaseCount=(select count(1) from master.dbo.SysDatabases);
open databaseNameCursor;
fetch next from databaseNameCursor into @databaseName
create table #tempTable
(
id int identity(1,1) not null,
databasename nvarchar(max),
schemaname nvarchar(max),
tablename nvarchar(max),
primary key(id)
);
while (@@fetch_status=0 and @databaseCount>0)
begin
begin try
set @databaseCount=@databaseCount-1;
declare @tableFullName nvarchar(1024);
set @tableFullName="select """+@databaseName+""",schema_name(schema_id),name from "+@databaseName+".sys.tables";
insert into #tempTable(databasename,schemaname,tablename)
exec sp_executesql @tableFullName;
--指向下一个游标
fetch next from databaseNameCursor into @databaseName
end try
begin catch
continue;
end catch
end
close databaseNameCursor;
deallocate databaseNameCursor;
select * from #tempTable
先执行注释1,然后注释2到注释8脚本一起执行,最后执行注释9或者使用临时表。
--结束END--
本文标题: SQLServer之查询当前服务器下所有目录视图表
本文链接: https://www.lsjlt.com/news/4605.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-13
2024-05-13
2024-05-11
2024-05-11
2024-05-10
2024-05-07
2024-04-30
2024-04-30
2024-04-30
2024-04-29
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0