想实现一个sql程序:1.遍历所有数据库2.遍历数据库里的所有表现在已经实现:1.已经能够找到所有数据库2.但是在使用execute,来切换数据库时,没有效果,切换之后依然是先前数据库
查看文档之后,发现使用execute use来改变上下文,只会持续到execute执行完毕希望各位帮帮忙,有什么好的解决办法吗?谢谢
查看文档之后,发现使用execute use来改变上下文,只会持续到execute执行完毕希望各位帮帮忙,有什么好的解决办法吗?谢谢
set @db = '...'exec('use ' + @db)
set @db = '...' exec('use ' + @db)
declare @db as varchar(10)
declare @sql as varchar(100)
set @db = '...'
set @sql = 'use ' + @dbexec(@sql)
exec sp_msforeachdb ' use [?] select * from sysobjects'--庫自定議條件,超過100結果集用表變量或臨時表存儲
@command1 = N'
USE ?
EXEC sp_MsForeachtable
@command1 = N''PRINT ''''$'''' '',
@replacechar = N''$''
'
DECLARE @catalog_name varchar(40), @scheme_name varchar(50),@table_name varchar(40), @table_type varchar(40),
@message varchar(80), @column_name varchar(40),@data_type varchar(40),
@sqlstring nvarchar(40)PRINT '-------- Database report --------'DECLARE scremata_cursor CURSOR FOR
SELECT name
FROM sys.databasesOPEN scremata_cursorFETCH NEXT FROM scremata_cursor
INTO @catalog_nameWHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = 'Database Name=' + @catalog_name
PRINT @message
select @sqlstring=N'USE '+@catalog_name
execute sp_executesql @sqlstring
--execute xp_use @sqlstring
DECLARE tables_cursor CURSOR FOR
SELECT TABLE_NAME,TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @catalog_name --当前表的数据库,总是当前数据库 OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @table_name,@table_type WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' TABLE_NAME=' + @table_name +' TABLE_TYPE=' + @table_type
PRINT @message DECLARE columns_cursor CURSOR FOR
SELECT COLUMN_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table_name
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @column_name,@data_type
WHILE @@FETCH_STATUS = 0
BEGIN SELECT @message = ' COLUMN_NAME=' + @column_name + ' DATA_TYPE=' + @data_type
/* PRINT @message */ FETCH NEXT FROM columns_cursor INTO @column_name,@data_type
END
CLOSE columns_cursor
DEALLOCATE columns_cursor FETCH NEXT FROM tables_cursor INTO @table_name,@table_type END CLOSE tables_cursor
DEALLOCATE tables_cursor
FETCH NEXT FROM scremata_cursor
INTO @catalog_name
ENDCLOSE scremata_cursor
DEALLOCATE scremata_cursor
GO