SQLServer之查询当前服务器下所有目录视图表

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或者使用临时表。