SQL脚本

`/1:删除临时表/ if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#tempTable')) drop table #tempTable;

/2:定义游标/ 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);

/3:打开游标/ open databaseNameCursor;

/4:连接游标/ fetch next from databaseNameCursor into @databaseName

/5:创建临时表/ create table #tempTable ( id int identity(1,1) not null, databasename nvarchar(max), schemaname nvarchar(max), tablename nvarchar(max), primary key(id) );

/6:循环插入临时表中*/ 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

/7:关闭游标/ close databaseNameCursor;

/8:释放游标/ deallocate databaseNameCursor;

/9:查看服务器所有表/ select * from #tempTable`

SQL脚本使用

先执行注释1，然后注释2到注释8脚本一起执行，最后执行注释9或者使用临时表。

SQL执行结果