|
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执行结果
|
|